Copy specific field for each record in filter results in a form to another table

[bobjackson]

Hi,

I am trying to filter one table in a form by building, then selecting WO_ID.value from a combobox48 then clicking a button to copy the PIN field for each record into another table along with the WO_ID value I've started writing the code but it's been a few years since I've done anything like this and with very little hair left already thought maybe someone who is doing this regularly may be able to help. I've got as far as this.

Private Sub Command47_Click()
Dim rstSource As DAO.Recordset
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set rst = CurrentDb.OpenRecordset("Select * From Tbl_Work_Order_Contents;")

Set rst = Me.RecordsetClone

With rst
While Not .EOF
With rstInsert
.AddNew
For Each Me.PIN In rstSource.Fields
With fld
' Copy field content.
rstInsert.Fields !PIN.Value = PIN.Value
rstInsert.Fields !WO_ID.Value = Combo48.Value


End With
Next
.Update
End With
.MoveNext
Next
rstInsert.Close
.Close
End With

Set rstInsert = Nothing
Set rstSource = Nothing
End Sub

Any help would be gratefully appreciated.

Regards,

Rob

 

[bobjackson]

Duane,

I've stripped all tables, forms, queries and reports out that are not relevant to this part of the db and reduced number of records and attached if this helps

Regards,

Rob

 

[dhookom]

Thanks for the condensed version of the database.

I get 22 records returned in the query that MajP suggested. What do you see?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[bobjackson]

Duane,

Yes, confirmed I get 22 also, but when I click the button in the form selected DD3 on second combo (6) it only puts the first record with PIN 2361 into the Tbl_WO_Contents and not all 22 records.

Cheers,

Rob

 

[MajP]

You made WO_ID a primary key. Thus it has to be unique. It is a foreign key. Ensure it is not a primary key and not unique.

 

[bobjackson]

Thank you Duane amd MajP for all your help, kicking myself that I had missed the primary key bit, my excuse is I've not touched access for a few years now and am very rusty. I've now only got one bit of code to sort out which I know is going to be daunting as I normally have an idea on how to achieve what I want but this could prove interesting. Anyway when I get stuck I'm sure that will be the start of a new thread. Again thanks for all your help

Regards,

Rob

 

[dhookom]

Trying MajP's

SELECT 1, PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building ID] = 6

should have displayed more than one record. It should have displayed all of the records with a building ID of 6 regardless of the primary/unique index on the target table. There was no target table in his query.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[bobjackson]

Duane,

Thanks, yes when I ran it the second time in query it did give me all the records, don't know what went wrong the first time I ran it. Again thanks for all your help.

Regards,

Rob