boblarson 15

Okay, the first thing is that you should not have Table 2 - the Invoice Totals. That stores calculated data which is not a properly normalized structure. So, you would build that by pulling the Invoice Details and summing them.

From your post here's how I see the table structure:

[code]

(Client info)Table 1
PK - ClientID
CompanyName
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail

If the client can have more than one contact, you might want to revise that first table to be like this:

(ClientInfo) Table 1
PK – ClientID
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip
CompanyPhone
CompanyFax
CompanyWebsite

(Contacts) Table 1a
PK - ContactID (Autonumber)
ClientID – Long Integer (FK)
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail
UseCompanyAddress (Yes/No)

(Client invoice Header)Table 2
PK-ClientInvoiceID
ClientId
InvoiceDate
StudyNameID

(Invoice Details) Table 3
PK – InvoiceDetailsID (Autonumber)
ClientInvoiceID – Long Integer (FK)
Quantity
SupplyID – Long Integer (FK)

(Supplies) Table 4
PK -- SupplyID (Autonumber)
SupplyDescription

(Payment itemization)Table 5
PK – PaymentID (Autonumber)
ClientInvoiceID – Long Integer (FK)
CPI
Payment

What is PRI invoice number??? I don’t think you need this table
(PRI invoice number) Table x
PK - PRI invoice ID
Client id
Client invoice id

(Studies) Table 6
PK – StudyID (Autonumber)
StudyName
ClientID – Long Integer (FK)
PONumber
Jobnumber
MarketID – Long Integer (FK)
StudyDate

(Study Type) Table 7
PK- StudyTypeID
StudyType

(Market) Table 8
PK - MarketID – (Autonumber)
MarketName
[/code]
I don't know what CPI standa for ...

boblarson 15

[QUOTE=panhwer;1575536]In combobox use onchange() event [/QUOTE]
Not the On Change event. AFTER UPDATE event.

[QUOTE=panhwer;1575536] .. and pass this value to a function as argument and use ajax to dynamically update second combo box...
Study little bit about ajax.[/QUOTE]
What are you talking about Ajax here for? This is in an Access database and Access databases do not use Ajax. I think you misunderstood panhwer which forum category you were in.

boblarson 15

[QUOTE=BitBlt;1572467]Use the "On Change" event in your first combo box, update the "filter" property on your second combo box and issue a refresh.[/QUOTE]

Actually it should be in the combo's AFTER UPDATE event, not the On Change. On Change will also fire for every keystroke if someone starts typing in it which can cause problems and slow things down, depending on things.

BitBlt commented: Good catch. +7

boblarson 15

[QUOTE=TGeorge824;1575147]Hi

I want to figure out a way to update multiple rows that depend on a couple of variables. I don't really know the syntax in SQL, but I know what I would do in C, so maybe if you could translate this...

[CODE]
switch (x)
case 0:
switch(y)
case 0: myVar = var;
case 1: myVar = someOtherVar;
case 1:

.
.
.
[/CODE]

Something like that is what I need to do. I could do it all with a bunch of queries but I don't really want to do it that way.[/QUOTE]

In Access it would be something like

[code]
Select Case x
Case 0
Select Case y
Case 0
MyVar = var
Case 1
MyVar = SomeOtherVar
End Select

Case 1
     Select Case y
         Case 0
            MyVar = OneMoreVar
         Case 1
            MyVar = GotVar
     End Select

Case Else
      Msgbox "This has not been configured."

End Select

[/code]

boblarson 15

[QUOTE=ITNoobie;1573449]Ok, so basically all 6 tables in my database should have a primary key to foreign key relationship to each other?[/QUOTE]

I would say that, NORMALLY you would have tables which are linked together in some way. It is RARE to not be able to link them together. But also, if you do have them linked, if the links seem to form a big circle of sorts then you probably have something wrong with the design.

So, if you would like to list out the tables you have and their fields I'm sure that we could give you more detailed assistance, instead of the generalizing which may, or may not, fit your situation.

boblarson 15

[QUOTE=BitBlt;1561848]Yes, it is in MSAccess, it's the first window you see when you open up an MDB file. It shows your list of tables, or forms, or queries, etc. It is referred to as the "Database" window in the documentation.[/QUOTE]
It is also referring to the NAVIGATION PANE which is what the OP has referred to. So, they would have to

