dMax

[Shelly]

Hi, I have a question regarding DMax (I think this is the expression I need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number people from
1.. therefore within each family I can't have the same number recurring,
however I can have for example 4 number 1s, as they would be family 1, 2, 3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry a
number, based on their family number and the previous highest number within
that family. How can I do it? I saw that someone else used dMax with a yes/no
expression for an otherwise identical purpose, so I hope it's possisble!
Thanks in advance,
Shelly

 

[Allen Browne]

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) + 1
End Sub

 

[Shelly]

Thanks. I can't quite get it to work, I think maybe I'm writing it wrong? I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number", "Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) + 1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I have a question regarding DMax (I think this is the expression I
need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number people from
1.. therefore within each family I can't have the same number recurring,
however I can have for example 4 number 1s, as they would be family 1, 2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry a
number, based on their family number and the previous highest number
within
that family. How can I do it? I saw that someone else used dMax with a
yes/no
expression for an otherwise identical purpose, so I hope it's possisble!
Thanks in advance,
Shelly

 

[JohnFol]

You could break this down a bit. Use the Immediate Window and try the
following:

? DMax("[Individual ID Number]", "Individual Details")

This gives the highes individual number irrespective of family. Then get the
highest family number for any individual . .

? DMax("[Family ID Number]", "Individual Details")

Now get the Family ID from the form

? Me![Family ID Number]

put the bits together
DMax("Individual ID Number", "Individual Details", "[Family ID Number] = " &
Me![Family ID Number])

It might also be helpfull to post back what you mean when you say "I can't
quite get it to work".

i.e. do you get an error?

Thanks. I can't quite get it to work, I think maybe I'm writing it wrong?
I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number", "Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) + 1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I have a question regarding DMax (I think this is the expression I
need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number people
from
1.. therefore within each family I can't have the same number
recurring,
however I can have for example 4 number 1s, as they would be family 1,
2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry a
number, based on their family number and the previous highest number
within
that family. How can I do it? I saw that someone else used dMax with a
yes/no
expression for an otherwise identical purpose, so I hope it's
possisble!
Thanks in advance,
Shelly

 

[Shelly]

Thanks. I was getting runtime errors before... I have the table set up so
that the primary key is both the family number and hte individual id, maybe
this is wrong? I can enter data into the form now, and it wont let me enter
an individual ID number that has already been used within that family however
i still have to enter it manually (so this uses some gueswork as to what the
last highest number was). I entered the code that you suggested into the
immediate window but nothing is happening when I enter data into the form.
(no errors, I just have to enter the code manually). Thanks again
--
Shelly

You could break this down a bit. Use the Immediate Window and try the
following:

? DMax("[Individual ID Number]", "Individual Details")

This gives the highes individual number irrespective of family. Then get the
highest family number for any individual . .

? DMax("[Family ID Number]", "Individual Details")

Now get the Family ID from the form

? Me![Family ID Number]

put the bits together
DMax("Individual ID Number", "Individual Details", "[Family ID Number] = " &
Me![Family ID Number])

It might also be helpfull to post back what you mean when you say "I can't
quite get it to work".

i.e. do you get an error?

Thanks. I can't quite get it to work, I think maybe I'm writing it wrong?
I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number", "Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) + 1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I have a question regarding DMax (I think this is the expression I
need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number people
from
1.. therefore within each family I can't have the same number
recurring,
however I can have for example 4 number 1s, as they would be family 1,
2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry a
number, based on their family number and the previous highest number
within
that family. How can I do it? I saw that someone else used dMax with a
yes/no
expression for an otherwise identical purpose, so I hope it's
possisble!
Thanks in advance,
Shelly

 

[Allen Browne]

What happens if you open the Immediate window (press Ctrl+G), and enter the
expressions JohnFol suggested?

Include the question mark, and press Enter at the end.

Use this approach to figure out what is not working in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I was getting runtime errors before... I have the table set up so
that the primary key is both the family number and hte individual id,
maybe
this is wrong? I can enter data into the form now, and it wont let me
enter
an individual ID number that has already been used within that family
however
i still have to enter it manually (so this uses some gueswork as to what
the
last highest number was). I entered the code that you suggested into the
immediate window but nothing is happening when I enter data into the form.
(no errors, I just have to enter the code manually). Thanks again
--
Shelly

You could break this down a bit. Use the Immediate Window and try the
following:

? DMax("[Individual ID Number]", "Individual Details")

This gives the highes individual number irrespective of family. Then get
the
highest family number for any individual . .

? DMax("[Family ID Number]", "Individual Details")

Now get the Family ID from the form

? Me![Family ID Number]

put the bits together
DMax("Individual ID Number", "Individual Details", "[Family ID Number] =
" &
Me![Family ID Number])

It might also be helpfull to post back what you mean when you say "I
can't
quite get it to work".

i.e. do you get an error?

Thanks. I can't quite get it to work, I think maybe I'm writing it
wrong?
I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number",
"Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly


:

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) +
1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I have a question regarding DMax (I think this is the expression
I
need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number
people
from
1.. therefore within each family I can't have the same number
recurring,
however I can have for example 4 number 1s, as they would be family
1,
2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry
a
number, based on their family number and the previous highest number
within
that family. How can I do it? I saw that someone else used dMax with
a
yes/no
expression for an otherwise identical purpose, so I hope it's
possisble!
Thanks in advance,
Shelly

 

[Shelly]

Thanks. I think I'm getting there. I'm not getting any error messages any
more, but after I select the Family Number the Individual ID Number remains
at 0 until I change it. Any suggestions?
--
Shelly

What happens if you open the Immediate window (press Ctrl+G), and enter the
expressions JohnFol suggested?

Include the question mark, and press Enter at the end.

Use this approach to figure out what is not working in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I was getting runtime errors before... I have the table set up so
that the primary key is both the family number and hte individual id,
maybe
this is wrong? I can enter data into the form now, and it wont let me
enter
an individual ID number that has already been used within that family
however
i still have to enter it manually (so this uses some gueswork as to what
the
last highest number was). I entered the code that you suggested into the
immediate window but nothing is happening when I enter data into the form.
(no errors, I just have to enter the code manually). Thanks again
--
Shelly

You could break this down a bit. Use the Immediate Window and try the
following:

? DMax("[Individual ID Number]", "Individual Details")

This gives the highes individual number irrespective of family. Then get
the
highest family number for any individual . .

? DMax("[Family ID Number]", "Individual Details")

Now get the Family ID from the form

? Me![Family ID Number]

put the bits together
DMax("Individual ID Number", "Individual Details", "[Family ID Number] =
" &
Me![Family ID Number])

It might also be helpfull to post back what you mean when you say "I
can't
quite get it to work".

i.e. do you get an error?


Thanks. I can't quite get it to work, I think maybe I'm writing it
wrong?
I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number",
"Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly


:

In the BeforeUpdate event of the *form* where you enter these people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0) +
1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I have a question regarding DMax (I think this is the expression
I
need
to use).
My database has several families, each which have a unique ID number
("Family ID Number"), then within each family I have to number
people
from
1.. therefore within each family I can't have the same number
recurring,
however I can have for example 4 number 1s, as they would be family
1,
2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new entry
a
number, based on their family number and the previous highest number
within
that family. How can I do it? I saw that someone else used dMax with
a
yes/no
expression for an otherwise identical purpose, so I hope it's
possisble!
Thanks in advance,
Shelly

 

