update access table from another table?

Question

I have a form that creates records in table 1. I want that after a record is inserted, a field in table 1 is updated from a field in table 2.
The problem is when I execute a query to retrieve the record from Table 2, I got the message :"No Value given for one or more required parameters"

This is the code where the error displays in the After_Insert Event. (Note, there is a coma after the CurrentProject.Connection parameter, not .. as displayed)



Dim rsDest As New ADODB.Recordset

Dim SQLStmt As String

SQLStmt = "Select * from table2 WHERE table2_Id = " & Me!ID

rsDest.open SQLStmt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Answer 1

You could do this with another query. Make a separate query from the first one, then make a Macro that runs the first one and then the second one. Your form submit (button?) would then call the Macro instead of a single query. That's the Access way to do it. Your other choice is to do it all in VBA and craft the queries yourself.

Answer 2

I'm not familiar with visual basic but the problem is exactly what the error says: "No Value given for one or more required parameters"



Your SQLStatement is "Select * from table2 WHERE table2_Id = " & Me!ID

where &Me!ID is a parameter.



You call the method to execute the query but you never send a value for &Me!ID.



I'm not sure how to solve it since, like I told you, I'm not familiar with visual basic but as a programmer any hint that leads to the right direction is very valuable so you should investigate on how to send parameters in a query.



Good luck

Answer 3

there are distinctive approaches you're able to try this - you ought to use an replace question to replace the 2d table the place the checklist is replaced - and an insert question while a checklist is extra. yet in any different case (assuming you're using a sort to insert records) is to apply only a sprint code to accomplish a similar initiatives. I reckon queries could be extra helpful in the adventure that your new to get admission to, focus interior the append (insert) question first as those are much less complicated - the replace question is fairly extra good. i will deliver you an sq. code occasion which you will view in get admission to if which will help? HTH.