If Access 2007
Go to the ROUND OFFICE BUTTON > ACCESS OPTIONS > CURRENT DATABASE and then they can set the startup form and also UNCHECK the SHOW NAVIGATION PANE checkbox. However they will also need to uncheck the USE ACCESS SPECIAL KEYS or else the user can simply press F11 to open the navigation pane (or database window if in an older version).

If Access 2010
Go to FILE > ACCESS OPTIONS > CURRENT DATABASE and do the same as the Access 2007 instructions.

boblarson 15

[QUOTE=crazycat503;1541788]Hi,

I am trying to come up wit a database design for a small school. it works this way: in AC-Year, there are 3 terms with each term having 2 semesters. New students get registered at the start of each term. A student may take entrance exam. if he fails, he starts at freshman then after the semester, he can select department and program (i.e. diploma, certificate...). A student can select certificate accounting, for e.g., but if he is good, he can continue to diploma accounting program, taking the courses he needs only.

how do i store that information? How do i keep track of where he is studying? If he is upgraded, how do i keep his past? Please some tip as i am totally lost on this one.[/QUOTE]

Go here

[url]http://www.databaseanswers.org/data_models/[/url]

and look down the page for "STUDENTS" and there are a lot of data models there to choose from. You can look to see if any might work for you.

boblarson 15

You don't need to use the search to find the record in the other table. You would use a SQL Statement and recordset to bring back the appropriate record if there was one:

[CODE]'SEARCH FOR THE RECORD IN REMARK TABLE
Dim strSQL As String
Dim strWHERE As String
Dim rs As DAO.Recordset

' I assume that BuildCriteria does NOT return the word WHERE at the beginning
strWHERE = BuildCriteria("[IDOp]", dbInteger, OpNameClick)

strSQL = "SELECT * FROM Remark WHERE " & strWHERE

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
Msgbox "Record does not exist"
Else
' do whatever here with the record you have found from the other table
' not sure what it is you really want or what to do with it.
End If

rs.Close
Set rs = Nothing[/CODE]

boblarson 15

[QUOTE=koffimide;1529105]Below is the connection string,

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_cnxxxx_STRING
MM_cnxxxx_STRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("./uuuu.mdb")
%>[/QUOTE]

Check out the Connection String for Access here:
[url]http://www.connectionstrings.com/access[/url]

Yours doesn't look right.

boblarson 15

[QUOTE=isumasama;1537933]hi all,
i have created a crosstab query and one of the row heading contains a hyperlink field.
my problem is that the field is not working,
and on top of that it gives corrupted values too.

pls help
thanks[/QUOTE]

I don't believe you can use that directly in a crosstab. Create your crosstab query without that field and then create a select query which joins the crosstab with the hyperlink field in it.

boblarson 15

[QUOTE=charlybones;1506446]Ok, so after hours and hours of testing and searching online I'm almost convinced that this can't be done, but I'm asking here for help maybe someone can give me any idea as to why this isn't working.

I'm doing this query:
[CODE]SELECT * FROM(
(SELECT t1.PAG5 from table1 t1 where t1.PAG5 > 0)
UNION ALL
(select t2.PAG1 from table1 t2 where t2.PAG1 > 0)
)
[/CODE]

The bad part: it has to be access 2.0

This gives me an error after the first FROM. I'm guessing access 2.0 doesn't allow nested Select with Union. Is this true?

PS: Running the two Union queries by themselves works.[/QUOTE]

I just tried with this and it works:

[code]
Select * FROM (SELECT t1.PAG5 from table1 [B][COLOR="Red"]As[/COLOR][/B] t1 where t1.PAG5 > 0
UNION ALL
select t2.PAG1 from table1 [B][COLOR="red"]As[/COLOR][/B] t2 where t2.PAG1 > 0)[/code]

boblarson 15

[QUOTE=Hyo;1513776]I have done this query : Balance:[Medical]![Price]-750[/QUOTE]
That isn't a query. That may be PART of a query from the QBE grid but it is definitely not the entire query. If it is then that would explain a lot.

