Available List Based on Previous Field's Selection

[Gaddy]

Hi There,

I'm not sure that this is the correct section...

I have a form whereby a user for my database can be created. The fields required are listed below:

Payroll ID
Forename
Surname
Location
Manager

The last two fields are related to two other tables; tblLocation and tblManager, which are related to one another.

Each location (there are three) can have more than one manager, however, a manager can be associated with only one location.

I would like whomever is creating the user to only be able to select a Manager that works at a particular location, currently, I can select LocationID 1 and ManagerID 3, but ManagerID 3 works at LocationID 2.

How can I base the 'Manager' field in my form on the previous field's selection, e.g. 'Location'.

Also would need to restrict the order in which the form's fields are enter, i.e., 'Location' before 'Manager'.


Hope this made sense.

Many thanks.

 

[missinglinq]

The standard approach for this would be to use two 'Cascading Comboboxes.' The first Combobox would list the locations, and once a location is chosen the second Combobox would be populated only with the names of managers associated with that location. The second, 'Managers' Combobox could be set empty until a location is chosen.

As I said, the concept is called "Cascading Comboboxes" although the exact same thing applies to Listboxes with the MultiSelect Property set to None, and the code is interchangeable. Here are a couple of links with examples:

http://www.fontstuff.com/access/acctut10.htm

http://bytes.com/forum/thread605958.html

If you search here or Google the term "Cascading Comboboxes" along with "MS Access" you'll probably get a gazillion hits!

Linq ;0)>

 

[Gaddy]

Thank you for pointing me in the right direction. I think I am getting there, I've looked at the two links you've kindly provided above, but I am not knowledgeable enough to make them work for my db. I've found a website which was perfect (though I can't add it due to my postcount but it is on databasedev - filter combo boxes).

I've got my second combo box (cbo.ManagerID) to filter by the two Managers that are in location 1, but it currently shows the ID rather that their name.

If I click into the manager combo box first, it will display correctly (Forename & Surname), e.g. Sam Fisher. However, as mentioned above, it will show IDs if I go into the combo box for the location...

How can I get get it to display the forename (tblManager.Forename) and surname (tbl.Manager.Surname)?

The row source for cboManagerID is SELECT tblManager.ManagerID, [Forename] & " " & [Surname] AS ManagerName, tblManager.Forename, tblManager.Surname FROM tblManager;

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID]," & _
                   " [tblManager].[ManagerID]," & _
                   " [tblManager].[Forename] " & _
                     "FROM tblManager " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
    Me.cboManagerID.Requery
End Sub

Please can you also advise as to what is the best way to prevent data being entered into the second combo box prior to the first combo box being filled out?

 

[Cronk]

If you want to stop the user entering new Manager records then set the LimitToList (Data tab) of the cboManagerID properties to Yes.

 

[Gaddy]

Apologies, I didn't quite finish the last sentence.

Post edited.

 

[Gaddy]

I'm looking at it with a fresh set of eyes today. I've changed the code to:

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID]," & _
                   " [tblManager].[Forename]," & _
                   " [tblManager].[Surname] " & _
                     "FROM tblManager " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
    Me.cboManagerID.Requery
End Sub

And have adjusted the 'Column Count' to '3' and adjusted the column width to 0cm;1cm;1cm

This has made it work, but it doesn't quite look how I want it to. It has the name like this: Sam|Fisher even though I have the rowsource set as outlined in one of my previous posts.

Any ideas?

EDIT: Looked at the second combo box's list before going into the first combo where the code is and the way it looks is different.

For now I've just deleted the rowsource as it's pointless being there. What can I change in this code so that it displays the Forename and Surname as one field with a space between the two?

 

[bob fitz]

Untested "Air Code" is not one of by strong points, but try something like:

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID]," & _
                   " [tblManager].[Forename]," & " " & [tblManager].[Surname] AS FullName " & _
                     "FROM tblManager " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
    Me.cboManagerID.Requery
End Sub

 

[Gaddy]

Hi,

Thanks for your response.

It came up red in the bit you had changed, I've added a speech mark before the [tblManager].[Surname] so that it works and now it looks like this:

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID]," & _
                   " [tblManager].[Forename]," & " " & " [tblManager].[Surname] AS FullName " & _
                     "FROM tblManager " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
    Me.cboManagerID.Requery
End Sub

However, it doesn't work. I've tried fiddling around with the rowsource and column width but to no avail.

 

[Cronk]

If you want to display entries like Smith, John in your combo then use the following

