find highest number less than 1000

[kishanpatel50]

hi

im trying to look for code that will look in the MemberID field in MembersAndDaughter table and calculate the highest number less than 1000.

i think i need to use the DMAX function and tried this but didnt work

Private Sub Test_Click()
Dim memid As Integer

memid = DMax( MembersAndDaughter.MemberID, <1000 )
End Sub

 

[raskew]

Hi -

Try this:

memid = DMax("[MemberID]", "MembersAndDaughter", "[MemberID] < 1000")

HTH, Bob

 

[kishanpatel50]

hey thanks alot for that...worked like a charm

now how would i use the variable in the code below (variable renamed to NewMemID)

i think the code is assuming the variable, NewMemID, as a field but doesnt find it so it asks me to input some data.


Private Sub Command16_Click()

Dim NewMemID As Long

NewMemID = DMax("[MemberID]", "MembersAndDaughters", "[MemberID] < 1000") + 1

DoCmd.RunSQL "INSERT INTO MembersAndDaughters ( MemberID, MemberName, MemberFatherName, MemberGrandFatherName, MemberSurname ) " & _
"SELECT NewMemID, SiblingName, SiblingFatherName, SiblingGrandFatherName, SiblingSurname"
End Sub

 

[raskew]

Hi -

Where is the data coming from that you want to append?

Bob

 

[kishanpatel50]

ok

i have two tables
MembersAndDaughter
and
Siblings

and a form for each...

i have a button on each sibling record saying "move to MembersAndDaughter"
i have the code that will copy the details to the MembersAndDaughters table but i dont want to copy over the SiblingID because it is the same as their Fathers ID (MemberID)

so i wanted to get the highest value for memberID and add 1 to it which i have successfully done.
with this code

Private Sub Command16_Click()

Dim NewMemID As Long

NewMemID = DMax("[MemberID]", "MembersAndDaughters", "[MemberID] < 1000") + 1

but now i want to insert that number into the MemberID field instead of the SiblingID

im not using an append query..im doing it sraight by using sql i think...

i tried this but it didnt work..i think its because the code is assuming NewMemID is a filed but i want it to be the variable...

Private Sub Command16_Click()

Dim NewMemID As Long

NewMemID = DMax("[MemberID]", "MembersAndDaughters", "[MemberID] < 1000") + 1

DoCmd.RunSQL "INSERT INTO MembersAndDaughters ( MemberID, MemberName, MemberFatherName, MemberGrandFatherName, MemberSurname ) " & _
"SELECT NewMemID, SiblingName, SiblingFatherName, SiblingGrandFatherName, SiblingSurname"
End Sub