Solved: In an Access subform, how to look up the values for an unbound combo box based on the values of two other text boxes - all located on the subform

Create Account

Microsoft Access

Questions

Followers

Top Experts

fabi2004🇺🇸

In an Access subform, how to look up the values for an unbound combo box based on the values of two other text boxes - all located on the subform

It's pretty much in the title. I've been stuck on this and have run out of ways to try it.

I have a subform with a combo box that looks up values in a query. The values returned should fall between two dates located on two bound text boxes on the form.

I can't use a simple query for the Row Source of the combo box because the query doesn't recognize controls from a subform.

I can't use DLookup because it only returns a single value.

I've tried setting up a record set with VBA but I can't seem to get the code right (or it doesn't work this way either).

parent form: frmAssignment
tabcontrol: pg 1
subform: frmAssignmentSchedule
LinkMasterField: OrderID
LinkChildField: OrderID

On the subform the date fields are AStartDate and AEndDate, both bound to the subform's record source. The combo box is cmbHolidayView and I need this:

SELECT qryHolidayDates.HolidayName, qryHolidayDates.HolidayDate, qryHolidayDates.HolidayDayName
FROM qryHolidayDates
WHERE (((qryHolidayDates.HolidayDate) Between [Forms]![subfrmAssignmentSchedule].[AStartDate] And [Forms]![subfrmAssignmentSchedule].[AEndDate]))
ORDER BY qryHolidayDates.HolidayDate;

which works great from when the subform is opened directly, but I need it to work when the subform is opened from the parent form.

The combo box does not have to update any underlying table nor does it have to hold any data. It only needs to show the user which holidays fall between the two dates. Once the user views them, they are no longer needed.

Any ideas?

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

PatHartman🇺🇸

You need to qualify the subform by the object which contains it which is the main form. One of these will work as long as the Name property is - subfrmAssignmentSchedule. It doesn't matter what the actual subform object name is since you are referring to the subform control name.

SELECT qryHolidayDates.HolidayName, qryHolidayDates.HolidayDate, qryHolidayDates.HolidayDayName
FROM qryHolidayDates
WHERE (((qryHolidayDates.HolidayDate) Between [Forms]!frmAssignment![subfrmAssignmentSchedule].[AStartDate] And [Forms]!frmAssignment![subfrmAssignmentSchedule].[AEndDate]))
ORDER BY qryHolidayDates.HolidayDate;

OR

SELECT qryHolidayDates.HolidayName, qryHolidayDates.HolidayDate, qryHolidayDates.HolidayDayName
FROM qryHolidayDates
WHERE (((qryHolidayDates.HolidayDate) Between [Forms]!frmAssignment.Form![subfrmAssignmentSchedule].[AStartDate] And [Forms]!frmAssignment.Form![subfrmAssignmentSchedule].[AEndDate]))
ORDER BY qryHolidayDates.HolidayDate;

John Tsioumpris🇬🇷

If the subform has code behind...(hint:just hit the visual basic button...open the VBE and close it again) then you could reference it like this

SELECT qryHolidayDates.HolidayName, qryHolidayDates.HolidayDate, qryHolidayDates.HolidayDayName
FROM qryHolidayDates
WHERE (((qryHolidayDates.HolidayDate) Between Form_subfrmAssignmentSchedule.AStartDate And Form_subfrmAssignmentSchedule.AEndDate]))
ORDER BY qryHolidayDates.HolidayDate;

Open in new window

fabi2004🇺🇸

ASKER

Hi Pat,

Thanks for the feedback. But those were the first things I tried. I couldn't figure out what was wrong with my SQL until I read somewhere that a query can't use a subform as a criteria parameter. --- or something like that.

John,

Thank you for the feedback as well. I've never seen the underscore used before. Unfortunately, yours as well as Pat's suggestions all result in a pop-up request for the parameter.

I'm using Access 2016 if that makes any difference.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

John Tsioumpris🇬🇷

Then there is something wrong...can you post a screenshot

fabi2004🇺🇸

ASKER

A screenshot of ?

fabi2004🇺🇸

ASKER

Here is the parameter prompt using your SQL with the underscore.
ParameterPrompt.png

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

fabi2004🇺🇸

