Variable derived from Recordset not passing into SQL Statement 2

[leslie746]

Hi,
Can anybody help urgently, please?

In the script below the value of variable "strA" shows correctly in the Message Box, however, when I try to execute the SQL statement:

strSQL = "INSERT INTO [Material sent](Material, govt_id, [Date_Sent], sent) VALUES('Letter and Leaflet1', strA, Date(), Yes); "

- it asks for parameter strA as if its value is now not known, and when I run debug, at this point it shows no value for this variable.

Here is my script:

-----------------------------------------------------------
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strA As Long
Dim lngCount As Long

strSQL = "SELECT m_govt.govt_id, m_govt.govt_title, m_govt.govt_address1, m_govt.govt_address2, m_govt.govt_address3, m_govt.govt_address4, m_govt.govt_postcode, m_master.name FROM m_master INNER JOIN m_govt ON m_master.my_id=m_govt.my_id WHERE m_master.name='West Sussex';"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, , dbOpenDynaset)

rst.MoveLast
rst.MoveFirst
lngCount = rst.RecordCount
MsgBox lngCount

If rst.RecordCount = 0 Then
MsgBox "EMPTY RECORDSET", vbOKOnly

Exit Sub
Else

rst.MoveFirst

'Do Until rst.EOF

With rst

.MoveFirst

Do While Not .EOF

strA = rst.Fields(0)
MsgBox strA

strSQL = "INSERT INTO [Material sent](Material, govt_id, [Date_Sent], sent) VALUES('Letter and Leaflet1', strA, Date(), Yes); "
DoCmd.RunSQL strSQL, Yes

.MoveNext
Loop
End With
--------------------------------------------------------

What am I doing wrong?
Early assistance would be most appreciated.
Thanks.

 

[KenReay]

hi

strSQL = "INSERT INTO [Material sent](Material, govt_id, [Date_Sent], sent) VALUES('Letter and Leaflet1'," & strA & "," & "#" & Format(Date(),"yyyy/mm/dd") & "#, Yes); "
DoCmd.RunSQL strSQL, Yes

you need to concat the content of strA into your string, not the variable name, similarly with Date() and dates must be bounded by #

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leslie746]

Dear Ken,
Thank you very much for your unbelievably fast response.
It works, and if I can trouble you a bit more.

In the same script, there is a "WHERE" condition which I have hardcoded "West Sussex" because in the original query it was supposed to be a parameter "Select County" but for some reason when I put it into the very first SQL statement like this:

strSQL = "SELECT m_govt.govt_id, m_govt.govt_title, m_govt.govt_address1, m_govt.govt_address2, m_govt.govt_address3, m_govt.govt_address4, m_govt.govt_postcode, m_master.name FROM m_master INNER JOIN m_govt ON m_master.my_id=m_govt.my_id WHERE m_master.name=[Select County];"

I am getting the error message: "Too few parameters. Expected 1."

Any suggestions, please?

Best Regards.
Leslie.

 

[PHV]

You must have a "Select County" TextBox in your form and then replace this:
WHERE m_master.name=[Select County];"
By this:
WHERE m_master.name=[tt]'"[/tt] & Me![Select County] & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KenReay]

Hi

Sorry been away from my PC for a while, PHV's suggestion is the way to go, it is as I would have said myself

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leslie746]

Thank you very much to both of you, Ken and PHV.
You have been very helpful and everything seem to be working.
Best Regards.