Private Sub cboLocationID_AfterUpdate()     Dim sManagerSource As String       sManagerSource = "SELECT [tblManager].[ManagerID]," & _                    " [tblManager].[Surname] & ', ' & " [tblManager].[Forename] " & _                      "FROM tblManager " & _                      "WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY Surname, Forename"     Me.cboManagerID.RowSource = sManagerSource      End Sub

Change the column count to 2 and the column width to 0 (it's not necessary to specify the last column width).

Note you don't need to include the Value property, nor requery the combo as this will happen when the Rowsource is changed.

 

[Gaddy]

That code doesn't work, keeps highlighting [tblManager] in the line (and also highlights in red):

"[tblManager].[Surname] & ', ' & " [tblManager].[Forename] " & _

Unexpected: end of statement

I've fiddled around some more and mixed 'n' matched (I've not got a clue what I'm doing as you can probably tell).

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID]," & _
                   " [tblManager].[Forename]," & " " & " [tblManager].[Surname] " & _
                     "FROM tblManager " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value & "ORDER BY Forename, Surname"
    Me.cboManagerID.RowSource = sManagerSource
End Sub

Syntax error (missing operator) in query expression '[LocationID] = 1ORDER BY Forename, Surname'

Surname and Forename are on tblManager not tblLocation.



EDIT: Changed it some more and I think there is something definitely wrong with the ORDER BY part...

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID], [tblManager].[Forename], [tblManager].[Surname]" & _
                     "FROM [tblManager] " & _
                     "ORDER BY Forename,Surname" & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
End Sub

Syntax error (missing operator) in query expression 'SurnameWHERE [LocationID 3'.

 

[Gaddy]

I think I'm getting there (I've learnt a bit this evening, I'm not going to sleep until it's working)...

I'm fairly certain that I need to convert this section:

sManagerSource = "SELECT [tblManager].[ManagerID], [tblManager].[Forename], [tblManager].[Surname]" & _

To the SQL equivalent:

SELECT tblManager.ManagerID, [Forename] & " " & [Surname] AS FullName

Then, I can look at getting this 'ORDER BY' bit done.


Another update:

sManagerSource = "SELECT [tblManager].[ManagerID], [tblManager].[Forename]," & " " & "[tblManager].[Surname] AS FullName" & _

I think I am getting warmer, and I am reasonably sure that I am getting the error message, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." because of the 'AS FullName' bit.



EDIT 2: Get in!! I've done it! I was thinking that perhaps the problem was with the double quotes and the statement thinking it was ending when I didn't want it to and it was - that probably didn't make sense...

Anyway I have got it working and the code looks like this:

Private Sub cboLocationID_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[ManagerID], [tblManager].[Forename] & ' ' & [tblManager].[Surname]" & _
                     "FROM [tblManager] " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
End Sub

 

[Cronk]

Gaddy

My apologies. As you can see, I did not delete the extra double quote in the manager name.

Anyway, it looks like you learned a bit in going through this process.

 

[Gaddy]

No worries - I also changed the double quotes where it joins the forename and surname together to single quotes.

I still cannot get this to sort into order though, no matter what I do.

 

[Gaddy]

I've now got some code that when the form is opened, disables the combo box - cboManagerID - if cboLocationID is null.

I've also got some further code on cboLocationID so that when a value is selected, cboManagerID becomes available.

This obviously prevents anyone from selecting the second combo box (cboManagerID) first, but it does not prevent bad data though...

For instance, I can select a location, then a manager, then switch the location and save.

How can I prevent this?


Here's my code:

Private Sub Form_Load()

    If IsNull(Me.cboLocationID) Then
        Me.cboManagerID.Enabled = False
        
    Else

  Me.cboLocationID.Enabled = True
            
End If
End Sub
Private Sub cboLocationID_AfterUpdate()
    
    If IsNull(Me.cboManagerID) Then
        Me.cboManagerID.Enabled = True
        
    Dim sManagerSource As String
    
        sManagerSource = " SELECT [tblManager].[ManagerID], [tblManager].[Forename] & ' ' & [tblManager].[Surname] " & _
                     " FROM [tblManager] " & _
                     " WHERE [LocationID] = " & Me.cboLocationID.Value & " ORDER BY Forename, Surname"
        Me.cboManagerID.RowSource = sManagerSource
  
End If
End Sub

EDIT: I've found a way by following this website http://accessprogrammer.blogspot.co.uk/2005/08/filter-combobox-based-on-another.html

Works perfectly for me.