SQL Insert Errors

[weilerdo]

Hi All, I am trying to get code for a button to work. I keep getting errors everytime. I get error 1334 if I do seperate DoCmd lines. If I try both on 1 line I get 2943 errors. Im still pretty wet behind the ears on VB code so any help would be greatly appreciated.

I have tried like this: DoCmd.RunSQL strSQL1, strSQL2 and like
this DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2

Here is my full code so far. I actually need to do 3 of them.

Private Sub Command24_Click()
Dim strSQL1 As String
Dim strSQL2 As String


strSQL1 = "INSERT INTO MASTER1 (PART_DES, PART_NO, PART_LOC, MINIMUM) VALUES ('" & Me.PART_DES & "', '" & Me.PART_NO & "', '" & Me.PART_LOC & "', '" & Me.MINIMUM & "');"
strSQL2 = "INSERT INTO INVEN ( PART_DES, STOCK, DATE, TOTAL_ON, LAST_PRI, RPDATE) VALUES ('" & Me.PART_DES & "', '" & Me.Text31 & "', '" & Me.DATE & "', '" & Me.Text31 & "', '" & Me.V1LAST_PRI & "', '" & Me.DATE & "');"


DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2

End Sub​

Thank You for any assistance
Don

 

[wazz]

i could be wrong but i think the data type in the sql statement has to match the field data type, so, you have quotes around every value but numbers don't need quotes and dates need to be surrounded by #. hth.

 

[weilerdo]

That did not seem to get it. I went back and checked all of my fields. It seems to be a syntax error my DoCmd lines. Do you know if there is a limit to how many fields can be updated.

 

[wazz]

i was just fooling around with it and i still think it's your sql statements:

for ex, if DATE is a date field and TOTAL_ON is a number and RPDATE is a date:

strSQL2 = & _
"INSERT INTO INVEN ( PART_DES, STOCK, DATE, TOTAL_ON, LAST_PRI, RPDATE)" & _
"VALUES ('" & Me.PART_DES & "', '" & Me.Text31 & "', #" & Me.DATE & "#, " & Me.Text31 & ", '" & Me.V1LAST_PRI & "', #" & Me.DATE & "#);"

note that i also removed the single-quotes around Me.Text31. not sure if there's a limit, but the limit would be much higher than this for sure.

EDIT: i just noticed you have two controls called Text31. Maybe that's it?? also, use two separate DoCmd.RunSQL statements.

 

[weilerdo]

I tried that and I am still getting the 3134 error. I am beginning to wonder if I have some kind of corruption underlying in it. I tried REMing out my statement and running just yours and still getting it. I have attached a copy of it for you to see if maybe I am missing something on this.


Thanks again for all your help with this.

 

[wazz]

i don't have a final answer but found a few things:

- field in master1 is COMMENT, in query it's COMMENTS;

- field in inven is STOCKED, in query it's STOCK;
- field in inven is RP_DATE, in query it's RPDATE.

- rename the date field in the table (date is a reserved word - use lastupdate or sim.)
- also rename the date control on the form.

- the RP_DATE field is a text field so there will be a problem storing the default value (now()) from the form. you'll need a new field or convert what you have.

- i got it working by making the above changes, except for the DATE field. there's definitely something going on there. maybe it will sort itself for you after making the changes. post back with news.

 

[weilerdo]

Hey Wazz, Nice find, I think I have been looking at this thing to long I should have seen those. I changed them and for now I took the default value out of the RP_Date field. In your post you said you got it to work? I am still getting errors. I keep getting a Compile Error on the Me.UNIT_MEAS Method or data member not found. So I tried taking that 1 out of the query and the compile error seems to just keep moving to the left everytime. I had double and triple checked and the field in MASTER1 UNIT_MEAS is there and its a text field. My field on the form is named UNIT_MEAS and is a text field as well so IM not sure why it keeps giving me this error.

 

[wazz]

i only got the rp_date field to work after adding a new field in the table and making it a date type instead of text.

i took out all the fields except UNIT_MEAS from the other query and tested it alone and it works fine.

i think if you really want to keep going with the unbound form you should start from scratch renaming the table fields and form controls and getting the data types and syntax for the query right for each item added - iow, add one field at a time to the query and test your insert statement, then add another field and so on; if you have a problem with one, test it separately until you get the syntax. so far these are the only things slowing you down. i would go with a bound form with lookup tables and comboboxes to save reentering the same thing over and over, but maybe you already have those somewhere else.

btw: i also made the form popup=no. i don't know if that's relevant or not, prob not.

 

[weilerdo]

Thanks Wazz, Yeah I do I think I will try and start the form over again. I wish I could come up with an easier way to do this form. It's a new part entry form so the user needs to add all of those fields that go into the Master table, but I also need it to add a entry to the Inven table to be able to track how many of a part we have and when we use them. Unfortunately I am very limited to changing a lot of the fields and format as this database was orginallly written in Borland DB2 running on a windows 3.0 machine and my boss has now decided that its time to convert it over to something a little newer. But he wants the fell it it was.