Save subform highlighted record field into a global variable 1

[waubain]

I am trying to create a new user interface when the application is opened. A mock up is pictured below.

Subform subfrmMainPage row source is qryStudy from tblStudy. StudyID is the primary key and is the key that ties all other tables together. subfrmMainPage resides in unbound frmMainPage. Buttons cmdStudy, cmdDispense, and cmdInventory are in frmMainPage.
The row is highlighted using a script by Stephen Lebans

http://www.lebans.com/conditionalformatting.htm

Unfortuately Stephen is retired from Access and no longer provides support.This script may already provide this but the code is way over my head.

I want the user to be able to highlight the study of interest and then be able to click one of the Cmd Buttons based on what they want to accomplish. Typically this would open a form. I am thinking that I would like to save StudyID.Value (=30) into a Global variable that I could then use in a query to only show StudyID30 record when opening up the form.

I have searched all weekend and cannot find anything that is even remotely close. I am a beginner, but if I see an example I can usually get it to work after trial and error. Any help here would be appreciated.


You don't know what you don't know...

 

[MajP]

I amazed because that code is so old and this can be done much simpler now. However, there is still a lot of references to that link. That was needed in A97 or prior when there was no real CF.

1) Put a hidden unbound textbox on the subform. Call it "txtboxSelected"
2) In the subforms "OnCurrent" event put
me.txtboxselected = me.studyid
3)Now put an unbound textbox behind all of your controls and send to back. Span the length of the detail section. Set the default color. This is use for your highlighting
4)in conditional formatting for this textbox
expression is:
[textBoxSelected] = [studyID]


You can download the Northwind sample database that shows examples of how to do the rest. These are pretty common and trivial things and I am suprised that you can not find how to do it. The OpenForm method of the docmd object has ways to specify the filter to apply when opening a form.

http://msdn.microsoft.com/en-us/library/aa141520(v=office.10).aspx

Since the record is on the subform you would need something like

DoCmd.OpenForm "YourFormName", , ,"StudyID = " me.subformMainPage.form.StudyID

 

[PHV]

The Cmd Buttons seem to be in the header section of the subform, so I'd use something like this in the Click event procedures:

DoCmd.OpenForm "YourFormName", , ,"StudyID=" & Me!StudyID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

I amazed because that code is so old and this can be done much simpler now

It did seem like a lot of script just to highlight a row. I do try to ignore older posts, but this post to Lebans script was only a few years old. I count my blessings every day that ignorance is not painful.

Thank you for you advice and direction to the Northwind db.

The Cmd Buttons seem to be in the header section of the subform

The Cmd Buttons are on the main form. The All, Open, Close, Review button are in the header section of the subform. That is an option group that filters the list. I probably need to bring that up to the main form. The subform started as the main form, but it worked better as a subform.

Waubain

You don't know what you don't know...