Results 1 to 12 of 12
  1. #1
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10

    Form combo boxes linked to queries with criteria from previous combo boxes are not working

    I have a form with combo boxes created via wizard. I want each combo box list to be limited based on the prior combo box criteria. (except the initial combo box)...

    cmbFIRST - from a table

    cmbSECOND
    - from query - field 1; [Forms]![frmDATASHEET]![cmbFIRST]

    cmbTHIRD
    - from query 2 - field 1; [Forms]![frmDATASHEET]![cmbFIRST] field 2; [Forms]![frmDATASHEET]![cmbSECOND]

    cmbFOURTH
    - from query 2 - field 1; [Forms]![frmDATASHEET]![cmbFIRST] field 2; [Forms]![frmDATASHEET]![cmbSECOND]; field 3; [Forms]![frmDATASHEET]![cmbTHIRD].

    When I open the queries I am prompted for the criteria... so it is looking for something.

    When I close the query and open the form the form shows blank combo lists, the criteria data is entered in the same row.



    None of the combo box's based on queries are working - simply showing blank data. The row source points to the correct field in the correct query; example: SELECT [qryONE].[fieldONE] FROM qryONE ORDER BY [fieldONE]

    Any thoughts on why the combo box's are not showing any data?

    Thank you!!

  2. #2
    eki einstein's Avatar
    eki einstein is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Location
    Bekasi - Jawa Barat- Indonesia
    Posts
    26
    can you upload the sample database?

  3. #3
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10
    I can do that... but first, I may have found something:

    This datasheet resides as a subform to a masterform (Masterform holds no functionality, but allows a header where the datasheet does not). The combo box's are working well if entered in the subform directly, excluding the masterform... When entered via the masterform I loose the combo box lists - do I need to update my criteria to include the master form too? If so, how would I do that... if not I am happy to create a sample.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Review http://www.datapigtechnologies.com/f...combobox2.html

    RowSource of cmbFirst would be like:

    SELECT field FROM tablename;

    The RowSource of cmbSecond would be like:

    SELECT field FROM tablename WHERE somefield=[cmbFirst];

    Need code to requery the dependent comboboxes.


    If you set the form as Continuous can arrange controls to look like Datasheet then the form header will display and main/sub form arrangement not required.
    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
    eki einstein's Avatar
    eki einstein is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Location
    Bekasi - Jawa Barat- Indonesia
    Posts
    26
    is quite bit confusing if not seeing the database it self, 1. check master form properties are the subform and master form are linked?, click the Sub form, check Properties (when master form open with the subform) >DATA>"LINK Master field" and "Link child fields" if there are links delete (as you say the master form not functional except for allow header), or why you just not make Continuous form and make it like the sub form view because you can add a header there.

  6. #6
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10
    GOT IT

    I did not include the main form in my query criteria...
    Corrected
    From: [Forms]![frmDATASHEET]![cmbFIRST]
    To: [Forms]![frmMain]![frmDATASHEET].[Form]![cmbDISTRICT]

    I can see how the information was abundunt thus confusing, in addition to the lack of necessary information - my apologies... Thank you for your replies, each reply gets me thinking in a fashion I have never thought about before - It's like learning a new language!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Actually, shouldn't need all that form referencing as long as the comboboxes are all on the same form. See the syntax in my post 4.
    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.

  8. #8
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10
    June7 - Although I have the combo boxes working now, I would like to try what you are suggesting... with that said, I will need help with the requery code.

    I did some reading, but none of those codes worked.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Be aware, dependent combo or list boxes in continuous or datasheet view do not work nice if the dependent comboboxes have lookup with alias.

    How are they working if you don't have requery code?

    I use the GotFocus event of the dependent combobox.

    Me.cmbSecond.Requery
    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
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10
    Changing the row source did not work alone, showing a blank list, I thought the Me.Requery might be the missing link to make it work - but it does not.

    Once entering the GotFocus - I get "If ME is a new macro or macro group, make sure you saved it"... Note: I have multiple queries.

    Also, I am not sure what a lookup alias is - I am assuming it is a cmbFIRST="First" type thing, which I do not have any codes of that kind. The only code I have as of now is an after update to allow the entered info to duplicate in the new row.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What I suggested is VBA code.

    In the OnGotFocus event, select [Event Procedure] from the dropdown. Click the ellipsis (...) to open VBA editor. Type code into the procedure.
    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.

  12. #12
    Ash is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    10
    June7 - Tried again this morning with a fresh mind - fixed a rowsource issue and entered the requery as VBA... Worked great!

    Thank you for the suggestion, very educating!

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

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. Lookups, queries, and combo boxes, oh my
    By MGustin in forum Access
    Replies: 2
    Last Post: 12-04-2013, 12:46 AM
  3. Why aren't my Combo Boxes working?
    By djclntn in forum Forms
    Replies: 2
    Last Post: 02-15-2013, 04:50 PM
  4. how to add Combo Boxes in Queries?
    By djclntn in forum Queries
    Replies: 1
    Last Post: 04-25-2012, 05:13 PM
  5. Working of Combo boxes in Form
    By suryaprasad in forum Forms
    Replies: 3
    Last Post: 06-29-2011, 11:54 AM

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