Based on drop down value, form pops up displaying record clicked on

[arothacker]

I have a continuous form called Headcount with multiple fields and a drop down field called update. When the user selects the drop down for the record as "open"...is there a way for another form to pop up and display that particular record?



The headcount form has locked fields but when the user chooses "open" we want to unlock the fields ID, Last Name, and First Name so that the user can edit only those particular fields and only when Open is selected.

[pbaldy]

How about:

http://www.baldyweb.com/wherecondition.htm

[John_G]

Hi -

Fields are "Locked" only on the form they are in, so that is not a problem.

To answer your question, yes you can easily open another form when "Open" is selected. In the After Update event of the Dropdown control:

Check if "Open" was selected in the dropdown - if not, exit the procedure.

Open the editing form with:

DoCmd.OpenForm formname, , , criteria, ,acdialog

criteria is like a where clause (without the WHERE) that identifies the record you want to edit, and typically looks something like "CustomerID = me!Customer_ID" . me!Customer_ID is the value on the current record of your continuous form.

acDialog is not required, but it's effect is to keep the user in the editing form until it is closed. No other actions outside the editing form can be done while it is open. It is a very useful feature.


HTH

John

[arothacker]

That works perfectly! To add on, are we able to use the pop-up form to edit the record then? It allows me to edit it within the pop-up form but then doesn't save back to the original record.

[pbaldy]

It should save if it's bound to the same data. You may need to requery the original form to see the updated data.

[John_G]

Hi -

It probably does save back to the original record, but you just don't see it on the main form. To see the changes, you need to requery the main form after the popup closes.

If you open the popup with the acDialog option, then you can add me.requery following the DoCmd.OpenForm.... line.

That is another useful effect of the acDialog option - it stops the code until the popup form is closed.

John

[arothacker]

This is where it gets tricky because the form is based off filters from a query. So we have form #1 with drop downs such as location. The user selects location miami and then presses a button which opens form #2 that displays all the heads in miami. When the user selects "open" in the drop down field then form #3 opens. when I try to requery form #2 I get an error because of the filters I think


ideally we want to have the form pop up and copy that record's data into the fields to make a new record but I haven't been able to figure that out yet...this was our alternative solution

[pbaldy]

Try

DoCmd.OpenForm...
Forms!FormJustOpened.TextboxName = Me.TextboxName

If the form is opened in dialog mode, you'll need OpenArgs instead:

http://www.baldyweb.com/OpenArgs.htm

[John_G]

What is the error you get when you try to requery Form2? Where do you have the code, and what does it look like?

The filters applied to Form2 should not cause an error when requerying it.

One caveat though is that if you edit a record so that it no longer meets the filter criteria, then the requery will cause that record to seem to "disappear".

John

[arothacker]

Pbaldy - your solution worked perfectly!! I was looking into OpenArgs but couldn't find clear instructions on how to use it. Thank you!!

John - the error I got was asking for one of the field values. I put the requery on form 2 when I think it might've worked if I put it on form 3 after the update.

Thanks for all your help guys!! This has been stumping me for quite some time!

[pbaldy]

Happy to help!