Populating Table through VBA

[branston]

Hi all,

Im sure there is a way to do this but im drawing a blank... I have some code, and at one point I want it to open a form, create a new record and then populate that record with data. The data i want it to populate is quite simple - 1 field needs to show a maximum + 1 of another field which ive worked out and stored as a variable, and the other 1 just needs to show the date now(). I thought it would be something super easy like

[Record1]=intMax + 1
[Record2]=now()

but it doesnt seem to be so :-(
Any help would be greatly appreciated!

Thank you

 

[branston]

oops, just realised i forgot to put the error in!
It comes up with an ever-so-helpful:
'Microsoft Office Access can't find the field "|" referred to in your experssion'

 

[Scooterbug]

You have to tell it which field that you want populated...

me.record1 = intMax + 1
me.record2 = now()

I am assuming the fields on the form you are trying to populate are called record1 and record2 respectfully.

 

[branston]

Ah, wonderful. I will try that. Thank you.

 

[branston]

thanks for the input scooterbug, but it still doesnt seem to be working :-(
When i ask it to put the number i stored in a message box it works fine, so the variable bit is working, i just cant seem to get it to populate the record.
Any other sugestions?

 

[Rabbie]

Can you post the full code you are using to add the records or better still post a copy of your DB.

 

[branston]

Thanks for the help, but i found a previous post that worked... I was loath to use it at 1st as i had never done anything with recordsets, but it worked a treat:

Dim intMax As Integer
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

intMax = DMax("MeetingNo", "TblMeeting") 'find max meeting no from table tblmeeting
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblMeeting", dbOpenDynaset) 'set MyRS as the table you want the data added/changed

MyRS.MoveFirst

MyRS.AddNew 'Add new record to MyRS
MyRS![MeetingNo] = intMax + 1 'Set the meetingNo on MyRS to the max + 1
MyRS![MeetingDate] = Now() 'Set the meetingDate on MyRS to now
MyRS.Update

MyRS.Close
Set MyRS = Nothing

Thats anyway for all your help. Im always pleasantly surprised with how quick people get back to me on this site!

 

[Rabbie]

Thanks for the help, but i found a previous post that worked... I was loath to use it at 1st as i had never done anything with recordsets, but it worked a treat:

Dim intMax As Integer
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

intMax = DMax("MeetingNo", "TblMeeting") 'find max meeting no from table tblmeeting
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblMeeting", dbOpenDynaset) 'set MyRS as the table you want the data added/changed

MyRS.MoveFirst

MyRS.AddNew 'Add new record to MyRS
MyRS![MeetingNo] = intMax + 1 'Set the meetingNo on MyRS to the max + 1
MyRS![MeetingDate] = Now() 'Set the meetingDate on MyRS to now
MyRS.Update

MyRS.Close
Set MyRS = Nothing

Thats anyway for all your help. Im always pleasantly surprised with how quick people get back to me on this site!

Happy to hear its all working for you now