Solved Cascaded combobox in Navigation form (1 Viewer)

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
Hi,

I'm a newbie re MS Access so my track record is not so long. But I've some track record regarding SQL and VBA code.
I've managed to succeed with getting cascaded comboxes to work in ordinary forms. But when I add these forms to a Navigation form with both horizontal and vertical tabs I don't get them to work. I've googled a lot and tried a lot of different things - unfortunately without any success. I've 'placed' my forms on the vertical tabs, if this has something to do about it?
I've read, and tried, e.g. [Forms]![NavigationForm]![NavigationSubform].[Form]![comboBox] as filter input in my query. I've also tried to add my specific form name with the combobox placed inside.
Now I've started to think if I can write some SQL string in the rawdata source in the property sheet in order to get around the challenge...

I don't want to create my own Navigationform, but I consider it strongly as well after all this frustration.

Does any kind soul have any recommendations?
 

Micron

AWF VIP
Local time
Today, 04:51
Joined
Oct 20, 2018
Messages
3,476
I don't get them to work
what does that mean? The title and you post are a bit confusing - the combos are not on the nav form, they are on the usual form but whatever the issue is, it arises when these forms are loaded into a nav form? These combos are not referencing another form that you think you have 'loaded' into the nav form by any chance? Only one form at a time is loaded into the nav control. When you switch forms, one closes and the other opens.

The syntax you posted is for referencing controls on a form on a nav form. How would that fit with your combo's being on regular forms when those forms are loaded into the nav form?

OOPS I just noticed you asked and I answered a question in the introductory forum. Maybe a moderator will move this. I think there is a request at the top of this forum when you create a post that asks that you do not post questions here.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 19, 2013
Messages
16,553
moved to the forms forum
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:51
Joined
Oct 29, 2018
Messages
21,357
Hi hhag. Welcome to AWF!

Maybe [Navigation Form] supposed to have a space?
 

HiTechCoach

Well-known member
Local time
Today, 03:51
Joined
Mar 6, 2006
Messages
4,357
I find it best to use VBA code in the after update event of the Primary combo box to set the row source for the dependent (cascading) combo box control to avoid any issues with form references.
 

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
I find it best to use VBA code in the after update event of the Primary combo box to set the row source for the dependent (cascading) combo box control to avoid any issues with form references.

This sounds very nice!
I would really appreciate if you could note some lines of code on how to do this. Many, many thanks in advance!
 

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
Hi hhag. Welcome to AWF!

Maybe [Navigation Form] supposed to have a space?

No, unfortunately not. My form names don't have any spaces.
Thanks for your time! I appreciate it!
 

Micron

AWF VIP
Local time
Today, 04:51
Joined
Oct 20, 2018
Messages
3,476
My form names don't have any spaces.
So when you created this nav form you did not use the default name that Access gives to the nav form OR the navigation control? If that is correct the you must replace the defaults with your own names for both objects. Perhaps you did that.
 

HiTechCoach

Well-known member
Local time
Today, 03:51
Joined
Mar 6, 2006
Messages
4,357
This sounds very nice!
I would really appreciate if you could note some lines of code on how to do this. Many, many thanks in advance!

Something like:

Code:
Private Sub Combo1_AfterUpdate()


  Me.Combo2.RowSource = "Select ... Where [FieldName] = " & Me.Combo1

  Me.Combo2..Requery

End Sub
 

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
Hi again,

Still I don't get the query, which the second combobox is based on, to update correct. I interpret your input as follows:

Private Sub cboValdAnL_AfterUpdate()
Me.cboAffM.RowSource = "Select ... Where [LeaderID]=" & Me.cboLeader
Me.cboAffM.Requery
End Sub

The fieldname above is a fieldname in the query to be filtered. Correct?

Once again, many thanks in advance!

Rgds,
HHAG
 

HiTechCoach

Well-known member
Local time
Today, 03:51
Joined
Mar 6, 2006
Messages
4,357
Me.cboAffM.RowSource = "Select ... Where [LeaderID]=" & Me.cboLeader

First, in the WHERE clause of the SQL statement, is [LeaderID] a numeric field data type?

If a text data type then you must use the string delimiter like this:

Code:
... Where [LeaderID]=" & Chr(34) & Me.cboLeader & CHR(34)


Second, in SELECT part of the SQL statement, are you entering the fields and table name before the WHERE?

TIP: Use the Query Designer to build a working query including the filer of the filed. Manually type in a value for the criteria that you know is valid to test it. Switch to SQL view, and copy and paste the SQL into your code and make any adjustments need. I still use this trick after 20+ years working in Access.
 

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
Hi,

Still some problems.... frustrating.
And I'm soo happy that you HiTechCoach take some time with me.

I've attached a PDF describing my issue.... and I hope for some new response :)
 

Attachments

  • Access forum 1st issue.pdf
    87.3 KB · Views: 113

HiTechCoach

Well-known member
Local time
Today, 03:51
Joined
Mar 6, 2006
Messages
4,357
From the PDF:


Access forum 1st issue-2.jpg

1. This is the primary combobox. It’s named ‘cboValdAnL’.
The SQL code below AfterUpdate event is as follows:

Me.cboVäljAffM.RowSource = "SELECT tblAffM.AffMID, tblAffM.Projektnamn,
tblProjektort.Projektort, tblStatus.Status, tblAffM.AnbudsledareID" _
& " FROM tblStatus INNER JOIN (tblProjektort INNER JOIN tblAffM ON tblProjektort.[ProjektortID]
= tblAffM.[ProjektortID]) ON tblStatus.StatusID = tblAffM.StatusID" _
& " WHERE (((tblStatus.Status) = 'Open' Or (tblStatus.Status) = 'Not started') And
((tblAffM.LeaderID) = me.cboValdAnL))" _
& " ORDER BY tblAffM.Projektnamn, tblProjektort.Projektort"
And the I add:

me.cboValdAffM.Requery

2. When above code is running, I still get the InputBox. Strange. I’ve checked that I actually have a
value for me.cboValdAnL (and this is a numeric field). It’s just that it doesn’t seem to interpret the
numeric value in cboValdAnL
3. This is the second combobox, which name is cboValdAffM


Can it have something to do with that the form is places inside a navigation form with both
horizontal and vertical tabs?



You are getting very close.

Try changing the following:

Code:
& " WHERE (((tblStatus.Status) = 'Open' Or (tbl((tblAffM.LeaderID) = me.cboValdAnL))" _

to a data substitute into the SQL using one of the following:

If tblAffM.LeaderID is a numeric data tyoe
Code:
& " WHERE (((tblStatus.Status) = 'Open' Or (tbl((tblAffM.LeaderID) = " & Me.cboValdAnL & "))" _


If tblAffM.LeaderID is a text data tyoe
Code:
& " WHERE (((tblStatus.Status) = 'Open' Or (tbl((tblAffM.LeaderID) = " & Chr(34) & Me.cboValdAnL & Chr(34)  & "))" _
 
Last edited:

hhag

Member
Local time
Today, 09:51
Joined
Mar 23, 2020
Messages
69
Yes!! Finally!
I'm extremely grateful HiTechCoach for all help I've received from you! I appreciate it a lot!
Thank you so much for your time. :)(y)🙏🙏
Your TIP was 👍 And, if I had read your comments more in detail I had found the answer earlier. Sorry!
 

Users who are viewing this thread

Top Bottom