INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

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

CODE --> sql

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

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

Quote:

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

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

(OP)
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

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

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


CODE -->

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

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

(OP)
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

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

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

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

(OP)
Sorry,

INSERT INTO Tbl_WO_Contents (WO_ID, PIN) SELECT 1

Regards,

Rob

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

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

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

(OP)
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

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

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

CODE --> vba

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] = " & Forms![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:

CODE --> vba

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] = " & Me.[Combo52]
    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

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

(OP)
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


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

How many "r" in currentdb?

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

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

(OP)
Doh

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

(OP)
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

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

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

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

(OP)
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

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

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

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

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

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

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

(OP)
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

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

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.

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

(OP)
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

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

Trying MajP's

CODE --> sql

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close