Access 2003, DblClick copy record different table - MUST NOT BE LINKED (1 Viewer)

[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

 

[Pat Hartman]

Welcome aboard
I am confused by your problem description

TableB has AutoNumber, and many fields and linked tables (from which all management is performed)

Tables don't store tables so are you talking about moving a record from a table in one database to a dable in a different database?

To move a record from one table to another is a two step process.
1. Run an append query to copy the record from the first table and append it to the second.
2. Run a delete query to delete the record from the first table.

We've had a rash of requests to do this in the past week. In general it is an indication of a poor design. A more normalized design would keep the records in the same table but change their "status" or whatever you want to call it. Then your queries would use the "status" field to select appropriate records.

 

[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

 

[Pat Hartman]

You need to turn off the warnings just before you run the action query. So find that procedure and add the code in there.

WARNING!!!!!
Although it is necessary to turn off the warnings to stop the user from being annoyed by these system prompts, it is absolutely critical that you turn them back on again immediately after. Turning these warnings off has the undesirable side effect of turning off the warnings Access gives you when you close objects that you have modified and havent saved. So, you've spent two hours debugging a piece of code and you just close the code window. If warnings are off,Access will silently DISCARD all your hard work. If warnings are on, Access will at least ask you before discarding it.

As a visual reminder to myself, I always turn the hourglass on when I turn warnings off and then off when I turn warnings back on. That way, if I'm debugging and my code sets warnings off but never gets to the point where they are turned back on, I am aware of it and can turn them back on by running a macro. The only two macros I ever use are mSetWarningsOff and MSetWarningsOn and I do them as macros so I can easily run them outside of the code module.

 

[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!