Access 2003, DblClick copy record different table - MUST NOT BE LINKED

[exploreforinstance]

Access 2003, DblClick copy record different table - MUST NOT BE LINKED
Note - novice user.
TableA is large list of medical equipment names - displayed in Form, TableA, and List Box, AssetDescriptionA
User double-clicks, and populates TableB, Subform, Text Box, AssetDescriptionB
TableB has AutoNumber, and many fields and linked tables (from which all management is performed)
TableA, and Table B, must not be related, linked in any way - TableA's only purpose is to populate TableB.
I have searched and tried many methods and none have worked. I would really appreciate if someone can point me in the right direction.
I assume Event Procedure code is the way to go?
Thanks, Alan

 

[exploreforinstance]

Thanks for the quick response. I should have placed a comma after, "and many fields" - I meant that TableB (needing to copy records to) is linked to many other tables in the same db, and I don't want to restructure to accommodate.
I do not want to "move" a record from TableA to TableB, I want to copy it.
Both TableA and TableB are in the same db, and not linked together.
If Append is the right way to go, I know how to design a query, but not how to initiate it by double-clicking - use macro, or VB code?
Thanks again, I really appreciate any help.

 

[exploreforinstance]

Goal is to select equipment names, one at a time or in multiples, from thousands in TableA, and copy to - populate - TableB.

 

[exploreforinstance]

Append query works - used Criteria ([Forms]![YourFormName]![TextBox] ) to select record I double click on. Now if someone knows how to eliminate the "You are about to run" and, "You are about to append" statements, as well as automatically refresh the page, I'm all set.

 

[The John Rambo]

In the VBA editor insert the following code before you run your query (Turns OFF all system messages):

DoCmd.SetWarnings False

AND insert the following code after you run your query (Turns ON all system messages and refreshes the form):

DoCmd.SetWarnings True
Me.Requery

Please note if you do not turn on again NO system messages will show again until you set SetWarnings to True or until you re-open the database.

 

[exploreforinstance]

"before you run your query" - I placed Event Procedure in "Before Update" in the List Box, and it does turn off these messages. Is this correct?
"Turns ON all system messages" - where do I place this?
If I click code edit, it takes me to the last two entries:

Private Sub List410_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![List410], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub List410_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
End Sub

 

[The John Rambo]

"Turns ON all system messages" - where do I place this?

I would suggest removing the BeforeUpdate Procedure completely and placing the TURN OFF code just before the event and the TURN ON code just after the event that is causing the undesireable system messages. As Pat has described, leaving system messages off is very dangerous because it doesn't only affect the current procedure that you're working in but Access as a whole - this way will ensure that you're safe...

 

[exploreforinstance]

Thanks both for your responses. What is unclear to me - I am using a macro to initiate the append query, to copy a selected equipment name from TableA (List Box) to TableB (Subform Texbox). I understand you think it a bad idea to use Before Update to turn off all system messages - but where exactly do I place the Turn Off, and Turn On code? Is this [Event Procedure] code? but if it's not Before Update, or After Update, then where? How?

 

[The John Rambo]

Apologies, the code I entered was incorrect for ur situation (removed).

If you are running the append query from a macro you can change the Setwarnings options in your macro.
Insert an Action before your append query and select "Setwarnings", change the option to "Warnings Off" then insert another action after your append query, select "Setwarnings" again and change the option to "Warnings On".

 

[exploreforinstance]

Makes perfect sense - works! Thank you!