Synchronized Comboboxes

[IncidentalProgrammer]

I have a form in my database, which I mean for users to enter insurance claims through. The first step is to select an account from a combobox I have set up with its row source as a query that pulls only accounts (as oppsed to clients, companies, etc); this works fine. From there, a second combobox is meant to allow the user to select the appropriate insurance policy to place the claim on; this is where I'm running into trouble.



The policy number box is supposed to be limited to just the insurance policies belonging to the client the user has selected in the first combobox. I set up my code just like I saw on Microsoft's website, but I can't get this to work; every time I click the arrow, I just get a blank drop-down. Can anyone see the problem in my code?

Private Sub ctlEntID_AfterUpdate()
     'Update row source of policy number combobox based on account selection.
     
    Me.ctlPolNum.RowSource = "SELECT PolNum FROM" & _
    " tblPolAct WHERE EntID = " & Me.ctlEntID & _
    " ORDER BY PolNum"
    
    Me.ctlPolNum = Me.ctlPolNum.ItemData(0)
End Sub

"ctlEntID" is the first combobox (for selecting the account), "ctlPolNum" is the second combobox (selects policy number), "tblPolAct" is the table that stores a record of policies per account.

[burrina]

For your second combo, I believe you need to have both tables as the record source so that they are linked. Maybe you have this or not, don't know!

[IncidentalProgrammer]

I added in the table that the first combo is drawing from, but no change in the results. I tried as:

rivate Sub ctlEntID_AfterUpdate()
     'Update row source of policy number combobox based on account selection.
     
    Me.ctlPolNum.RowSource = "SELECT PolNum FROM" & _
    " tblPolAct.EntID, tblEntity.EntID WHERE EntID = " & Me.ctlEntID & _
    " ORDER BY PolNum"
    
    Me.ctlPolNum = Me.ctlPolNum.ItemData(0)
End Sub

And

rivate Sub ctlEntID_AfterUpdate()
     'Update row source of policy number combobox based on account selection.
     
    Me.ctlPolNum.RowSource = "SELECT PolNum FROM" & _
    " tblPolAct, tblEntity WHERE EntID = " & Me.ctlEntID & _
    " ORDER BY PolNum"
    
    Me.ctlPolNum = Me.ctlPolNum.ItemData(0)
End Sub

[June7]

The second combobox RowSource should not have to include both tables.

Do you have lookups set in table? What are the properties of first combobox:
RowSource
ColumnCount
ColumnWidths
BoundColumn

An alternative is to put the dynamic SQL statement directly in the RowSource and then the VBA would just be:

Me.ctlPolNum.Requery

If you want to provide db for analysis, follow instructions at bottom of my post.

[IncidentalProgrammer]

I haven't been using lookup fields because I read that they won't work in SQL Server, and once I'm done building this thing, it's getting cracked in half, and the back-end is going into SQL.

I'll try the SQL statement in Row Source. I'd tried that first and then read about these synchronized comboboxes, but maybe it wasn't working because I overcomplicated it trying to cram too much data into it (I have to populate 5 other fields based on the policy number. But maybe if I try just writing a short SQL for each box, it'll work better.

As of right now:
RowSource: none
ColumnCount: 1
Column Widths: none
BoundColumn: 0

[June7]

Think BoundColumn should be 1.

But I was asking for properties of first combobox, which I expect does have a RowSource other than none.

The synchronization should work with either approach.

[IncidentalProgrammer]

Woops! Sorry; that's what I get for trying to concentrate on two things at once.

RowSource: SELECT tblEntity.EntID, tblEntity.EntPrimName, tblEntity.EntStatus, tblEntity.EntStatDt, tblEntity.EntType FROM tblEntity WHERE (((tblEntity.EntType)="subfrmAccount")) ORDER BY tblEntity.EntID DESC;
ColumnCount: 4 (the other 3 columns feed other text fields)
ColumnWidths: 1;0;0;0
BoundColumn:1

I've tried flipping that number between 0 and 1, in combobox 2, and still end up with a blank combobox. Now that you mention it, I am a little confused about it. It defaults to 1, but I read that column count starts at 0? Or am I mixing something up?

[IncidentalProgrammer]

Your SQL statement/Requery recommendation worked! Thanks once again, June7!

[June7]

BoundColumn numbering starts with 1 but column Index starts with 0. Use Index to reference columns in expressions. Example: Me.cbxNameHere.Column(x)

I see 5 fields not 4 in the SQL.

Most people would hide the ID field and show the descriptive info: 0";1";0";0";0"

Most would also order by the descriptive value.

Programmatically setting the RowSource should automatically force a Requery of the combobox.

[IncidentalProgrammer]

Yeah, I know my structure is bizarre to anyone who's been doing this for a while. LOL

There's a fifth field that doesn't need to appear anywhere on this form; it just identifies the type of entity (in this case, Account), so I included it to filter my results down to just Accounts. The ID field is the Entity ID, which is what I want in the combobox. And tblPolAct does include EntID, in order to link the policy to the account that owns it.

Thanks for straightening me out on the column numbering, too!