Hi everyone, I’m trying to figure out how to transfer data in access via the SQL command and I’m not having much luck in figuring it out on my own.

Here is the set up. The form I have created is unbound that will gather all require data from the user for issuing and RMA Number (Return Material Authorization). Upon entering the data the form will lookup the last record number and will add one to it. This will be the new RmaNumber (string variable) it will also automatically pull the current user name (string variable) with along with all the specified form fields and add it to the table as a new record. Please note the form and table fields are ordered by relation. Also please note the code I’m using… or trying to use and advise. The problem occurs at the Dcmd.runsql when the code gets to this point I get a syntax error saying“syntax error in Insert Into statement”

The form is named “frm-RmaRecordCreation”

CheckExpeditedOrder = Checkbox (Boolean)
TextRmaCompanyName = Text
TextRmaStreetAdd1 = Text
TextRmaStreetAdd2 = Text
TextRmaCity = Text
TextRmaState = Text
TextRmaZipCode = Text
TextRmaCountry = Text
TextRmaTelephone = Text
TextRmaExt = Text
TextRmaFax = Text
TextRmaE-mail = Text
TextRmaPONumber = Text
TextRmaCarrierAcc = Text
CheckNistCertification = Checkbox (Boolean)
TextRmaNotes = Text
VarUserName = Variable String (from Public module)
NewRmaNumber = Variable String (Dim for current form)

These are to be recorded into

The table is named “Tbl-RmaNumberLog”

ExpeditedOrder = Checkbox (Boolean)
RmaCompanyName = Text
RmaStreetAdd1 = Text
RmaStreetAdd2 = Text
RmaCity = Text
RmaState = Text
RmaZipCode = Text
RmaCountry = Text
RmaTelephone = Text
RmaExt = Text
RmaFax = Text
RmaE-mail = Text
RmaPONumber = Text
RmaCarrierAcc = Text
NistCertification = Checkbox (Boolean)
TextRmaNotes = Text
RecordCreator = Text
RmaNumber = Text

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Turn off warnings to bypass table data insertion verification
DoCmd.SetWarnings False

MsgBox "warning Off"


' define table and values to be recorded into it
DoCmd.RunSQL ("INSERT INTO tbl-RmaNumberLog([ExpeditedOrder], [RmaCompanyName], [RmaStreetAdd1]," & _
"[RmaStreetAdd2], [RmaCity], [RmaState], [RmaZipCode], [RmaCountry]," & _
"[RmaTelephone], [RmaExt], [RmaFax], [RmaE-mail ], [RmaPONumber],[RecordCreator], [RmaNumber]" & _
"[RmaCarrierAcc], [NistCertification], [RmaNotes]" & _
"VALUES('[CheckExpeditedOrder]', '[TextRmaCompanyName]', '[TextRmaStreetAdd1]'," & _
"'[TextRmaStreetAdd2]', '[TextRmaCity]', '[TextRmaState]', '[TextRmaZipCode]', '[TextRmaCountry]'," & _
"'[TextRmaTelephone]', '[TextRmaExt]', '[TextRmaFax]', '[TextRmaE-mail]', '[TextRmaPONumber]'," & _
"'[TextRmaCarrierAcc]', '[CheckNistCertification]', '[TextRmaNotes]', VarUserName, NewRmaNumber);")

' Turn on warnings in case something happens from here on in
DoCmd.SetWarnings True

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

I have noticed that some people place the code into a string variable and then execute the variable, while others just execute the code directly. Is there any benefit or drawbacks with either approach or is it just a preference. Also is a Boolean considered String or Interger for the data transfer? Or does it have its own category? Text from what I understand has to be surrounded with single quotes while numbers are ok as is… what about Boolean?

Hope you guys can help since I have spent literally days trying to figure it out on my own.

Sincerely,

Luis

It is preferable NOT to build the string and execute it at the same time. Access has a problem with that. So create a string variable (sSQL) and do this:

sSQL = "INSERT INTO tbl-RmaNumberLog([ExpeditedOrder], [RmaCompanyName], [RmaStreetAdd1]," & _
"[RmaStreetAdd2], [RmaCity], [RmaState], [RmaZipCode], [RmaCountry]," & _
"[RmaTelephone], [RmaExt], [RmaFax], [RmaE-mail ], [RmaPONumber],[RecordCreator], [RmaNumber]" & _
"[RmaCarrierAcc], [NistCertification], [RmaNotes]" & _
" VALUES('[CheckExpeditedOrder]', '[TextRmaCompanyName]', '[TextRmaStreetAdd1]'," & _
"'[TextRmaStreetAdd2]', '[TextRmaCity]', '[TextRmaState]', '[TextRmaZipCode]', '[TextRmaCountry]'," & _
"'[TextRmaTelephone]', '[TextRmaExt]', '[TextRmaFax]', '[TextRmaE-mail]', '[TextRmaPONumber]'," & _
"'[TextRmaCarrierAcc]', '[CheckNistCertification]', '[TextRmaNotes]', VarUserName, NewRmaNumber)"

