INSERT input box (1 Viewer)

[Angelflower]

Hi... I has been unemployed for two years but now I am not back to work so my VBA skills are rusty... How do I get this to work:

Dim strInput As String

strInput = InputBox("[Enter Check Number, eg 143469:]", "Enter Check Number", 0)


'Append records to 790_4tb_utility_data_DD1131_transactions
DoCmd.RunSQL "INSERT INTO 790_4tb_utility_data_DD1131_transactions ( transdate, customer_name_common, check_number ) " & _
"SELECT Now() AS transaction_date, [200_5sq_DD1131_cash_collection_voucher].customer_name_common , [200_5sq_DD1131_cash_collection_voucher].check_number " & _
"FROM 200_5sq_DD1131_cash_collection_voucher LEFT JOIN 790_4tb_utility_data_DD1131_transactions ON ([200_5sq_DD1131_cash_collection_voucher].customer_name_common = [790_4tb_utility_data_DD1131_transactions].customer_name_common) " & _
"WHERE [200_5sq_DD1131_cash_collection_voucher].check_number Like '*' & strInput ;"


Thanks so much for your help!

 

[jdraw]

What error are you getting?

here's a modified version, but not sure what you need.

Sub testoct19a()
Dim strInput As String
Dim db As DAO.Database
Set db = CurrentDb
   On Error GoTo testoct19a_Error

strInput = InputBox("[Enter Check Number, eg 143469:]", "Enter Check Number", 0)


'Append records to 790_4tb_utility_data_DD1131_transactions

Dim sql As String '<<< set the sql up as separate string
sql = "INSERT INTO 790_4tb_utility_data_DD1131_transactions ( transdate, customer_name_common, check_number ) " & _
"SELECT Now() AS transaction_date, [200_5sq_DD1131_cash_collection_voucher].customer_name_common , [200_5sq_DD1131_cash_collection_voucher].check_number " & _
"FROM 200_5sq_DD1131_cash_collection_voucher LEFT JOIN 790_4tb_utility_data_DD1131_transactions ON ([200_5sq_DD1131_cash_collection_voucher].customer_name_common = [790_4tb_utility_data_DD1131_transactions].customer_name_common) " & _
"WHERE [200_5sq_DD1131_cash_collection_voucher].check_number Like '*" & strInput & "';"

Debug.Print sql '              <<<< this will let you check your sql string
db.Execute sql, dbFailOnError  '<<< this will force an error if there's a problem
   On Error GoTo 0
   Exit Sub

testoct19a_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testoct19a of Module AWF_Related"
End Sub

 

[Angelflower]

Thank you that worked!