Combo Box limiting another combo box list - selection issue

[pmp1]

I am running Access 2007 on Vista Business.
I have used faq702-4289 to get one combo box controling (filtering) the list on a second combo box and it works very well.
However, what doesn't work is:
(1) as I cycle through records the second combo box does not display the record from the database unless it is the first of the filtered list.
(2)I can not select (and keep selected) any item in the second combo box except the first item.
I hope I have made my issues (which I'm sure are directly related) clear and that someone may be able to assist.
Regards
Peter

 

[Remou]

Have you added code to the Current event to requery the dependant combobox?

http://lessthandot.com

 

[pmp1]

Remou,
Sorry for the delay in replying. I'm not sure what you mean as the dependant combobox does not have a Current event. I added requery to the form's Current event with no effect. I then spent another couple of hours searching for a solution only to find many solutions which were different and I only became more confused.
Here is all that I have done to date:
Tables
Inwards - links to FileCategory and FileSubject
FileCategory
FileSubject
Form
Inwards - includes:
cboFileCategory - first combo box
cboFileSubject - dependant combo box
cboFileCategory
Row Source -
SELECT FileCategory.FileCategoryID, FileCategory.FileCategory
FROM FileCategory
ORDER BY FileCategory.[FileCategory];

After Update Event -
Private Sub cboFileCategory_AfterUpdate()
cboFileSubject.Requery
End Sub
cboFileSubject
Row Source -
SELECT FileSubject.[_FileCategory], FileSubject.[File Subject]
FROM FileSubject
WHERE (((FileSubject.[_FileCategory])=[Forms]![Inwards]![cboFileCategory]))
ORDER BY FileSubject.[_FileCategory], FileSubject.[File Subject];

Got Focus Event -
Private Sub cboFileSubject_GotFocus()
If Len(Trim(Nz(cboFileCategory, "") & "")) = 0 Then
MsgBox "Please Specifiy a File Category first"
cboFileCategory.SetFocus
Else
cboFileSubject.Requery
End If
End Sub
After Update Event -
Private Sub cboFileSubject_AfterUpdate()
cboFileSubject.Requery
End Sub

I hope this additional info assists you to help me. Happy to provide any other relevant information you may need.

For what it's worth my classification as programmer should read "poor" programmer.

Thanks for helping so far.

Peter

 

[PHV]

cboFileCategory
Row Source -
[!]SELECT FileCategoryID, FileCategory
FROM FileCategory
ORDER BY FileCategory;[/!]

After Update Event -
[!]Private Sub cboFileCategory_AfterUpdate()
cboFileSubject.RowSource = "SELECT _FileCategory, [File Subject] FROM FileSubject WHERE _FileCategory=" _
& Me!cboFileCategory & " ORDER BY 1,2"
End Sub[/!]

cboFileSubject
Row Source -
[!]SELECT _FileCategory, [File Subject]
FROM FileSubject
WHERE _FileCategory=[Forms]![Inwards]![cboFileCategory]
ORDER BY _FileCategory, [File Subject];[/!]

Got Focus Event -
[!]Private Sub cboFileSubject_GotFocus()
If Trim(cboFileCategory & "") = "" Then
MsgBox "Please Specifiy a File Category first"
cboFileCategory.SetFocus
End If
End Sub[/!]
After Update Event -
nothing necessary here.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pmp1]

PHV

Thanks for the advice provided and sorry to take so long to reply. We currently struggling with Cyclone Hamish off our East Coast.
I have tried your solution without success and would appreciate you explaining some of the details as your solution is somewhat different to my original attemp as documented FAQ702-4289:
I have also changed the names the tables to be preceeded with tbl etc. as well as changing the name of my lookup fields having a suffix of ID and predeed with an underscore to prevent me being confused when writing SQL or VB, a practice I shall endevour to continue.
However, I would like to understand the syntax and code structure you have used in the After Update Event as it is here I believe my lies. Understanding why and what you have done will enable me to fix an error I may have.
Regards
Peter

 

[pmp1]

Hi!
I believe I may be trying to do something that can not be done. On my form where I have the two combo boxes, the first being file category which is simply looking at the tblFileCategory and the second being the dependant list, I am trying to use the form to view a registered document (tblInwards) which includes a lookup to tblFileSubject (I also had a lookup to tblFileCategory which caused a "loop" as tblFileSubject has a lookup to tblFileCategory).
I am trying use the same form to view an inwards correspondence record as well add and/or change details of a record in tblInwards.
Could this be causing my problem.
Regards
Peter

 

[pmp1]

I have made several changes and still do not understand how to get my form to work correctly.
Changes made include:
1. Tables
tblInwards link to tblFileSubject no longer linked to tblFileCategory
tblFileSubject link to tblFileCategory
2. deleted all space characters in field names
3. fields that are pointers to another table are prefixed with an underscore and have 'ID' as a suffix.
The above changes at 2 & 3 where to avoid me being confused

I am trying to do the following on one form:
1. Display records describing correspondence received;
2. Edit records that may have been recorded incorrectly;
3. Add a record describing new correspondence received;
Some of the fields are text boxes and others are combo boxes.

I am attempting to achieve the following:
1. Display tblFileSubject.SubjectDescription in cboFileSubject based upon tblInwards._FileSubjectID;
2. Limit cboFileSubject to those file subjects that match file category based on the current record's _FileSubjectID liked from tblInwards through tblFileSubect to tblFileCategory;
3. Display file category in cboFileCategory based on the record's file category which is based on the records _FileSubjectID.

I look forward to hearing from anyone who can help.

Regards

Peter