DoCmd.RunSQL sSQL

Note I also removed the parens, and the closing ; since it won't be needed.

Also, you are not doing any data validation on this string, so will you end up with Nulls? Are you worried about SQL injection? Also, I added a space before VALUES.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Oops, you are missing a closing parens on the column list as well:

sSQL = "INSERT INTO tbl-RmaNumberLog([ExpeditedOrder], [RmaCompanyName], [RmaStreetAdd1]," & _
"[RmaStreetAdd2], [RmaCity], [RmaState], [RmaZipCode], [RmaCountry]," & _
"[RmaTelephone], [RmaExt], [RmaFax], [RmaE-mail ], [RmaPONumber],[RecordCreator], [RmaNumber]" & _
"[RmaCarrierAcc], [NistCertification], [RmaNotes])" & _
" VALUES('[CheckExpeditedOrder]', '[TextRmaCompanyName]', '[TextRmaStreetAdd1]'," & _
"'[TextRmaStreetAdd2]', '[TextRmaCity]', '[TextRmaState]', '[TextRmaZipCode]', '[TextRmaCountry]'," & _
"'[TextRmaTelephone]', '[TextRmaExt]', '[TextRmaFax]', '[TextRmaE-mail]', '[TextRmaPONumber]'," & _
"'[TextRmaCarrierAcc]', '[CheckNistCertification]', '[TextRmaNotes]', VarUserName, NewRmaNumber)"


mmcdonal

Look it up at: http://wrox.books24x7.com

Can Access REALLY pull values from form fields like that right into the middle of quoted strings???

That is, when you do

SQL = "INSERT INTO table (dbfield) VALUES('[formfield]')"
DoCmd.RunSQL SQL

Does it really see the [formfield] as the name of some field out in the FORM and pull in the value of that field in place of [formfield]??? I could understand that happening if the apostrophes were not around the [formfield] but surely when you use '[formfield]' you just defeated the field replacement and specified that as a literal string. NO?

And while I'm asking...how does Access know *WHICH* form to get the form fields from???

Sorry, obviously not an Access programmmer.

Are you reeeeealllyyyy just asking rhetorical questions to make you feel superior? Reeeeaaallly? What are you, 12? (He asked rhetorically)

Sorry Luis, sometimes people overlook the obvious. I apologize for Old Pedant's condescending post as well.

What he is saying is that you need to break the string to add the control values, and refer to the form fields by also referencing the form. I usually take the values into variables myself.

So:

sSQL = "INSERT INTO tbl-RmaNumberLog([ExpeditedOrder], [RmaCompanyName], [RmaStreetAdd1]," & _
"[RmaStreetAdd2], [RmaCity], [RmaState], [RmaZipCode], [RmaCountry]," & _
"[RmaTelephone], [RmaExt], [RmaFax], [RmaE-mail ], [RmaPONumber],[RecordCreator], [RmaNumber]" & _
"[RmaCarrierAcc], [NistCertification], [RmaNotes])" & _
" VALUES('[CheckExpeditedOrder]', '[TextRmaCompanyName]', '[TextRmaStreetAdd1]'," & _
"'[TextRmaStreetAdd2]', '[TextRmaCity]', '[TextRmaState]', '[TextRmaZipCode]', '[TextRmaCountry]'," & _
"'[TextRmaTelephone]', '[TextRmaExt]', '[TextRmaFax]', '[TextRmaE-mail]', '[TextRmaPONumber]'," & _
"'[TextRmaCarrierAcc]', '[CheckNistCertification]', '[TextRmaNotes]', VarUserName, NewRmaNumber)"


Should be more like:

sSQL = "INSERT INTO tbl-RmaNumberLog([ExpeditedOrder], [RmaCompanyName], [RmaStreetAdd1]," & _
"[RmaStreetAdd2], [RmaCity], [RmaState], [RmaZipCode], [RmaCountry]," & _
"[RmaTelephone], [RmaExt], [RmaFax], [RmaE-mail ], [RmaPONumber],[RecordCreator], [RmaNumber]" & _
"[RmaCarrierAcc], [NistCertification], [RmaNotes])" & _
" VALUES('" & Me.CheckExpeditedOrder & "', '" & Me.TextRmaCompanyName & "', '" & Me.TextRmaStreetAdd1 & "'," etc.

