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