trouble with dmax

S

Shelly

Hi, I posted this question last week but I'm still having trouble so I'm
hoping if I explain again someone can help!
I have a table (Individual Details) with two fields which are the primary
key - Family ID Number and Individual ID Number. Individuals within each
family are numbered sequentially, starting at 1 each time a new family is
added to the database. So each person has a unique combination os Family ID
Number and Individal ID Number.

My problem is that with the database getting large, it is becoming difficult
to manually find the last highest Indivudal ID Number within an individual
number when a new person is added.

What i want to do is enter the Family ID Number on the form, then have
Access look up the next available Individual ID Number for that family and
store it in the field Individual ID Number in the table.

I've tried some codes but I get the #Name error. Help!

Thanks
 
G

Gijs Beukenoot

Shelly drukte met precisie uit :
Hi, I posted this question last week but I'm still having trouble so I'm
hoping if I explain again someone can help!
I have a table (Individual Details) with two fields which are the primary
key - Family ID Number and Individual ID Number. Individuals within each
family are numbered sequentially, starting at 1 each time a new family is
added to the database. So each person has a unique combination os Family ID
Number and Individal ID Number.

My problem is that with the database getting large, it is becoming difficult
to manually find the last highest Indivudal ID Number within an individual
number when a new person is added.

What i want to do is enter the Family ID Number on the form, then have
Access look up the next available Individual ID Number for that family and
store it in the field Individual ID Number in the table.

I've tried some codes but I get the #Name error. Help!

Thanks
Click to expand...

I think you'll need something like this (adjust fieldnames where
needed):
Me.IndividualID = DMax("IndividualID", "IndividualDetails", "FamilyID="
& CStr(Me.FamilyID))
 
G

Gijs Beukenoot

Shelly gebruikte zijn klavier om te schrijven :
Thanks... sorry to sound so stupid, but where should I enter that?
Click to expand...


Erm... If I say on the AfterUpdate event, you're probably only 50%
happy??

Let me rephrase it:
You can use a macro to do this. Click on the 3-dotted-button in the
properties screen of the FamilyIDnumber. Choose Macro
In the macro, in the dropdown, look for setvalue, set the Item to
[Individual ID number] (the name must be exactly as the name appears on
the property-screen for this control) and set the expression to
=DMax("IndividualID", "IndividualDetails", "FamilyID=" &
CStr(Me.FamilyID))
Close the macro and you're done.

You can also use code, instead of choosing macro, choose code. The code
window opens. Make sure you're at the correct event (Afterupdate of the
FamilyID). Copy or type the foolwing code :
Me.IndividualID = DMax("IndividualID", "IndividualDetails", "FamilyID="
& CStr(Me.FamilyID))
Close the code-window (and save it) and you're done.

Chooise either one of these, not both, just pick the one you're
(perhaps) already familiar with.
 
Top