Where are Missing Variables?

[Guest]

I need to INSERT INTO a table with 19 columns. To simplify the code, I do
not list the columns after the table name, which means I have to define all
19 variables. (My form has only 16 of them, so I define the others as Null.)
Here is how I thought it would work. But Access crashes on Execute, saying
it expects 19 variables. I don't see why it fails.

Dim v1, v2, v3, v4..., v19 As String
v1 = Me.NewANDI
v2 = Me.NewName
v3 = Me.NewAlphaL
v4 = Me.NewAlphaF
....
v19 = Me.NewManAddr
strSQL = "INSERT INTO DonorTbl VALUES (v1, v2, v3, v4..., v19)"
CurrentDb.Execute strSQL, dbFailOnError

Thanks in advance.

 

[chris.nebinger]

The reason it is expecting parameters is that the access engine does
not understand what V1 is. It is a variable in the code, but when it
is run against the database, the query engine does not understand that.


You could, instead, do:

strSQL = "Insert Into DonorTBL VALUES (FOrms!frmName!newANDI,
Forms!frmName!newName, ....)"

OR:
Public Function Quote (S)
Quote= chr(34) & s & chr(34)
End Function


strsql = "INSERT INTO DonorTBL VALUES(" & quote(v1) & "," & quote(v2) &
"," ...)


OR:

Dim dbs as dao.database
dim rst as dao.recordset
set dbs=currentdb
' Adding the WHERE clause means no records will be returned. it is
less memory intensive with larger recordsets
set rst = dbs.openrecordset ("Select * from donortable where 0=1")
rst.addnew
rst!Field1 = Me.NewANDI
rst!field2 = Me.NewName
etc...

rst.update



Chris Nebinger

 

[Guest]

Thanks, Chris. I now remember enough from past experience that your
approaches will solve the problem. I appreciate the help.

 

[Douglas J Steele]

Chris neglected to point out that you only need to use the Quote function
around values for Text fields. If the field is numeric, you don't need the
Quote function. If the field is date/time, you need to delimit with #, not a
quote (and the date needs to be in mm/dd/yyyy format)

I must admit I don't understand your comment that "to simplify the code, I
do not list the columns after the table name, which means I have to define
all 19 variables". That hardly seems like a simplification to me!

 

[Guest]

In addition to the other comments you have received, You also need to be
aware of the way Dim works.
The way you have done it:
Dim v1, v2, v3, v4..., v19 As String
Means you have 18 Variant variables and 1 string variable. Each varialbe
has to be typed individually.
Dim v1 as String, v2 as Sting, v3 as string
An untyped varialbe defaults to Variant.

Also, you said your form has only 16 controls and there are 19 fields in the
table. If you want to allow those 3 fields to remain Null, you can either
type the variables as Variant or in the VALUES clause of the SQL,

VALUES v1, v2, v3, Null, v4, v5, Null, v6, v7, Null, v8,.....

 

[Douglas J Steele]

I was going to point that out, except the line "My form has only 16 of them,
so I define the others as Null." made me think that perhaps it was a good
thing that they were variants!

 

[Guest]

Yeah, I thought the same thing....
Then I thought about what his next post would be