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.
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.