[Allen Browne]

What do you get in the Immediate window?

Keep working in the expression until you get it right.

For an explanation of how to supply the 3 arguments for DMax(), DLookup(),
etc, see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I think I'm getting there. I'm not getting any error messages any
more, but after I select the Family Number the Individual ID Number
remains
at 0 until I change it. Any suggestions?
--
Shelly

What happens if you open the Immediate window (press Ctrl+G), and enter
the
expressions JohnFol suggested?

Include the question mark, and press Enter at the end.

Use this approach to figure out what is not working in your code.

Thanks. I was getting runtime errors before... I have the table set up
so
that the primary key is both the family number and hte individual id,
maybe
this is wrong? I can enter data into the form now, and it wont let me
enter
an individual ID number that has already been used within that family
however
i still have to enter it manually (so this uses some gueswork as to
what
the
last highest number was). I entered the code that you suggested into
the
immediate window but nothing is happening when I enter data into the
form.
(no errors, I just have to enter the code manually). Thanks again
--
Shelly


:

You could break this down a bit. Use the Immediate Window and try the
following:

? DMax("[Individual ID Number]", "Individual Details")

This gives the highes individual number irrespective of family. Then
get
the
highest family number for any individual . .

? DMax("[Family ID Number]", "Individual Details")

Now get the Family ID from the form

? Me![Family ID Number]

put the bits together
DMax("Individual ID Number", "Individual Details", "[Family ID Number]
=
" &
Me![Family ID Number])

It might also be helpfull to post back what you mean when you say "I
can't
quite get it to work".

i.e. do you get an error?


Thanks. I can't quite get it to work, I think maybe I'm writing it
wrong?
I
entered this:

Private Sub Individual_ID_Number_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[Individual ID Number] = Nz(DMax("Individual ID Number",
"Individual
Details", strWhere), 0) + 1
End Sub

but evidently I've stuffed something up!
--
Shelly


:

In the BeforeUpdate event of the *form* where you enter these
people.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "[Family ID Number] = " & Me.[Family ID Number]
Me.[PersonID] = Nz(DMax("PersonNumber", "Table1", strWhere), 0)
+
1
End Sub


Hi, I have a question regarding DMax (I think this is the
expression
I
need
to use).
My database has several families, each which have a unique ID
number
("Family ID Number"), then within each family I have to number
people
from
1.. therefore within each family I can't have the same number
recurring,
however I can have for example 4 number 1s, as they would be
family
1,
2,
3,
4 and so on (I have to start each family with 1).
What I want to do is use dMax to automatically assign each new
entry
a
number, based on their family number and the previous highest
number
within
that family. How can I do it? I saw that someone else used dMax
with
a
yes/no
expression for an otherwise identical purpose, so I hope it's
possisble!

 

[Shelly]

Thanks Allen! I'll kep trying until I get there.. I'm sure it's something
simple!