Needing Help on Relations, Is there anyone that can help?

[David Davis]

Hello,

I am somewhat new at access 2003 and I really need some assistance on this so I can continue on. Any assistance would help.

I want to retrieve last sequential number in Field1 in Table1 to put in Field 3 of Table2. How do I do this? I tried Nz(DMax("[Field1]","Table1"))+1 in the default value of Field 3 in Table2 but it came up with Unknown Function 'NZ' in validation expression. Can someone help....

Thanks

Dave

[Stewart Ross]

Hello,

I am somewhat new at access 2003 and I really need some assistance on this so I can continue on. Any assistance would help.

I want to retrieve last sequential number in Field1 in Table1 to put in Field 3 of Table2. How do I do this? I tried Nz(DMax("[Field1]","Table1"))+1 in the default value of Field 3 in Table2 but it came up with Unknown Function 'NZ' in validation expression. Can someone help....

Thanks

Dave

Hi David. You can't place such an expression in the default value property of the table itself - the error message is a bit misleading here. You can put such an expression into the default value of a control bound to the value of Field1 on a user data entry form, preceded by an = sign to indicate the use of an expression, as shown below. Note the use of a zero in the Nz function below to return a 0 if the field is null:

 =Nz(DMax("[Field1]","Table1", 0))+1

If you want to prevent users changing the value, you also need to set two other properties for the field: enabled to No and locked to Yes.

-Stewart

[David Davis]

Hi David. You can't place such an expression in the default value property of the table itself - the error message is a bit misleading here. You can put such an expression into the default value of a control bound to the value of Field1 on a user data entry form, preceded by an = sign to indicate the use of an expression, as shown below. Note the use of a zero in the Nz function below to return a 0 if the field is null:

 =Nz(DMax("[Field1]","Table1", 0))+1

If you want to prevent users changing the value, you also need to set two other properties for the field: enabled to No and locked to Yes.

-Stewart

Stewart,

Is there a expression you can use to take the next consecutive unused number of one field of table1 and put it into another field in table2 so that the field in Table1 gets updated at the same time table2 is requesting the next unused consecutive numver. I am assuming you link the tables together but I am not sure about pulling the data out of one to put in the other or am I going in the wrong direction? Does any of this makes since?

David

[Stewart Ross]

Hi David. A table design often has a unique numeric identifier as the primary key. Access provides an autonumber field which automatically increments for each record, although it has some quirks which mean it is not always the best choice.

Generally, linking the key fields in tables participating in one-to-many relationships can be done using a user form with a subform for the many-side relationship.The linkage of parent and child key fields between the form and subform takes care of setting key values between tables auotmatically.

Without knowing more about your database design its hard to advise you more specifically.

There is a good HowTo article in the forum at http://www.thescripts.com/forum/thread585228.html on table design and normalisation, which is an essential first step in designing any database. Any good book on database design will also help you with understanding the basics of relations between tables.

-Stewart

[David Davis]

Hi David. A table design often has a unique numeric identifier as the primary key. Access provides an autonumber field which automatically increments for each record, although it has some quirks which mean it is not always the best choice.

Generally, linking the key fields in tables participating in one-to-many relationships can be done using a user form with a subform for the many-side relationship.The linkage of parent and child key fields between the form and subform takes care of setting key values between tables auotmatically.

Without knowing more about your database design its hard to advise you more specifically.

There is a good HowTo article in the forum at http://www.thescripts.com/forum/thread585228.html on table design and normalisation, which is an essential first step in designing any database. Any good book on database design will also help you with understanding the basics of relations between tables.

-Stewart

Stewart,

Thanks for the help, I am now getting somewhere, one more question. How do I get the same result for text fields I tried substituting the numeric value with text but it gave me the last alpha value which would be "w" in this case but I want the last inputted value in the field. I used the substitution below

=Nz(DMax("[StudentCompany]","Master Cert Database"))

but like I commented above it didnt work. What solution do you have for this?

David

[Stewart Ross]

Stewart,

Thanks for the help, I am now getting somewhere, one more question. How do I get the same result for text fields I tried substituting the numeric value with text but it gave me the last alpha value which would be "w" in this case but I want the last inputted value in the field. I used the substitution below

=Nz(DMax("[StudentCompany]","Master Cert Database"))

but like I commented above it didnt work. What solution do you have for this?

David

Hi David. If what you want is to set the default value of a field from the last value entered this can be done using a small amount of visual basic coding on the After Update event of the field concerned (assuming that you are using an Access Form for data entry). Open the form in design view and double-click on the field in the form to bring up its properties sheet. Right-click on the After Update property, select Build, Code, and add the following code into the skeleton Sub ctrlname_Afterupdate() procedure which Access will create for you:

Me![yourcontrolname].DefaultValue = "'" & Me![yourcontrolname] & "'"

substituting the real name of your control for yourcontrolname.

There is no simple function that does this for you without coding.

-Stewart