ASKER

I'm thinking I need some hidden fields in the parent form that take on the values of the textboxes in the subform. Then I can use those hidden fields on the parent form in the query for the combo box on the subform.

This seems so convoluted so I'm holding out for a better option.

John Tsioumpris🇬🇷

have you activated the cide behind?...its crucial

fabi2004🇺🇸

ASKER

activated the cide behind

I don't know what that means.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

John Tsioumpris🇬🇷

right click the subform ->design view ->click the button of visual basic...you are entering the Visual Basic Editor...close it...try again

fabi2004🇺🇸

ASKER

I'm sorry, I'm just not following your train of thought. Why do you want me to open, close and re-open the vba editor?

I've created a very simplified sample db showing the problem I'm having. See the form "frmExample" , tab "page 7", click the combo box "Holidays"
Database12.zip

PatHartman🇺🇸

my SQL until I read somewhere that a query can't use a subform as a criteria parameter. --- or something like that

Not true.
Did you verify that you were using the Name Property of the Subform CONTROL rather than the actual name of the subform object in your reference?

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

SOLUTION

John Tsioumpris🇬🇷

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

PatHartman🇺🇸

The syntax of the Where clause was incorrect. Deciding when to use the bang (!) vs the dot (.) is certainly confusing but in general Access defined objects are qualified with the dot and user defined objects are qualified with a bang. The one exception that I know of is that controls on forms and reports as well as all columns of the form/report RecordSource are added to the fields collection of the form/report so in this instance Access adopts these user defined objects as its own. This is done in order to allow the VBA editor to give us intellisense. So - Me.mycontrolname will give us intellisense but Me!mycontrolname will not. In queries, you can't reference the "Me." object so user defined objects are always referenced using the bang (!). Sorry - my knowledge in this area is limited but that is my understanding of the why and how of bangs and dots.

SELECT tblHolidays.HolidayID, tblHolidays.Day, tblHolidays.Holiday, tblHolidays.Date
FROM tblHolidays
WHERE (((tblHolidays.Date) Between [Forms]![frmExample]![subfrmExample]![AStartDate] And [Forms]![frmExample]![subfrmExample]![AEndDate]))
ORDER BY tblHolidays.Holiday;

fabi2004🇺🇸

ASKER

John, thank you so much for helping me. Unfortunately, that doesn't work in my actual db. I still get the prompt for a parameter when I use it.

I just can't seem to get the query to recognize the controls.

Could it have anything to do with the tab control? My understanding is that we can ignore any reference to the tabs themselves. But I'm really out of ideas as to what I'm doing wrong naming those controls as parameters.

Just in case this is significant - although I can't imagine why...

The parent form clears the tab controls:

Private Sub Form_Load()
    Me.TabCtlAssignmentDetails.value = 0
    TabCtlAssignmentDetails_Change
End Sub

Open in new window

Then the tab control's on current event opens individual forms...

Private Sub TabCtlAssignmentDetails_Change()
    
        Select Case Me.TabCtlAssignmentDetails.value
            Case 0
                Me.tabctlsubform.SourceObject = "subfrmAssignmentDetails"
            Case 1
                Me.tabctlsubform.SourceObject = "subfrmAssignmentSchedule"
            Case 2
                Me.tabctlsubform.SourceObject = "subfrmAssignmentMPInsurance"
             Case 3
                Me.tabctlsubform.SourceObject = "subfrmAssignmentExpenses"
            Case 4
                Me.tabctlsubform.SourceObject = "subfrmAssignmentReimbursables"
            Case 5
                Me.tabctlsubform.SourceObject = "subfrmAssignmentSummary"
        End Select
        
End Sub

Open in new window

It's hard to be stuck for so long on something that should have been so straightforward. :-/

fabi2004🇺🇸

ASKER

Pat, thank you so much for that explanation. I can't seem to keep those straight. Thank goodness Access does most of it for me even when I get it wrong.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

fabi2004🇺🇸

ASKER

sorry, I meant to say the tab control's "change event" earlier.

fabi2004🇺🇸

ASKER

Pat,

You've lost me with this:

Did you verify that you were using the Name Property of the Subform CONTROL rather than the actual name of the subform object in your reference?

