Results 1 to 10 of 10
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Synchronized Comboboxes

    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?

    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.

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    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!

  3. #3
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    I added in the table that the first combo is drawing from, but no change in the results. I tried as:

    Code:
    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

    Code:
    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    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

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    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?

  8. #8
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Your SQL statement/Requery recommendation worked! Thanks once again, June7!

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-11-2014, 01:25 PM
  2. Replies: 6
    Last Post: 07-23-2012, 10:35 AM
  3. Synchronized Combo Box Difficulty
    By abc in forum Access
    Replies: 2
    Last Post: 01-03-2012, 03:18 PM
  4. Replies: 3
    Last Post: 12-17-2010, 06:31 AM
  5. Synchronized Combo Boxes
    By LesleaOH in forum Forms
    Replies: 34
    Last Post: 10-10-2009, 09:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums