populate combo box based upon another combo box selection tied to subform

[mdewell]

I have a form with a combo box that lists computer IDs from a table of all the computer IDs, to select focus on a particular computer ID in the subform. I've created a second combo box that can filter the subform's data based upon a different field, OU, in the subform. I want to be able to requery the subform's computer list, based upon the filter by OU, and repopulate the Computer ID combo box.

I thought I could do it by having in the selection query for the combo box's criteria getting its data from the subform's field entries, such as:

Criteria : Like ([Forms]![HIPAA_datasheet subform]![Computer])

as an entry in the computer query of the combo box. The combo box for the computer list is built off of one table, Computers, that is relationally linked to the main HIPAA Table, for popoulating the field for computer IDs in it. When I do this and I select the Computer ID, Access brings up an "Enter Parameter Value" window.

[June7]

I always name subform container controls different from the form they hold, like ctrHIPAA.

Is this code behind the main form?

Refer to the Computer field through the main form then the subform, like: Me.ctrHIPAA!Computer

If you want to refer to a subform control (unbound or bound), like: Me.ctrHIPAA.Form.controlname

I always name bound controls different from the field, like: tbxComputer

[mdewell]

Thanks for the reply June7. I think that would work for a VB code app to the combo box, but does Me.ctrHIPAA!Computer work if I've got the Row Source Query Builder window open and I'm filling in the Criteria field? At least that's how I was thinking of doing the filtering.

On another note, the field is called Computer, and the combo box is called ComputerLookup. sorry I'm not into the small descriptive letters first, but I may get into it, for it seems to be a VBA standard.

Thanks

[June7]

Exactly where is the combobox - on main form?

Referencing in Access controls and queries would be like:
Forms!formname.ctrHIPPA!Computer

The Me alias qualifier works only in VBA.

The name prefixes are not mandatory but conventional to programmers.

[mdewell]

Sorry it seems a little unclear.

The combo box is in the main form that houses the subform with names that follow:

Form : HIPAA_datasheet
Subform : HIPAA datasheet subform
Combo Box : ComputerLookUp
query field : Computer

I thought I could do it by just puttng in

Like ([Forms]![HIPAA_datasheet_subform]![Computer])

in the Criteria field of the Row Source Query Builder for the ComputerLookUp properties. Yet when I do this it brings up the "Enter Parameter Value" window.

[June7]

Okay, try my suggestion.

On reread of your OP, something odd about what you are trying. The reference to the subform field will return only the one value of the current record of the subform. Is that what you want?

Do you want to provide project for analysis? Copy database, remove confidential data, run Compact & Repair, zip if large, attach to post. The Attachment Manager is below the Advanced post editor.

[mdewell]

Hello June7,

I am trying to get more than one value, and I thought the LIKE would return more than one value. I'll try cleaning up the data and let you get a go at it if you want. I do know that if I enter something like *ACU* in the "Enter Parameter Value" window, it returns all the strings that match *ACU*, and that is what I wanted, so I know somehow this can be done.

Thanks

[June7]

LIKE operator will return more than one value when wildcard is used with it, otherwise just use = sign. Sorry, I neglected to follow through on my example.

Like ([Forms]![HIPAA_datasheet_subform]![Computer]) & "*"

or

Like "*" & ([Forms]![HIPAA_datasheet_subform]![Computer]) & "*"

[mdewell]

Here you are, see what you can do with it.

[June7]

If I understand, user should select OUFilter first as first step filter. Then want to restrict the ComputerLookUp to only computers that are associated with the OU. Referencing the Computer field on subform will not work. I got this to work.

1. Set the OUFilter BoundColumn to 1
2. Move the line that sets ComputerLoopkUp to "" into ComputerLookUp_AfterUpdate
3. Set ComputerLookUp properties
ColumnCount: 1
ColumnWidth:
BoundColumn: 1
RowSource:
SELECT Computer.Computer FROM (HIPAA_relational LEFT JOIN OU ON HIPAA_relational.OU_ID=OU.ID) LEFT JOIN Computer ON HIPAA_relational.Computer_ID=Computer.ID WHERE OU.ID=Forms!HIPAA_datasheet!OUFilter;

[mdewell]

Thanks June7,

You got me going in the right direction, but I had to mess around with it. I also did some thinkiing in my sleep I guess, for when I woke up I had a much better idea of how to go about it.

As I think you saw, I had a function SetDataSheetFliter. But, I then decided to edit it so that the filter would change the Row Source for the combo box. I somehow missed that in the code I could reassign the row source. So if you look at the code for the HIPAA_datasheet, you will see how I got it to work.

I just need to get my Computer Lookup combo box to highlight the actual computer name it selected, not the OU.

Thanks for your help again.

[June7]

I did test setting the RowSource with VBA but even that would not work when I used the HIPAA query as the source. That's why I set up combobox with its own RowSource SQL and then it all worked.

You want to set focus to the Computer textbox in the subform? I suspect edit of the OU should not be allowed. Set this control as Locked Yes and TabStop No. This will put the Computer textbox first to get focus. I like to give uneditable controls a transparent background, although in Datasheet View that will not be apparent.

[mdewell]

It's all good June7. I did just set the Row Source for the control in the VBA of the SetDatasheetFilter subroutine to the SQL query using the desired OU. It seems to work just fine.

About the set the focus, that was easy, I just did a Me.HIPAA_datasheet_subform.Form.Computer.SetFocus. That was what the was about. It was something that I wanted to get the OU combo box/subform/Computer combo box problem taken care of first.

Thanks again for all your help.

[June7]

Okay, code to set focus works but doesn't address the concern of improper editing of the OU field in the subform. Address that as I suggested and the code is unnecessary.

[mdewell]

June7,

Perhaps I'm understanding now what you were running into. When I go to the HIPAA_single_computer form. When I create a combo box and I try to set the OU for a single computer in the form, it changes all the computers in that OU to the OU selected. I.E. If the computer "CHICE50058" in "BioMed" is set to "ACU", then the other computer "CHICE50259" that was in "BioMed" gets set to "ACU" as well. Is this what you where pointing to?

I'll include my current MDB so that you can see that I'm filtering more data in the subform, as well, based upon selections in the main form, and then limiting the computer list to those in the subform.

Perhaps I'm not familiar enough with the LEFT JOIN SQL function. Maybe you could direct me to a better understanding of it, or explain it yourself.

Thanks