Insert qry-Macro group/Please point me in the right direction

[montrose]

My brain is a little overloaded right now. If someone can just get me going in the right direction....

I have a continuous form which shows equipment available for rent (unique id, etc). My plan is to put a command button at the end of the row to allow a user to place this piece of equipment "On Hold".

I think the command button would run a macro (macro group?) which:
1. 'Grabs' the current record
2. Uses an insert query to insert that record's unique id into tblHold
3. Minimizes the frmEquipAvail
4. Opens the frmHold for the user to identify for which customer the equipment is placed on hold and enter a release date for the hold
5. Requeries the frmEquipAvail
6. Closes the frmHold and maximizes frmEquipAvail.

My questions:
1. How can I 'grab' the current record? Bookmark it? How do I say that in the macro?
2. Do I need to create a separate "insert query" that takes the 'grabbed' record and inserts it into tblHold and reun that through this macro group?
2. When creating a macro group, how do you stop each macro from 'firing' all at the same time? May sound dumb, I know, but whenever I've tried to do one they all just ran at the same time.

I have no doubt this could be accomplished with vba, but I am vba challenged and suffer through using macros. Any and all suggestions, as always are welcome. Thanks. Learn what you can and share what you know.

 

[braindead2]

First let me congratulate you on what seems to be proper design. The average user would simply add an additional yes/no field to the table and named it stored? Using a hold table is certainly the way to go.

I think that perhaps what you are asking is easier then you may think
step1 no need to grab the current record as the button knows the current record
step2 no need to run an insert query to append the id to the table as your form that you will open will be based on that holdtbl table
step3 you can minimize with a macro or VBa
step4 the secret to this is the form you are now opening
A it,s record source should be set to holdtbl
B set the field that links to the unique field up so its default value = forms!the1stformname!the uniquefieldname
C open the 2nd form up in data entry mode
Basically what needs to happen here is on the button have it to open the form. By opening the form in dataentry mode since you open it to a new record it will fill in the value of the linking field and then the user can fill in the rest of the information name date etc. then when the are done on the closing form just maximixe and requery from that event.
Hopefully this did not just confuse you more
good luck

 

[montrose]

braindead2, I told you my brain was overloaded! What you're saying makes absolute sense and MIGHT have occurred to me if I hadn't been staring at the screen for what seems an eternity. The 'holding' tbl just seemed logical since after a few days I can dump the data and nobody cares, saves space. I know it will work, and thank you for helping me see things clearly-you'd laugh if you could see the complicated "solution" I was trying! If you weren't such a constant 'visitor' I'd be able to give you a star*. Montrose Learn what you can and share what you know.