Normally I would do data validation on the form since I do a lot of Access / SQL and don't want to throw SQL Server errors at my users. So I would do this:

Dim sEOrder As Boolean
...

sEOrder = Me.CheckExpeditedOrder 'without validation here
...

sSQL = "INSERT INTO tblMyTable(Col1, Col2, Col3...) VALUES(" & sEOrder & ", " etc.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Hello mmcdonal,

Your points have been very insightful. I tried to run the first codes earlier on and got error codes. I realized that I was missing the form pointer as you and Old pendant mentioned and got it to work last night which I am proud to say mirrors yours ( before looking at your post,,, I must be learning something after all). A pesky error remained with the checkboxes but after seeing your post and your use of the variables it cleared it right up ( I will definitely keep that in mind from now on). Thanks a lot for the assist I truly do Appreciate it.

If I could touch on a few things you mentioned earlier. You said that errors may occur if you declare and run the Sql in one step in VB. What kind of errors are those in general terms?

You made a good observation on the data verification. I'm somewhat Green on the whole access VB scene. In the past I made programs by using a whole bunch of queries forms and not much else. Now I’m trying to get a little more experience by forcing myself to go SQL whenever possible.

It was always my intention to do the verification but I figured one battle at a time. I'm not sure if this should be a new post item or if you could help cover it here how do you handle data verification? Should it be a module of some sort or embedded in the form... or perhaps a combination of the two?

Just want to thank you again for the help, I read many posts without success but your approach was very clear and I feel I have a better grasp of the subject now. I'll need to polish it a bit but I can see the relative way things fit together now.

Luis

Hi,

   I am glad that worked out for you. I really had to smack myself in the forehead after Old Pedant's post.

   Generally when you try to run a SQL string at the same time you are taking data, you will get a data type mismatch or similar error. Also, if you are not doing validation, then you might run into problems if your table requires certain data to have been inserted.

   Data validation can be done at the table level. This throws an error that your user's might not appreciate. It is very effective, however, and works regardless of where the data is coming from, so alleviates the need to code on the forms.

   I usually will check the data types in the code as I am assigning the control values to a variable. For required data, I can use:

If IsNull(Me.TextControl) Or Me.TextControl = "" Then
   MsgBox "Please enter a value.", vbInformation
   Exit Sub
Else
   sString = Me.TextControl
End If

Or for Integers or Long variables:

If IsNull(Me.NumberControl) Or Me.NumberControl = 0 Then
   MsgBox "Please enter a value.", vbInformation
   Exit Sub
Else
   lNumber = Me.NumberControl
End If

If the value is not required, I could use:

If IsNull(Me.TextControl) Or Me.TextControl = "" Then
   sString = ""
Else
   sString = Me.TextControl
End If

I use this method a lot when I use SQL Server as the back end because SQL Server throws some ominous errors, and can also give itself away in the error text.

Once you have your data validated, then you can build the SQL string, then run the SQL string. The nice thing with this structure is, if you get an error, you know just where it is in your code/processes.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

No, it wasn't even rhetorical. It was an honest question.

Admittedly, I *thought* you were making a mistake, but I certainly did not *know* so.

In PHP, you can do something very very similar to what was going on there (that is, simply embed variables in the middle of strings and have the values of those variables picked up within the string), so I thought it really was possible. If I had had to guess, I would have thought that maybe the apostrophes were the only mistake, and that you could indeed refer to form fields with the [....] notation or similar.

I've used Access *A LOT* as a database from ASP pages (and a bit from ASP.NET pages) and knew it wouldn't work there, but really had no idea how VBA in Access handled Access form fields.

In fact, I'm a bit surprised by the final answer, as I would have expected some integrated way of referring to form fields in VBA. Shows exactly what I didn't/don't know.

Okay, so now I *will* say that I think there is still a mistake in this code:
Now that looks exactly like how you'd do it in ASP/ADO/VBScript. And now it looks to me like you have forgotten to account for the possibility of an apostrophe within some data (some form field).

I think that you probably need to do
No?

MMcDonal says he does the validation before building the query, so likely he takes care of the apostrophe problem as part of validation. That's what I do with ASP/ADO/VBScript.

You know it's funny, Old P, but the apostrophe issue never comes up on internal government databases. But thanks for that insightful post. That is an easy validation tip. I will have to incorporate that.

mmcdonal

Look it up at: http://wrox.books24x7.com