Post the SQL of the query (Go to VIEW > SQL VIEW to be able to get that and copy and paste it here.

boblarson 15

I forgot to include how to call this.

It should be:

Call AdjustProcNum("YourTableNameInQuotes", "YourFieldNameInQuotes", YourNewNumber)

boblarson 15

Copy this function into a standard module (not form, report or class) and name the module something other than the name of the function:

[code]
Function AdjustProcNum(strTableName As String, strFieldName As String, lngNewNum As Long)
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "Select [" & strFieldName & "] From [" & strTableName & "] " & _
"ORDER BY [" & strFieldName & "]"

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
If rst(strFieldName).Value >= lngNewNum Then
rst.Edit
rst(strFieldName).Value = rst(strFieldName).Value + 1
rst.Update
End If
rst.MoveNext
Loop

MsgBox "You can now enter the new process number " & lngNewNum, vbInformation

rst.Close
Set rst = Nothing
End Function
[/code]

boblarson 15

I'm assuming Outcome is numeric but it wouldn't have to be but you would change the value for NZ to vbNullString if it is text.

[CODE]MyNum1 = ]")[COLOR="red"][B]Nz([/B][/COLOR]DLookup("[Outcome]", "Remark", _
"[IDRemark] = Form![SelectTxt]")[COLOR="red"][B],0)[/B][/COLOR]
OutTxt.Value = MyNum1[/CODE]

boblarson 15

Set up the table to be imported to first with the right datatypes and all and then import the data.

boblarson 15

You can try setting the Search button's DEFAULT property to YES and see if that helps.

boblarson 15

You are using the wrong ODBC driver. You should be using one for ACCESS. You don't select the driver based on what you are programming FROM but where you want to connect TO.

So you want the Microsoft Access ODBC driver and the connection strings are available here:

[URL="http://www.connectionstrings.com/access"]For Access 2003 or prior[/URL]

[URL="http://www.connectionstrings.com/access-2007"]for Access 2007 or greater[/URL]

boblarson 15

It is not a simple task for which you seek. About the only way you will get to do it, short of being an EXPERT Access Programmer and writing a LOT of code is to purchase [URL="http://www.peterssoftware.com/ss.htm"]Shrinker/Stretcher from Peter's Software[/URL] which does do this for you.

boblarson 15

  1. Is this a saved query (not being done in code)?

  2. Is this combo box on a subform perhaps?

  3. As for concatenating, it has to do with the way the value is inserted and not the form reference so the query doesn't have to do an evaluation of the control.

  4. Please post the [B]ENTIRE SQL string[/B], not just what YOU think is relevant.

boblarson 15

What is the SQL of the query. I'll bet you included the combo inside the SQL and didn't concatenate it in so that it would pass the VALUE and not have to try to evaluate it.

boblarson 15

[QUOTE=amina_latif;1439126]Hi I am new to VB.NET but for my final year project have started a system for childrens daycare center. I want to create user accounts which I think I should be able to do in acess.

How do I link this to VB.NET? is it hard? what will vb.net let me do?[/QUOTE]
If you are using VB.NET as the frontend, then don't go trying to create user accounts in Access. You can use tables in Access to store your user account information but use VB.NET to handle the users and the access to various points. Access security (available only with mdb files and not the newer Access 2007/2010 ACCDB files) is meant for use when you use Access directly as a frontend.

boblarson 15

[QUOTE=erum;1438600]i m facing problem while conencting with sql server mangemnet studio

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)[/QUOTE]
You posted this in the wrong category. It should be in the SQL Server category instead.

boblarson 15

You don't link SharePoint to an Access database, you link an Access database to SharePoint. Access can display SharePoint data and use it for creating reports, not the other way around. You may want to read this for more information:

[url]http://www.databasejournal.com/features/msaccess/article.php/3809741/SharePoint-and-Microsoft-Access---Getting-Motivated.htm[/url]

boblarson 15

Why not just create a frontend in Access?

boblarson 15

I answered your question in your other post here:

[url]http://www.daniweb.com/forums/thread331196.html[/url]

boblarson 15

With tab controls you do not reference any tabs. Just reference a control as if it was on the form itself. If you are referencing a control on a subform within a tab just use the normal syntax for referring to a control on the subform. There is no special referencing needed. Pretend the tabs don't even exist.

boblarson 15

Control Source of the text box:

=DMax("YourDateFieldNameHere", "YourTableNameHere")

Replace YourDateFieldNameHere with your actual field which has the dates and YourTableNameHere with the name of the table where the date field resides. And KEEP THE DOUBLE QUOTES.

boblarson 15

No, each computer has to have it set up. You can run a batch file to update registry settings ([URL="http://accessjunkie.com/faq_33.aspx"]see here for more[/URL] about the registry settings required).

boblarson 15

Yes, you should make an ACCDE file. What is happening right now is that you have compile errors that you need to fix before it can compile to an ACCDE. So, open the VBA window, and go up to DEBUG > COMPILE {yourprojectnamehere} and run the compile. Fix the error that it highlights and then go compile again and keep fixing the errors until you have no more errors. Then you should be able to create the ACCDE file.