PatHartman🇺🇸

You're welcome. The query worked for me in the sample database so hopefully, you can translate it to the real one given the bang and dot explanation.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

PatHartman🇺🇸

In your database the name of the subform is the same as the Name property of the subform control on the main form. They do not need to be the same. The name of the subform object is stored in the SourceObject property on the Data tab. The point is that when referencing controls on subforms, you need to follow the hierarchical path and that is via the Name Property of the subform Control NOT it's ControlSource property.

This may be relevant in the production version of the app although it isn't in the sample you posted. Most of the time these two properties end up with the same name because Access uses the name of the ControlSource object as the Name of the control but you can change that and it might be different if someone else built the form or if you added the subform by using the subform control in the ribbon rather than dragging the subform onto the main form from the navigation pain (sic).

fabi2004🇺🇸

ASKER

Thanks again.

An y idea why my naming is affecting how this should work? I just can't get sql to recognise those controls.

This was what I duplicated given yours and John's examples, but neither worked. Still getting a parameter prompt when clicking the combo box on the subform when the subform in opened inside the parent form.

I have no trouble at all with it if I create the sql to work outside the parent form (with the subform as a stand alone form). It's only when it's combined with the parent form that those controls get lost.

SELECT qryHolidayDates.HolidayName, qryHolidayDates.HolidayDate, qryHolidayDates.HolidayDayName
FROM qryHolidayDates
WHERE (((qryHolidayDates.HolidayDate) Between [Forms]![frmAssignments]![subfrmAssignmentSchedulel]![AStartDate] And [Forms]![frmAssignments]![subfrmAssignmentSchedulel]![AEndDate]))
ORDER BY qryHolidayDates.HolidayDate;

Open in new window

PatHartman🇺🇸

There is no way we can debug this without the actual application. Did you check the ControlSource property of the subform control and check its Name property? If they are DIFFERENT, you MUST use the Name property.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

fabi2004🇺🇸

ASKER

:-( I just don't know what this means

ControlSource property of the subform control and check its Name property

The subform doesn't have a control source, it has a record source which queries 3 tables.

The name of the subform is subfrmAssignmentSchedule.

The combo box is unbound (no control source) and named cmbHolidayView. The query above is set up as the RowSource.

The problem has something to do with referencing the subform's controls from the parent form. Otherwise, it works fine.

The db is 1.4 Mb when I zip it up. Is that too large?

If so, maybe I can spend some time next week creating a copy that only includes the pertinent tables and forms.

PatHartman🇺🇸

I'm sorry. I'm so used to referring to the ControlSource. The subform has a SourceObject.

1.4 mb is probably too big to upload, plus you don't want to post any sensitive data.

fabi2004🇺🇸

ASKER

I think I figured out what the problem is.

The parent form contains a tab control minimized so only the tabs show. Then there is a subform directly beneath it called tabctlsubform.

The tabctlsubform shows different subforms depending on which tab is clicked.

When "Case 1
Me.tabctlsubform.SourceObject = "subfrmAssignmentSchedule"

then we see the subfrmAssignmentSchedule.

So, how do I reference subfrmAssignmentSchedule since it will be the "SourceObject" of the tabctlsubform?

I tried Forms!frmAssignments!tabctlsubform!subformAssignmentSchedule!AStartDate --- that didn't work. So, do I try to reference the AStartDate control as if it was on the tabctlSubform itself?

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

fabi2004🇺🇸

ASKER

oops! Yes, we were posting at the same time. The problem is that the SourceObject is sufrmAssignmentSchedule but the subform of the parent form is tabctlsubform

ASKER CERTIFIED SOLUTION

PatHartman🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

fabi2004🇺🇸

ASKER

Yes Yes Yes! That worked. :-) <--- big smile

It was a problem of my own making and not enough skill to understand what you were saying from the very beginning.

Thank you!!!

And thank you to John as well for solving the problem as presented in the sample. It just wasn't exactly what I was dealing with in the real db because I didn't set up the sample to match.

Breath of relief. Much better way to start the weekend now.

Thanks again to both of you!

PatHartman🇺🇸

You're welcome.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Microsoft Access

Questions

Followers

Top Experts

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.