Results 1 to 13 of 13
  1. #1
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151

    Combo box on continuous form

    I have a continuous form. It has a combo box which needs two values from the same record that is being updated on the form. It seems to be picking up the values from the first record on the form, but not updating the values when it moves to the next record. Please help with how to make it read the values from each record. Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Just like a textbox, a bound combobox can display only the value from the field it is bound to or that value's alias if the combobox RowSource is multi-column.

    Are you sure the combobox is bound to the correct field?
    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.

  3. #3
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    In more detail, this database is for a school. There are 2 types of classes: seminars and classes. There are 2 ways to take a class: audit or credit. The valid grades depend on both the class/seminar and the credit/audit. Valid grades are in a special lookup table that includes these values and the grade choices.

    Classes are a table (this is where the class/seminar field is). Individuals are a table. Jctindividuals&classes is a table that joins an individual (id#) with a class (id#). It includes whether the student is taking the class for credit or for audit. The continuous form uses a query (joining class, Jctindividuals&classes, and individuals) as its record source. The grade field shown on the continuous form has a record source of "grade" and a row source of "SELECT ValidGrades.ValidGrade, ValidGrades.CreditTypeDescr, ValidGrades.ClassType FROM ValidGrades WHERE (((ValidGrades.CreditTypeDescr)=[Forms]![GradesForm]![CreditTypeDescr]) AND ((ValidGrades.ClassType)=[Forms]![GradesForm]![Type]));"

    Is there a way in that SQL statement to get it to re-run the query when it moves to a new record? Or reference the section of the form? If this weren't a continuous form, I think it would be working correctly. Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So the combobox RowSource is a conditional SQL with lookup alias. This does not work nicely in Continuous or Datasheet form as you have discovered. This is a known issue with cascading (dependent) comboboxes and has been discussed in many, many threads. Solutions involve requerying the combobox, dynamically setting the combobox RowSource between conditional and non-conditional, and including the lookup table in the form RecordSource to bind a locked textbox to the alias field and set it on top of the combobox. Suggest you do a quick search in forum on the topic keywords.
    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
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    I've searched the forum and tried various things without success. My main problem is how to specify that the statement "SELECT (ValidGrades.CreditTypeDescr)=[Forms]![GradesForm]![CreditTypeDescr])" is referencing the current row on the continuous form, not the first row on the continuous form. I can't find anything that qualifies the row.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The code will pull value from whichever record has the focus. Try removing the form name qualifier. Maybe give controls a name different from the fields like tbxType or cbxType and then reference the control names.

    SELECT ValidGrade, CreditTypeDescr, ClassType FROM ValidGrades WHERE CreditTypeDescr=[cbxCreditTypeDescr] AND ClassType=[cbxType];
    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
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    I tried that. Now the combo box shows no values (is empty). From what you have said and what I have tried, I'm pretty sure the problem is that the focus is not being updated as I move down the form to other records. Is there a macro command that I can use to "force" this?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The technique works for me. The combobox will be empty for records where the combobox RowSource filter excludes their respective alias. That's the drawback to cascading combobox using alias.

    If you want to provide db for analsysis, 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.

  9. #9
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Are you putting this code on the "on click" event? If not, where? Thanks.

  10. #10
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    "The combobox will be empty for records where the combobox RowSource filter excludes their respective alias." I don't know what this means. Sorry, please explain.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I provided example of combobox RowSource conditional SQL statement. The combobox will only have records that meet the CreditTypeDescr and Type field parameters.

    If field combobox is bound to is based on a lookup alias, the alias will not show for those records where the alias is not in the combobox list.

    However, the example SQL does not appear to be using an alias, so I am not sure why your combobox is empty.

    At this point, I think the only way will be able to help is to examine the db.
    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
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You can use OnCurrent Event to trigger something when you move records. Not sure if it will work for what you need to but you can requery a combo box maybe using this. I'm having hard time understanding what you have and what the issue is. Maybe give some screen shots of the continuous form and the combo box sources?

    Another thought, create 2 unbound fields in the form header or top of form and in the OnCurrent event of form, move the value of the 2 fields in the record needed for the query to those 2 temp fields, then use those temp fields in your query.

  13. #13
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    I finally got this working. I have a combo box with 3 values from the valid grades table
    SELECT ValidGrades.Grade FROM ValidGrades WHERE (((ValidGrades.CreditTypeDescr)=[Forms]![IndividualsinaClass]![crheader]) AND ((ValidGrades.ClassType)=[Forms]![IndividualsinaClass]![ classtype]));


    I only show the grade from the combo box. On the on-click event, I requery the grade - this was the main thing I was missing before. Thanks to all.

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

Similar Threads

  1. Filter continuous form by combo box
    By revolution9540 in forum Forms
    Replies: 10
    Last Post: 08-04-2015, 02:16 PM
  2. Filter a continuous form using a combo box
    By Chky071 in forum Access
    Replies: 5
    Last Post: 05-04-2015, 08:06 AM
  3. Replies: 1
    Last Post: 01-07-2014, 09:33 AM
  4. Replies: 1
    Last Post: 11-24-2011, 07:45 AM
  5. Cascading Combo box in Continuous Form
    By neo651 in forum Access
    Replies: 1
    Last Post: 09-15-2011, 02:34 AM

Tags for this Thread

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