DMax function

[nrtyme@gmail.com]

Hello,

I need to add several new records to Table2 from Table1. Table2
contains a field called [Member Number] that needs to be the previous
maximum value of [Member Number] incremented by 1.

Below is my code but i keep getting the error "MyDatabase " can't find
the field '|' referred to in your expression. Please help, I am going
crazy here :)!

Private Sub cmdNewOnlineMbr _Click()
On Error GoTo Err_cmdNewOnlin eMbr_Click

Dim db As Database ' Current database
Dim rsOnlineDb As Recordset ' Source table
Dim rsInhouseDb As Recordset ' Target table
Dim i As Integer ' Loop counter

DoCmd.OpenQuery "qryAddNewMembe rs"
Set db = CurrentDb()
Set rsOnlineDb = db.OpenRecordse t("Member Updates")
Set rsInhouseDb = db.OpenRecordse t("New Members")
With rsInhouseDb
' Loop through all records in source table.
Do While Not rsOnlineDb.EOF
' Add all new records to target table.
.AddNew
!MEMBERNUMBER = DMax([MEMBER NUMBER], Members) + 1
!FIRST = rsOnlineDb!Firs tName
!MI = rsOnlineDb!MI
!LAST = rsOnlineDb!Last Name
!MBRSHPEXP = rsOnlineDb!MBRS HPEXP
!NOTES = rsOnlineDb!NOTE S
!EMPLOYER = rsOnlineDb!EMPL OYER
!POSITION = rsOnlineDb!Posi tionTitle
!ADDRESS1 = rsOnlineDb!ADDR ESS1
!ADDRESS2 = rsOnlineDb!ADDR ESS2
!ADDRESS3 = rsOnlineDb!ADDR ESS3
.Update
rsOnlineDb.Move Next
Loop
End With
rsInhouseDb.Clo se
rsOnlineDb.Clos e


Exit_cmdNewOnli neMbr_Click:
Exit Sub

Err_cmdNewOnlin eMbr_Click:
MsgBox Err.Description
Resume Exit_cmdNewOnli neMbr_Click

End Sub

[Salad]

Re: DMax function

nrtyme@gmail.co m wrote:

Hello,
>
I need to add several new records to Table2 from Table1. Table2
contains a field called [Member Number] that needs to be the previous
maximum value of [Member Number] incremented by 1.
>
Below is my code but i keep getting the error "MyDatabase " can't find
the field '|' referred to in your expression. Please help, I am going
crazy here :)!
>
Private Sub cmdNewOnlineMbr _Click()
On Error GoTo Err_cmdNewOnlin eMbr_Click
>
Dim db As Database ' Current database
Dim rsOnlineDb As Recordset ' Source table
Dim rsInhouseDb As Recordset ' Target table
Dim i As Integer ' Loop counter
>
DoCmd.OpenQuery "qryAddNewMembe rs"
Set db = CurrentDb()
Set rsOnlineDb = db.OpenRecordse t("Member Updates")
Set rsInhouseDb = db.OpenRecordse t("New Members")
With rsInhouseDb
' Loop through all records in source table.
Do While Not rsOnlineDb.EOF
' Add all new records to target table.
.AddNew
!MEMBERNUMBER = DMax([MEMBER NUMBER], Members) + 1
!FIRST = rsOnlineDb!Firs tName
!MI = rsOnlineDb!MI
!LAST = rsOnlineDb!Last Name
!MBRSHPEXP = rsOnlineDb!MBRS HPEXP
!NOTES = rsOnlineDb!NOTE S
!EMPLOYER = rsOnlineDb!EMPL OYER
!POSITION = rsOnlineDb!Posi tionTitle
!ADDRESS1 = rsOnlineDb!ADDR ESS1
!ADDRESS2 = rsOnlineDb!ADDR ESS2
!ADDRESS3 = rsOnlineDb!ADDR ESS3
.Update
rsOnlineDb.Move Next
Loop
End With
rsInhouseDb.Clo se
rsOnlineDb.Clos e
>
>
Exit_cmdNewOnli neMbr_Click:
Exit Sub
>
Err_cmdNewOnlin eMbr_Click:
MsgBox Err.Description
Resume Exit_cmdNewOnli neMbr_Click
>
End Sub

I'll assume the line
!MEMBERNUMBER = DMax([MEMBER NUMBER], Members) + 1
is the culprit/problem from what I gleaned in your post.

Typically I use " around each item; field name, table name, and criteria
if that is included.

For example, in the code line above, you have no reference to Members as
a variable. I might use the syntax oif
!MEMBERNUMBER = DMax("MEMBER NUMBER", "Members") + 1

Another thing I noticed...you don't update the Members table in your
code. I would think that as you loop thru the records the new records
would contain the same MemberNumber + 1 which I figure is not what you
want. Perhaps after updating the Members table you want to update the
members table as well so you stay in synch with the Max number.