update field in existing records displayed in subform from main form autonumber

[rdscott]

I have a two tables: school (one) and members (many) linked together by schoolID. There is a yes/no field (newmembers) in members to identify new members.



I have a form to create an invoice for a particular school (by selecting school from combobox) which creates an autonumber invoiceID. There is a subform to display the new members linked to that school and if newmembers is true. This displays automatically.

I want to automatically update a field (invno) in the members table as it is being displayed in the subform from the autonumber invoiceID from the main form.

Can this be done?

Thanks

[June7]

I expect there is but does this mean each member will be included on only one invoice?

[rdscott]

as new members are added to the school there is a one time charge for membership. those members need to link to the invoice they were billed on.

[June7]

Try an UPDATE sql action. Since they will no longer be NewMember need to also set that field to False. In VBA code, like:

CurrentDb.Execute "UPDATE Members SET InvNo='" & Me.InvNo & "', NewMember=False WHERE SchoolID='" & Me.SchoolID & "' AND NewMember=True;"

If InvNo and SchoolID are number datatype, remove the apostrophe delimiters.

However, better commit the invoice record to table before running the update.

[rdscott]

Thanks for your help but I can't seem to get it to work.

InvNo and SchooliD are numeric. How would i make the SQL action automatically after autonumber has been populated?

Thanks,

[June7]

I don't know if can attach code to a textbox bound to autonumber field. Never tried. Could try in the AfterUpdate event of form but I've never done that either.

Why doesn't it work - error message, wrong results, nothing happens? Where did you put the code?

[rdscott]

nothing happens... i added to autonumber field as an afterupdate event.

[June7]

Then there is the form Close or AfterUpdate events or a button Click event.

[rdscott]

got it to work. thanks for your help.

put the following code in afterupdate on first field in form. would not work without the refreshrecord

DoCmd.RefreshRecord
CurrentDb.Execute "UPDATE tblMembers SET InvoiceNo=" & Me.Invoice & " WHERE CollegeID=" & Me.CollegeID & " AND NewMember=True;"

[June7]

Don't need to change True to False?

[rdscott]

yes, I took that temporarily for testing purposes. forgot to post updated code.