Problems with the possible values of one combo box depending on the value of another

[cheese9799]

SOLVED



I have 2 combo boxes. The first one selects a category, and then the second should then display all the possible values from that category. And it does that. The following code is used:



In the AfterUpdate of the 1st combo box:

Private Sub cboCertificateType_AfterUpdate()
Me.cboJobType = Null
Me.cboJobType.Requery
End Sub

(where the 1st combo box is called 'cboCertificateType' and the 2nd combo box is called 'cboJobType')


The query for the 2nd combo box is:

SELECT tblJob.idsJobID, tblJob.chrJobType
FROM tblJob
WHERE (((tblJob.lngCertificateID)=[Forms]![frmBooking]![cboCertificateType]));

(and you'll just have to trust me that those table, field and form names are correct)



Now this works partially. I do get the option to select a category, and then I do then get a selection of all of the available value in that category. However, when pressing the left/right arrow to browse through records, the 2nd combo box is empty whenever the 1st combo box is not equal to the last one I selected.

It's quite hard to explain so here's an example: I select the option 'Installation', which provides a list of jobs that come under 'installation'. I select one of them. Now I press the back arrow to move back to the previous record, and because the category of that record was 'minor', and not 'installation', the value of the second combo box is blank.

The actual records saved in the table don't disappear, but it just appears blank when browsing through the records through the form.

The following solution failed:

Private Sub Form_Current()
Me.cboJobType = DLookup("chrJobType", "tblJob")
End Sub

I get 'The value you entered isn't valid for this field.'

[cheese9799]

Solved - just had to add another Me.cboJobType.Requery in the form OnCurrent event

[orange]

There are free video tutorials here.
This one has 2 combo boxes. The subject you are dealing with is Cascading Combo boxes.

http://www.datapigtechnologies.com/f...combobox2.html


Also nothing is = Null

I think you're looking for IsNull() function

see http://allenbrowne.com/casu-12.html