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

Registered User.
Joined
Mar 24, 2013
Messages
69
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 :

Code:
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
 
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.
SQL:
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.
Code:
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.

Code:
sSQL = "INSERT INTO tblTestScores (" & _
                  "dtTestDate, " & _
                  "numScore, " & _
                  "numCorrection)" & _
                  " VALUES (Date(), 20, -1)"
 
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:-

 
At this point I would like to repeat the reference to a really good debugging tool.
 
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.
 
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.
 
Using the function StringFormatSQL from the Northwind2 sample application, it could look like this:
Code:
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.
 
Last edited:
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...
Code:
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.
 
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.
 
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).
,
 

Users who are viewing this thread

Top Bottom