Access VBA code to insert into two different tables

Question

I have a user form that inserts into "user" table and "organizer" table when i click a button, user data gets inserted with no problem, but organizer table data doesn`t. it gives me an error saying "ms access set 1 filed to null due to type conversion failure"

NOTE: user_id is primary key in user table and foreign key in organizer table. its data type is long integer.

i would appreciate any help

Private Sub InsertSqlIntoOrgTable()

   Dim orgUserId As Long, sqlOrgInsertUsrId As String

   orgUserId = getLastUserId()

   sqlOrgInsertUsrId = "INSERT INTO ORGANIZER (USER_ID) VALUES ('&orgUserId&')"

   DoCmd.RunSQL sqlOrgInsertUsrId

End Sub

This function gets the last user_id that got inserted to use in the above code to insert it into the organizer table

Private Function getLastUserId() As Long

    Dim lastUserId As Long

    getLastUserId = DMax("USER_ID", "USER")

    getLastUserId = lastUserId

End Function

Edit:

My type conversion failure error has been resolved by modifying my sql statement into

 sqlOrgInsertUsrId = "INSERT INTO ORGANIZER (USER_ID) VALUES (" & orgUserId & ")"

Now i want to add "organization_name" which is taken from the same user from, into the "organizer" table. in the same Sub above, I added stuff for doing this, but it prompted me to inter the value (organization_name) again in a small dialogue box, then gave me another error saying "ms access set one row null due to validation rule violation"

NOTE: i didn`t put any validation rule for the "organization_name" anywhere

Private Sub InsertSqlIntoOrgTable()
   Dim orgName As String, SqlOrgNameInsert As String

   orgName = txtOrgName.Value  'takes the value from text field
   SqlOrgNameInsert = "INSERT INTO ORGANIZER (ORG_NAME)     VALUES (" & orgName & ") "

   DoCmd.RunSQL SqlOrgNameInsert

End Sub

Any help?

i changed the sql command for inserting user id into organizer to: sqlOrgInsertUsrId = "INSERT INTO ORGANIZER (USER_ID) VALUES (" & orgUserId & ")" AND it worked :) — user3713255
That's a new question. Please accept an answer here and create a new question, or things get rather messy. — Andre

Andre Andre · Accepted Answer · 2015-12-07T10:23:49

Your concatenation is wrong. It must be

sqlOrgInsertUsrId = "INSERT INTO ORGANIZER (USER_ID) VALUES (" & orgUserId & ")"

That takes care of the type conversion failure, afterwards the correction by cboden applies.

Access VBA code to insert into two different tables

2 Answers