Difficulty populating unbound combobox

Question

I'm new to MS Access and have been developing a basic CRM system. All has gone well so far, but am banging my head against what I assume is a very simple problem... I can understand VBS and get my way around Access in general, but am by no means an expert in either realm.

---

How do I make an unbound combobox automatically set itself to a certain value based on other values in a table?

I have a form for users to edit employee information, and this includes comboboxes for inputting an employee's Division and Branch. There are about 10 divisions, each with around 5 branches.

The Branch combobox saves the BranchID to the tblEmployee table, with this acting as a key to the tblBranch table. The tblBranch table also has BranchName and DivisionID, with DivisionID as the key to the tblDivision table, which basically just has Division ID and DivisionName.

Right now the Division combobox is unbound, as this is merely there to allow the user to narrow down the Branches combobox. Cascading the menus down like this works fine once you click on the Division combobox, but when I open the form, the Division combobox shows up blank (and therefore the Branch combobox also shows up blank, as it has a criteria reference to the Division combobox).

How do I make the Division combobox look to see what the BranchID is for a specific employee, and then pre-set itself with the relevant Division?

I tried doing this both by setting a binding and also as an event, but I couldn't get either way to work properly... (likely just newb-ness on my part).

Thanks!

---

Thanks! Glad to see I was generally on the right path. It's still not working, however, and I think it's because I'm not understanding the YourEmployeeIDField.

My code is as follows:

Private Sub cboDivision_Enter()
    Dim sql_ As String
    sql_ = "SELECT d.Division " & _
           "FROM tblDivision d INNER JOIN (tblBranch b INNER JOIN tblEmployee e ON b.BranchID = e.BranchID) ON d.DivisionID = b.DivisionID " & _
           "WHERE e.EmployeeID=" & Me.txtEmployeeID

    Me.cboDivision.RowSource = sql_
End Sub

I removed the selection criteria on the comboboxes to make sure that things weren't getting filtered out accidentally.

I tried using EmployeeID, e.EmployeeID, txtEmployeeID (the field on the form), and Me.txtEmployeeID, without much luck.

After investigating the RowSource approach a little bit more, I wonder if I may not have explained what exactly I want? (Or I'm just misunderstanding) The combobox cboDivision is populated with the tblDivision.Division, which is want I want. However, because it is unbound, when a record is loaded in my form, cboDivision is blank. I want it to display the Division associated with the bound cboBranch combobox (but when you click on cboDivision, it still has the full list of Divisions available to select).

---

The button code that I'm using is as follows (it's simplified SQL, but the same result happens with your code):

Private Sub Command240_Click()
    'Me.cboDivision.Value = 8
    Dim sqlStr As String
    sqlStr = "SELECT d.DivisionID" & _
           "FROM tblDivision d INNER JOIN tblBranch b ON b.DivisionID = d.DivisionID)" & _
           "WHERE b.BranchID=" & BranchID

    Me.cboDivision.Value = sqlStr

    MsgBox ("You clicked me.")
End Sub

The commented out "Me.cboDivision.Value = 8" makes the cboDivision combobox show the division associated with DivisionID 8, which is effectively what I want; however, if I click the button with the current code, the combobox updates to: "SELECT d.DivisionIDFROM tblDivision d INNER JOIN tblBranch b ON b.DivisionID = d.DivisionID)WHERE b.BranchID=45"

(The 45 at the end is the correct BranchID for the record, so that part is working at the very least).

Answer 1

You need to change the RowSource property of the combobox to populate the correct Division information based on the EmployeeID selected.

You can do this on the control's Enter event:

Private Sub YourComboBoxName_Enter()
    Dim sql_ As String
        sql_ = "SELECT d.DivisionName " & _
               "FROM tblDivision d INNER JOIN (tblBranch b INNER JOIN tblEmployee e ON b.BranchID = e.BranchID) ON d.DivisionID = b.DivisionID " & _
               "WHERE e.EmployeeID=" & YourEmployeeIDField

    Me.YourComboBoxName.RowSource = sql_
End Sub

The combobox control's RowSourceType must be set to Table/Query.

Answer 2

I figured out what I wanted to do... and it may have been that I didn't explain it properly. The code to do what I needed was as follows:

Private Sub Form_Current()
    Me.cboDivision.Value = DLookup("DivisionID", "tblBranch", "BranchID=" & BranchID)
    Me.cboMinistry.Value = DLookup("MinistryID", "tblDivision", "DivisionID=" & Me.cboDivision)
End Sub

So when I change the current record, it updates the unbound comboboxes based on the bound value of BranchID.

Thanks for your help, responders!