Store Value from unbound combo box (1 Viewer)

rhonda6373 · Apr 1, 2019

First, I am a novice at Access. I have the following question:

I have a database of attorneys. The attorneys detail table has a column named section and division. Each attorney is assigned to a section and division. The divisions are a subset based on the section selected. I need to store both in the attorneys detail table. I have the combo boxes working as expected; however, I don't know how to store the values in the attorney detail table since it is an unbound field.

I have the division drop down based on the value from the sections drop down based on the following approach that I googled:

Sections table with a SectionID and SectionName
Divisions table with Division IDs associated Section ID, and Division Name

There is a SectionID and DivisionID in the attorney detail table.

There are two combo boxes on the form: cboSection and cboDivision. The record source for cboSection is:

Code:

SELECT tblSections.SectionID, tblSections.Section
FROM tblSections;

The record source for cboDivision is:

Code:

SELECT tblSectionsDivisions.DivisionID, tblSectionsDivisions.SectionID, tblSectionsDivisions.Division
FROM tblSectionsDivisions;

I added the following code to the after update event for the cboSection field on the form:

Code:

Private Sub cboSection_AfterUpdate()
Dim sAttorneySource As String
    sAttorneySource = "SELECT [tblSectionsDivisions].[DivisionID]," & _
                   " [tblSectionsDivisions].[SectionID]," & _
                   " [tblSectionsDivisions].[Division] " & _
                     "FROM tblSectionsDivisions " & _
                     "WHERE [SectionID] = " & Me.cboSection.Value
    Me.cboDivision.RowSource = sAttorneySource
    Me.cboDivision.Requery
End Sub

Thanks!!!!!!

theDBguy · Apr 1, 2019

Hi. You used the term "unbound field," but I am guessing you meant to say "unbound control." Fields are in the table and controls are on the form. If you're using an unbound combobox and want to store the value to the table, then why not use a bound combobox? You can bind the unbound combobox by assigning the appropriate field name in the Control Source property of the combobox.

plog · Apr 1, 2019

There is a SectionID and DivisionID in the attorney detail table.

There only needs to be a DivisionID in the attorney detail table.

Since a DivisionID is associated with a SectionID in tblSectionsDivisions you know what section an attorney goes to if you know what Division they go to. Don't store more data than necessary.

rhonda6373 · Apr 2, 2019

If I bind the combo boxes, then how can I filter the division combo box to only show the divisions that are associated with the section that was selected?

The_Doc_Man · Apr 2, 2019

The answer to the question in post #4 would be found by looking up the topic we call "Cascading Combo Boxes" which is a very common topic in this forum.

rhonda6373 · Apr 2, 2019

I am not sure what I was doing differently than yesterday other than slept. I must have been binding the control incorrectly. At any rate, binding the control with the approach mentioned in my first post worked!

Thanks for all of your help and I appreciate your time!

Rhonda

theDBguy · Apr 2, 2019

Hi Rhonda. Glad to hear you got it sorted out. Good luck with your project.

Store Value from unbound combo box (1 Viewer)

rhonda6373

Registered User.

theDBguy

I’m here to help

plog

Banishment Pending

rhonda6373

Registered User.

The_Doc_Man

Immoderate Moderator

rhonda6373

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread