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

 

[dhookom]

I'm lost. Do you want to create a new record for every record in your table? Can you provide the table structure?

If I understand correctly you should be able to run a simple append query.

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

 

[bobjackson]

The main table in the db is Tbl_Apparatus, I want to filter a number of records from this table and create a record for each of them in a seperate table Tbl_WO_Contents along with the WO_ID which is selected from a combobox list on the form that I am using. The form relates to Tbl_Apparatus.

The only field from Tbl_Apparatus I want to copy to Tbl_WO_Content is PIN along with the selected Work Order ID from the combobox

Added table details in attachment.

In summary I have over 17,000 records in Tbl_Apparatus and want to filter on a building (field in Tbl_Apparatus) or cell (field in Tbl_Apparatus) and copy the PIN (field in Tbl_Apparatus) for all the records selected on the filter to another table Tbl_WO_Content and the WO_ID selected from the combobox.

Regards

Rob

 

[dhookom]

I have no idea what the attached file was supposed to show.

Again, I think you can create an append query with SQL like

INSERT INTO Tbl_WO_Contents (WO_ID, PIN)
SELECT Forms![YourFormName]![YourComboBox], PIN 
FROM Tbl_Apparatus
WHERE [whatever you mean by filter to a number of records];

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

 

[MajP]

Again, I think you can create an append query with SQL like

I might be wrong, but I am pretty certain you can not return a field name or table name through code like that in SQL. A function can return a value but not a name of a field or table. You would have to do that in code.

dim strSql as string
strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
strSql = strsql & Forms![YourFormName]![YourComboBox]
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [whatever you mean by filter to a number of records]"
currrentdb.execute strSql

 

[bobjackson]

Thanks for the above, I have tried both of the above, dhookom using your code it comes back with syntax errors for the insert into Tbl_WO_contents line and select forms line.

MajP debug does not find any errors but when I click on the button it reports a syntax error clicking on debug opens the code and highlights the final line. Below is the code I have input.

Private Sub Command47_Click()
'INSERT INTO Tbl_WO_Contents (WO_ID, PIN)
'SELECT Forms![Frm_Apparatus_WO]![Combo48], PIN
'FROM Tbl_Apparatus
'WHERE [Tbl_Apparatus]![Building] = [Frm_Apparatus_WO]![Combo52]
Dim strSql As String
strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
strSql = strSql & "Forms![Frm_Apparatus_WO]![Combo48]"
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus]![Building] = [Frm_Apparatus_WO]![combo52]"
currrentdb.Execute strSql

End Sub

Regards

Rob

 

[MajP]

Always put a debug.print for test purposes so you can verify your string. You can comment it out once done.

strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
strSql = strSql & "Forms![Frm_Apparatus_WO]![Combo48]"
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus]![Building] = [Frm_Apparatus_WO]![combo52]"
debug.print strSql
currrentdb.Execute strSq

You want your final string to look something like
"INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT SomeFieldName, ..."
not
""INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT Forms![Frm_Apparatus_WO]![Combo48], ..."

So you would need to remove the quotes
strSql & "Forms![Frm_Apparatus_WO]![Combo48]"
to be
strSql & Forms![Frm_Apparatus_WO]![Combo48]

On this line you actually do not have to replace the form reference with a literal, but I would for ease of debuggin
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building] = " & [Frm_Apparatus_WO]![combo52]

if it does not work now please post the sql string from the debug statement

 

[bobjackson]

Thanks, done those mods and comes up with can't find field '[1' referred to in your expression.

Code now looks like

Private Sub Command47_Click()
Dim strSql As String
strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
strSql = strSql & Forms![Frm_Apparatus_WO]![Combo48]
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building] = " & [Frm_Apparatus_WO]![Combo52]
currrentdb.Execute strSql

End Sub

Cheers

Rob

 

[MajP]

So where is the output from your debug.print so we can see the resolved SQL string?

 

[bobjackson]

Sorry,

INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT 1

Regards,

Rob

 

[dhookom]

Where did you place the debug.print? Is it immediately before the currentdb.Execute line?

What is the Row Source of Combo48? What are some typical values? Are there spaces in the values?

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

 

[bobjackson]

the debug highlights line strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building] = " & [Frm_Apparatus_WO]![Combo52]

The row source of combo48 is table tbl_work_orders field wo_id which is an autonumber (key) for this table, the record selected has a value of 1

Regards,

Rob

 

[dhookom]

Debug.Print is not the same as a break in the code. Your code is missing a [Forms]

Private Sub Command47_Click()
    Dim strSql As String
    strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
    strSql = strSql & Forms![Frm_Apparatus_WO]![Combo48]
    strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building] = " & [highlight #FCE94F]Forms![/highlight][Frm_Apparatus_WO]![Combo52]
    debug.Print "strSQL value: " & strSQL
    currrentdb.Execute strSql

End Sub

If the code is running in Frm_apparatus_WO you can use:

Private Sub Command47_Click()
    Dim strSql As String
    strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
    strSql = strSql & [highlight #FCE94F]Me.[Combo48]
[/highlight]    strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building] = " & [highlight #FCE94F]Me.[Combo52][/highlight]
    debug.Print "strSQL value: " & strSQL
    currrentdb.Execute strSql

End Sub

This code assumes Building and PIN are both numeric fields. If they are text, you will need to add delimiters.


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

 

[bobjackson]

Duane,

Thanks, it still comes up with run-time error '424' object required. Building is now Building ID which is numeric and PIN is also numeric, WO ID and PIN in Tbl_WO_Contents are also numeric.


Code now reads:-

Private Sub Command47_Click()
Dim strSql As String
strSql = "INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT "
strSql = strSql & Me.[Combo48]
strSql = strSql & ", PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building ID] = " & Me.[Combo52]
Debug.Print "strSQL value: " & strSql
currrentdb.Execute strSql

End Sub

debug.print reads:-

strSQL value: INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT 1, PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building ID] = 6

6 corresponds correctly to the building selected in combo52

Regards,

Rob

 

[dhookom]

How many "r" in currentdb?

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

 

[bobjackson]

Doh

 

[bobjackson]

Duane,

Thanks for the help, it's now functioning but only writing one record into Tbl_WO_Contents, there are 2434 records in Tbl_Apparatus that meet the criteria for Building ID.

Cheers

Rob

 

[MajP]

Run this in the query design to see if you really get more records
SELECT 1, PIN FROM Tbl_Apparatus WHERE [Tbl_Apparatus].[Building ID] = 6

 

[bobjackson]

Hi,

Tried this and only get the one record but if I open Tbl_apparatus and filter [Building ID] = 6 then I get 2434 records. Originally I thought I would have to do an IF, THEN to go through all records in Tbl Apparatus to copy into the new table.

Any help would be gratefully appreciated.

Regards,

Rob

 

[dhookom]

Is Building ID a lookup field? This isn't making sense.

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