No value given for one or more parameters

[muyBN]

Upon trying to do an "Insert into" statement into Access 2003, I receive the
error:

Run-time error '-2147217904(80040e10)':
No value given for one or more parameters

This message is about as helpful as "you have a hole in your head" (which is
probably true, but regardless) since all the variable values are valid. Where
should I look to diagnose this? Any help will be appreciated.

These are the possible affected code pieces and the resulting SQL:

Dim ... strListing_Date As String, ... strDescription_Ad As String, ...
strPrice As String, ... blnSent As Boolean, strOur_Source As String 'these
are the only non-text/non-string items to be written to the database


strSQL = "INSERT INTO " & strTable & _
"(Lead_ID, Listing_Date, Data_Source, Contact_Email,
Contact_Phone1, Contact_Name1, " & _
"Square_Feet, Bedrooms, Bathrooms, Lot_Size, Year_Built,
Garage, " & _
"Price, Property_Street, Property_City, Property_ST,
Property_ZIP, " & _
"Possession,Create_Date,Sent,Our_Source)" & _
" VALUES ('" & strLead_ID & "','" & strYear_Built & "',
'" & strData_Source & " ', " & _
"'" & strContact_Email & "', '" & strContact_Phone1 &
"', '" & strContact_Name1 & "', " & _
"'" & strSquare_Feet & "', '" & strBedrooms & "', '" &
strBathrooms & "', '" & strLot_Size & "', '" & strYear_Built & "', '" &
strGarage & "', " & _
" strPrice ,'" & strProperty_Street & "', '" &
strProperty_City & "', '" & strProperty_ST & "', '" & strProperty_ZIP & "', "
& _
"'" & strPossession & "', date(), 0, 'BOD')"


Call mbInsertIntoDB(strSQL, strDBSource, strLead_ID, strTable, strDB,
blnUseRecord)

Sub mbInsertIntoDB(strSQL As String, strDBSource As String, strLead_ID As
String, strTable As String, strDB As String, blnUseRecord)
Dim rsRecSet As New ADODB.Recordset, objConn As New ADODB.Connection,
strInsert As String
strInsert = UseRecordSet(strSQL, "Lead_ID", strDB)
End Sub


Function UseRecordSet(strSQL As String, strField As String, strDB As String)
As String
Dim objConn As New ADODB.Connection, rsRecSet As New ADODB.Recordset,
strVariable As String

Set objConn = CreateObject("adodb.connection")
objConn.ConnectionString = "data source=" & strDB & ";
Provider=Microsoft.Jet.OLEDB.4.0"
objConn.Open
Set rsRecSet = CreateObject("adodb.recordset")
rsRecSet.Open strSQL, objConn 'this is where the error pops up
On Error Resume Next
strVariable = rsRecSet(strField)
On Error GoTo 0
objConn.Close
UseRecordSet = strVariable
End Function


INSERT INTO Listings(Lead_ID, Listing_Date, Data_Source, Contact_Email,
Contact_Phone1, Contact_Name1, Square_Feet, Bedrooms, Bathrooms, Lot_Size,
Year_Built, Garage, Price, Property_Street, Property_City, Property_ST,
Property_ZIP, Possession,Create_Date,Sent,Our_Source) VALUES
('Lead_ID','Year_Built', 'Data_Source ', 'Contact_Email', 'Contact_Phone1',
'Contact_Name1', 'Square_Feet', 'Bedrooms', 'Bathrooms', 'Lot_Size',
'Year_Built', 'Garage', strPrice ,'Property_Street', 'Property_City',
'Property_ST', 'Prope-ty_ZIP', 'Possession', date(), 0, 'BOD')

(I have no idea where the above 'Prope-ty_ZIP' comes from.)

 

[muyBN]

It appears that I have resolved the problem. I looked over the single-quote,
double-quote and ampersand notation with an even more fine-toothed comb than
I did before; and one of the variables or DB fields was out of place.