Insert Into vba help!

[smellysohc]

Hi Guys-

I am fairly new to vba and here what i have going on. I have a form tied to a table. One of the fields in the form is a lookup field full of choices from another table. When the user clicks Save and Next I need to save the form and then update a 3rd table with many values from the form. The issue i'm having is the syntax error when trying to insert the data from the lookup field into another table (whose same column is the exact same lookup)

What it is expecting is the ID from that lookup field's table. I tested this by placing a random number (e.g. 22) and it will place the text in the table for that value. So i need to some how gather the right information from the form to be able to insert it the correct way into the other form...

The actual error is:
Tracker set 1 field(s) to null due to a type conversion failure

Now onto some code:


Private Sub SaveandAddtoClient_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL "INSERT INTO [Client Data Inventory History] ([Serial#], Notes, Status, Client) values('" & Serial_ & "', '" & Notes & "', 2, '" & Client & "')"
End Sub


I've tried lots of iteration of Client in the values () area... Any ideas why it's expecting the ID of the lookup table and how to get around this??

Thank you guys!!

[CreganTur]

Compare the data types of the variable you're using as Values against the dat type of the fields in the table- the error says it's a tpe conversion issue, so you're probably trying to put an incompatable type into a field.