append with VB SQL

spinkung

Registered User.
Local time
Today, 15:02
Joined
Dec 4, 2006
Messages
267
Hi All,

- I'm trying to write an append query in VBA.
- I keep getting the message: you are about to append 0 rows

I've been through the help file to run it as a query and still get the same message. can anyone see why its not adding anything??

here's what i've got...
Code:
sql = "INSERT INTO TRANSACTIONS (jobNo, charge, quantity, logBy, reqDt) " & _
      "SELECT TRANSACTIONS.jobNo, TRANSACTIONS.charge, " & _
      "TRANSACTIONS.quantity, TRANSACTIONS.logBy, TRANSACTIONS.reqDt " & _
      "FROM TRANSACTIONS " & _
      "WHERE TRANSACTIONS.jobNo = '" & Me.txtJobNo.Value & "' " & _
      "AND TRANSACTIONS.charge = '" & Me.comboCharge.Column(1) & "' " & _
      "AND TRANSACTIONS.quantity = '" & Me.comboQty.Column(0) & "' " & _
      "AND TRANSACTIONS.logBy = '" & str & "' " & _
      "AND TRANSACTIONS.reqDt = '" & dt & "'"

Debug.Print sql

DoCmd.RunSQL (sql)


Thanks,
Spinkung.
 
If you run the query as a plain Select query is it returning any records? If not then you need to look at your WHERE clause. With so many ANDs in it it is quite likely it is filtering everything out
 
there aren't yet any records in the tables. but when i add a record manually and run a search query on all fields then i do get the result???
 
Am I missing something, if you don't have any records yet, how can you append them?:confused:
 
sorry, maybe i've got it wrong then.

im trying to insert new records..????
 
OK......

Im on the right track now.

Problem im getting now is that its saying the number of query values and destination fields are not the same.

They are the same except for the first column in the table which is and id field which is set to autonumber......


Code:
sql = "INSERT INTO TRANSACTIONS (jobNo, charge, quantity, logBy, reqDt) " & _
      "VALUES (TRANSACTIONS.jobNo = '" & Me.txtJobNo.Value & "' " & _
      "AND TRANSACTIONS.charge = '" & Me.comboCharge.Column(1) & "' " & _
      "AND TRANSACTIONS.quantity = '" & Me.comboQty.Column(0) & "' " & _
      "AND TRANSACTIONS.logBy = '" & george & "' " & _
      "AND TRANSACTIONS.reqDt = '" & dt & "')"
 
You don't need the Autonumber field. It is filled in automatically when you add a new record.
 

Users who are viewing this thread

Back
Top Bottom