I am using a continues form as a sub-form in access. In that form I am trying to use a combo box to display the description of an object bases on its primary key. The problem is that the combo box will only display the first record in the continues form the rest are left blank, also when the dropdown is pressed the list is empty. What is thank is happening is that the query that the combo box is based off of only runs for the first record and the rest of the records do not get looked up. I was wondering if there was a way around this problem, I would appreciate any help that you could offer.

What is the data structure behind the continuous form, and the combo box (where is that?) and the final presentation of data?


mmcdonal

Look it up at: http://wrox.books24x7.com

I am not really sure what you mean, but the form is based off of a table named TubContent. This table has a value called JigID stored in it; the JigID is a primary key in another table which stores the description field I am trying to use for the dropdown field in the combo box.

One catch is that I am trying to limit what is displayed in this combo box by what TubID was chosen in the previous field.

I have all of this working, but like I said it only works for the first record that is listed in the form. To check that is really worked in the GotFocus event I put a Me.Rrefresh, now when that field is clicked on it displays the right information including all the proper dropdown choices, but when a new field is chosen which ever field previously displayed the information goes blank.

I am not sure if this helped much, but I would appreciate any advice you could offer me.

Thanks

Unfortunately dependent combo boxes do not function properly in continuous or datasheet forms.

According to "Building Microsoft Applications" by Microsoft Press there is a way to do it. I've tried it with mixed results on a form cataloguing errors found on inspections. The main way is to get the source of the form to be a query containing references to the 2nd combo box.

The query below is the source to the form i mentioned. Error Group is the first combo and suberror is the dependant combo box.

The trick to getting it to work is unbound text boxes over the top of combo boxes. As a user enters one box the VBA automatically moves the focus to the combo box.

SELECT tblaradvisertrainingissues.artrainingissueid, tblaradvisertrainingissues.inspectid, tblaradvisertrainingissues.entrydate, tblaradvisertrainingissues.daterequested, tblaradvisertrainingissues.received, tblaradvisertrainingissues.datereceived, tblaradvisertrainingissues.itemtype, tblaradvisertrainingissues.requestedby, tblaradvisertrainingissues.requestedby, tblaradvisertrainingissues.suberror, tblerrortypes.errorgroup, tblerrortypes.suberrortype, tblaradvisertrainingissues.aradviserid, tblaradvisertrainingissues.arid, tblaradvisertrainingissues.factfinddate, tblaradvisertrainingissues.itemdetails
FROM tblerrortypes RIGHT JOIN tblaradvisertrainingissues ON tblerrortypes.suberrorid = tblaradvisertrainingissues.suberror
ORDER BY tblaradvisertrainingissues.daterequested;

Thanks for all the help

I did not get it to work like I first planed, but I got it to work. I ended up creating two different combo boxes. One which displays the description correctly for all records, and one that limits the choices based on which tub was previously chosen.

Then in the OnGot and OnLost Focus events I wrote code to move the focus and change the visibility of the combo boxes so that when the focus is set to them data can be entered correctly, but when they don’t have focus all the data is displayed correctly.