Read two tables and Append to append (1 Viewer)

[MB]

Hello,

I am new to VBA and I need to create a button that will read from two tables and append to an existing record in a table. Here is the scenario.

I have an Access database that has two tables called “Hardwaretable” and “AMSInfo”. There are about 6,000 records in the Hardwaretable and 14,000 records in the AMSInfo table. Both tables have the same field names, Serial_Number, Model_Number, Bar_Code, PO, Cost, Invoice, Inv_Date, Acq_Date and Source. I need the program to look at two fields in each table, the “Serial_Number” and the “Model_Number”. If the records within “Serial_Number” and “Model_Number” match then take the records from fields “Bar_Code”, “PO”, “Cost”, “Invoice”, “Inv_Date”, Acq_Date” and “Source” from the “AMSInfo” table and append them to the same fields in the “Hardwaretable”. I wrote the following code, but every time it starts running it stops halfway through the “AMSInfo” table. I set up a simple loop that goes through and JUST READS from “AMSInfo” table but it always stops in the same place. Am I not doing something wrong? Please help. Here is my code..

Private Sub cmdPush_Click()
'data goes from AMSInfo to Hardwaretable
Dim rstht As DAO.Recordset 'Hardwaretable
Dim rstams As DAO.Recordset 'AMSInfo table
Dim serialnum
Dim modelnum
'open first table and go to first record
Set rstht = CurrentDb.OpenRecordset("Hardwaretable")
rstht.MoveFirst

'open second table and go to first record
Set rstams = CurrentDb.OpenRecordset("AMSInfo")
rstams.MoveFirst

'each time it grabs a serial number and model number from table1, it loops through
' the entirety or table2.
Do Until rstams.EOF
DoEvents
ActiveXCtl2.Value = rstams.PercentPosition
serialnum = rstams.Fields("Serial_Number").Value
modelnum = rstams.Fields("Model_Number").Value
Debug.Print serialnum & " : " & modelnum
'here is where it loops through all of table2 looking for a match to serialnum and modelnum
Do Until rstht.EOF
If ((rstht.Fields("Serial_Number").Value = serialnum) And (rstht.Fields("Model_Number").Value = modelnum)) Then
'put any writing within the "edit/update" block
rstht.Edit
rstht.Fields("Bar_Code").Value = rstams.Fields("Bar_Code").Value
rstht.Fields("Manufacturer").Value = rstams.Fields("Manufacturer").Value
rstht.Fields("Cost").Value = rstams.Fields("Cost").Value
rstht.Fields("PO").Value = rstams.Fields("PO").Value
rstht.Fields("Inv_Date").Value = rstams.Fields("Inv_Date").Value
rstht.Fields("Invoice").Value = rstams.Fields("Invoice").Value
rstht.Fields("Source").Value = rstams.Fields("Source").Value

rstht.Update

End If
'move to the next record in hardwaretable
rstht.MoveNext
Loop
'since it is at the end of table2, AMSInfo, it needs to be reset to first record
rstht.MoveFirst
'move to next record in table1, Hardwaretable
rstams.MoveNext
Loop
'close both tables now
rstht.Close
rstams.Close

Much appreciated

MB

 

[Chris RR]

Why not write an update query that does this and then just run that query from code?

 

[MB]

Hi Chris RR,

Thank you for your reply. I have tried creating an update query and used SQL but it did not work. I am very new to VBA and SQL and I am sure I am doing something wrong. Could you be more specific on how I can do this. Thank you so much.

MB

 

[Pat Hartman]

Let's start with terminology. Rows are "appended", columns are "updated". Append is the process of adding something new, updating is the process of changing something that is already there.

An update query is MUCH simpler and more efficient than the code loop you are trying to write.

Use the query builder to build the update query.
1. Add the table you want to update to the query window, then add the table from which you are obtaining the data.
2. Join the two tables on their primary keys.
3. Select the columns from table1 that you want to update.
4. Change the query type to "update".
5. In the "Update To" cell for each field, type the name of the field that is suppling the data from table2 and qualify it by the table2 name as in - [table2].[fldA] - be sure to use the square brackets so Access knows you are referring to a column name and not just typing a text variable.

 

[MB]

Pat,

Thank you, the update query worked.

MB