By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 1,783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

DMax function

P: n/a
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_cmdNewOnlineMbr_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 "qryAddNewMembers"
Set db = CurrentDb()
Set rsOnlineDb = db.OpenRecordset("Member Updates")
Set rsInhouseDb = db.OpenRecordset("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!FirstName
!MI = rsOnlineDb!MI
!LAST = rsOnlineDb!LastName
!MBRSHPEXP = rsOnlineDb!MBRSHPEXP
!NOTES = rsOnlineDb!NOTES
!EMPLOYER = rsOnlineDb!EMPLOYER
!POSITION = rsOnlineDb!PositionTitle
!ADDRESS1 = rsOnlineDb!ADDRESS1
!ADDRESS2 = rsOnlineDb!ADDRESS2
!ADDRESS3 = rsOnlineDb!ADDRESS3
.Update
rsOnlineDb.MoveNext
Loop
End With
rsInhouseDb.Close
rsOnlineDb.Close
Exit_cmdNewOnlineMbr_Click:
Exit Sub

Err_cmdNewOnlineMbr_Click:
MsgBox Err.Description
Resume Exit_cmdNewOnlineMbr_Click

End Sub
Sep 10 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
nr****@gmail.com 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_cmdNewOnlineMbr_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 "qryAddNewMembers"
Set db = CurrentDb()
Set rsOnlineDb = db.OpenRecordset("Member Updates")
Set rsInhouseDb = db.OpenRecordset("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!FirstName
!MI = rsOnlineDb!MI
!LAST = rsOnlineDb!LastName
!MBRSHPEXP = rsOnlineDb!MBRSHPEXP
!NOTES = rsOnlineDb!NOTES
!EMPLOYER = rsOnlineDb!EMPLOYER
!POSITION = rsOnlineDb!PositionTitle
!ADDRESS1 = rsOnlineDb!ADDRESS1
!ADDRESS2 = rsOnlineDb!ADDRESS2
!ADDRESS3 = rsOnlineDb!ADDRESS3
.Update
rsOnlineDb.MoveNext
Loop
End With
rsInhouseDb.Close
rsOnlineDb.Close
Exit_cmdNewOnlineMbr_Click:
Exit Sub

Err_cmdNewOnlineMbr_Click:
MsgBox Err.Description
Resume Exit_cmdNewOnlineMbr_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.
Sep 11 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.