VBA to insert values in one table from another table.

[gemadan96]

I have a Member Details form and when I add a new member I want to be able to add a record to a second table for that member with some default information.

I have the following VBA code:

Private Sub btnNewRegistration_Click()
If Me.Dirty Then Me.Dirty = False
CurrentDb.Execute "INSERT INTO Registration(ID, PrID, ProgramFee) SELECT " & Me.ID & " AS ID, PrID, ProgramFee FROM Programs WHERE PrID = " & GetDefaultProgram()
End Sub

GetDefaultProgram() is a function defined in a module in the database that defines the record to use from the Programs table.

When I run it I get a compile error: sub or function not defined. When I debug it highlights the .ID portion of Me.ID.

Any suggestions to resolve this are appreciated.

[burrina]

I may not understand your Schema,but you should not have to add Duplicate data under any circumstances. Mainform/Subform is the general way to add data and details.
tblRegistration
tblRegistrationDetails
Is this not what you are trying to do?

[June7]

ID and PrID are number type fields?

Set a breakpoint on the Execute line. Step debug. Does Me.ID show a value?

I don't see problem with that code. Maybe the issue is actually with the GetDefaultProgram procedure.

[gemadan96]

There are 3 tables. Members, Programs, and Registration. Members has contact information for the members. Programs has information for the programs members can register for. Registrations contains the information on programs the member has registered for.

What I want to do is when I add a new member I also want to register them for the default program and add the ProgramFee to the Registration table (Registration Fees may be altered for each record and therefore need to be stored in the registration table. The default program is identified by the GetDefaultProgram() function. ID is a number field identifying the member. PrID is a number field identifying the program.

I've done a similar task in another database. In that case it inserts each record from another table into the registration table for the member. So basically if there 6 programs then 6 records are inserted into the registration table for the member. What I'm trying to accomplish now is insert just the selected default record into the registration table for the member.

The button itself only appears when creating a new member.

[June7]

My comments are still applicable.

[gemadan96]

Proof that spelling counts. My original had GetDefaultProgram() when it should have been Get DefProgram()

Here's the final statement:

CurrentDb.Execute "INSERT INTO Registration(ID, [PrID], [ProgramFee]) SELECT " & Me.ID & " AS ID, [PrID], [ProgramFee] FROM Programs WHERE [PrID] = GetDefProgram()"

Thanks for the help.