Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ? (1 Viewer)

[alan2013]

I'm having difficulty in creating the VBA code required to - on the click of a button on a Form - insert some data recorded on that Form into a Table (ie. one that isn't the source for the Form).

The data recorded on the Form that needs to be inserted into the (other) Table :

dtTestDate
numScore
numCorrection

numScore and numCorrection are both Number data type.

Most recently (been trying to resolve this over the last couple of days), the code I'm using (behind the On Click event of the button) has been :

DoCmd.RunSQL "INSERT INTO tblTestScores (" & _
                  "dtTestDate, " & _
                  "numScore, " & _
                  "numCorrection)" & _
                  " VALUES (" & _
                  "Date()," & _
                  "' & 20 & ' ," & _
                  "' & -1 & ');"

It seems this variation of the code is just not working....though I'm not getting any error message. Based on some previous experience in using SQL to insert records into a Table, I suspect that the problem lies in the SQL code relating to numScore and numCorrection (and that the SQL relating to dtTestDate is not the problem).

Prompted by some Googling re the issue, I've tried some other variations of the code (removal of the single quotes round the numeric values; use of brackets round the numeric values; etc). Those variations give rise to an error message indicating that the number of values to be inserted (three in this case) doesn't tally with the number of fields in the INSERT INTo part of the code (possibly because the VBA code re the two numeric values is not as it should be ?).

There's probably a really simple solution to this, but I can't see it right now, and I need to move on with this.
Can anyone help with this, please ?
Thanks in advance

 

[ebs17]

though I'm not getting any error message

You probably turned off the notifications. If you ignore mistakes, you get into problems.

The goal is a functioning SQL statement. That's why it's a good idea to look at such a statement before putting it together using VBA. This results in instructions for action if problems arise.

INSERT INTO TableX  (LongField, TextField, DoubleField, DateField)
VALUES (23, "SomeText", 55.50, #3/16/2024#)

The next easy step is to use a variable to compose the SQL statement via VBA. You can view and control their content separately, and you will then also use them when executing the query.

Dim sSQL As String
sSQL = "INSERT INTO ..."
Debug.Print sSQL
Currentdb.Execute sSQL, dbFaiOnError

You now want to use form controls or variables to pass values. Both types of objects are unknown to the database engine (ACE), so their values come from outside.
Such external values must be formatted according to the data type so that they can be interpreted correctly after being incorporated into the statement. As you know, the SQL string only contains text, and all texts must be interpreted correctly.
However, your variables do not pass values, they are simply field names, which raises doubts about your data modeling. You can just add names like that.

sSQL = "INSERT INTO tblTestScores (" & _
                  "dtTestDate, " & _
                  "numScore, " & _
                  "numCorrection)" & _
                  " VALUES (Date(), 20, -1)"

 

[Uncle Gizmo]

I call it the notorious "insert into SQL statement" because people often have problems with it because of the difficulty in formatting the string properly.

It can be formatted easily and with no errors if you follow this trick.

See the blog on my website here:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

 

[ebs17]

At this point I would like to repeat the reference to a really good debugging tool.

Add-In for debug SQL in VBA

Compiling SQL statements or filters in VBA and then using them is quite error-prone. The add-in linked below sets display of the instruction as well as test and error display to a higher level. SqlDebugPrint Eberhard

www.access-programmers.co.uk

 

[alan2013]

Thanks, ebs17 & UncleGizmo. I'm continuing to sort it. The error I'm getting right now (seen it before with some other variations I tried) :

Run-time error 3346 : Number of query values and destination fields are not the same.

 

[Gasman]

Put the string into a string variable and then debug.print it.
That will show you your errors.
Then you can use that in the execution.

 

[ebs17]

I'm continuing to sort it.

Maybe at some point you will find the time to look at, analyze and test my suggestion with sSQL.

 

[Josef P.]

Using the function StringFormatSQL from the Northwind2 sample application, it could look like this:

const InsertSqlTemplate as string = "INSERT INTO tblTestScores (dtTestDate, numScore, numCorrection) VALUES (Date(), {0}, {1})"
dim InsertSql as String

InsertSql = StringFormatSQL(InsertSqlTemplate, 20, -1)  'assume that 20 and -1 will be variables later.
...

The procedure uses the data type of the transferred parameters for SQL text formatting. This may or may not be a good thing.
The key tip that can be drawn from this example: design a procedure that ensures that you don't have to deal with trivialities such as converting values to SQL text when writing code on a daily routine.


[OT: NW2 Code]
What does it actually look like legally to show parts of the code from NW2 publicly?
Somehow it would be practical if the authors could make the code publicly visible (if MSFT allows this). Then one could refer to it.

 

[MarkK]

The difficult and error-prone problem of cobbling together SQL statements in code is why DAO allows you to create and run a temporary QueryDef. Here's a code example...

Private Const SQL_INSERT_SCORE As String = _
    "INSERT INTO tblTestScores " & _
        "( dtTestDate, numScore, numCorrection ) " & _
    "VALUES " & _
        "( Date(), p0, p1 )"

Private Sub InsertScore()
    With CurrentDb.CreateQueryDef("", SQL_INSERT_SCORE)
        .Parameters("p0") = Me.tbScore
        .Parameters("p1") = Me.tbCorrection
        .Execute dbFailOnError
    End With
End Sub

The QueryDef knows the data types, so you don't have to format or delimiting anything.

 

[Pat Hartman]

Run-time error 3346 : Number of query values and destination fields are not the same.

seems pretty obvious

Use the suggestions to add your code to a string variable. Then post your actual code with the error.

 

[ebs17]

Run-time error 3346 : Number of query values and destination fields are not the same.

If you use a variable for decimal numbers (single, double, currency, decimal) as part of the query and the comma is the decimal separator in your regional settings, then when you simply insert it, 60.55 becomes => 60,55
The comma is then interpreted as a field separator in the fields collection, resulting in one more field.
Then use Str(DoubleVariable).
,