Inputting info from a form into two tables

[TaylorLeonard]

Hi, I have been trying to figure this out for hours, but can't seem to get it. I have a form where the administrator inputs someone elses personal info (FirstName, lastName, email, phone#, etc.) and when they click enter, I have it so a new entry is created in my Volunteer table. What i would like to do it, is when the admin clicks the enter button, a new entry will be made in the Password table. When a new record is added to the Volunteer table, the primary key is the ID which is an autonumber. The password table is linked by foreign key to the Volunteer table by a UserID which is equal to the ID primary key in the User table. My goal was to click enter, create the new entry in the Volunteer table, then open the new recordset and create a new entry in the password table, with the UserID = ID and the username and password being equal to the first and last names respectively. This would simply be the default username and password for the user until they logged in themselves and changed their username and password. I tried doing this in VBA by attempting to select the largest ID number (largest being the most recent) and then using INSERT INTO to put the into into the password table. What I atttempted is below.


Private Sub Enter_Click()

DoCmd.GoToRecord , , acNewRec
Me.TextNew1.Value = Me.TextFirstName.Value
Me.TextNew2.Value = Me.TextLastName.Value

DoCmd.OpenTable "Password"
DoCmd.GoToRecord , , acNewRec

DoCmd.RunSQL "INSERT INTO Password(UserID, UserName, Password) SELECT Max(Volunteer.ID), Volunteer.FirstName, Volunteer.LastName FROM Volunteer;"

End Sub


I appreciate any help.

-Taylor

[TaylorLeonard]

I modified my code, so that it looks like what's below. I know the query works because i can run it by itself to append the Password table (i just have to enter the Volunteer.ID value). However, when i actually use the form, i get an error after it runs through the query that says "Run-time error '13': Type Mismatch" and i do not know what this means nor do i understand how to fix it. Any help?


Thanks,

Taylor



Private Sub Enter_Click()

DoCmd.GoToRecord , , acNewRec
Me.TextNew1.Value = Me.TextFirstName.Value
Me.TextNew2.Value = Me.TextLastName.Value

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Password(UserID,UserName,Password) SELECT Volunteer.ID, New.FirstName, New.LastName FROM Volunteer WHERE ((Volunteer.ID) = [Forms]![Volunteer]![Text67])"
DoCmd.SetWarnings True

End Sub