Still Have SubForm Blues!

[sahaitu]

I have a form: frmMain and a subform fsubDevExac. There are three unbound combo boxes cboIssue, cboSubIssue and cboSubCategory on the main form that serve as lookup boxes on frmMain.

Their information comes from tblIssues, tblSubIssues, and tblSubCategories. There is code behind each of these boxes that filters the following one based on the previous selection.

The subform's record source is a query which includes tblCases, tblIssues, tblSubIssues and tblSubCategory. The tblCases has a primary key of txtCaseID. The other tables have a primary key of txtIssueID, txtSubIssueID and txtSubCategoryID respectively. Each of these keys are foreign keys in tblCases.

I have linked the subform using the Link Master/Child fields dialouge box. I have linked the forms by their IssueID, SubIssueID and SubCategoryID. When I make selections from the combo box, the subform is pulling cases that are not related to the selected issue, sub issue and sub category. Also, the subform is not refreshing the data after selections are made.

For example, there are in total 97 cases in the tblCases table. All of which have assigned Issue, SubIssue and SubCategory ID's. Though when I make selections using my lookup boxes, the same thirteen cases are returned everytime.

Please help, I truly need it....

Thanks in advance, Sahaitu....

 

[Remou]

Hi
Perhaps you could post the SQL statement for the main form and the subform?
You say "Each of these keys are foreign keys in tblCases." This seems a little unusual, at first glance. I suspect that the tblCases key should be the foreign key in each of the related tables, rather than this way around.

 

[WantoN]

I may have misunderstood you here, but have coded the comboboxes to requery the next combo box after each selection? And to requery the subform data too.

Antoni

 

[sahaitu]

Thanks for your replies. Below is the SQL for the Main Form:

Private Sub cboIssue_AfterUpdate()
'Update SubIssue after Issue combo box change.
Me.cboSubIssue = Null
Me.cboSubIssue.Requery
Me.cboSubIssue = Me.cboSubIssue.ItemData(0)

'Allow form to be opened in data entry mode.
Dim strMyData As String
Dim strMyFilter As String
strMyData = Me![cboIssue]
strMyFilter = "[idnIssueID] = '" & strMyData & "'"
strMyFilter = "[DocumentName] = '" & strMyData & "'"
Me.DataEntry = False
Me.Filter = strMyFilter
End Sub


Private Sub cboSubIssue_AfterUpdate()
'Update SubCategory after SubIssue combo box change.
Me.cboSubCategory = Null
Me.cboSubCategory.Requery
Me.cboSubCategory = Me.cboSubCategory.ItemData(0)
End Sub

Private Sub Form_Close()
'Reset the Data Entry and Filter Properties so that the form opens blank.
Me.Filter = vbNullString
Me.FilterOn = False
Me.DataEntry = True
End Sub

The only SQL for the SubForm is to print the current records, etc.

 

[Remou]

Hi sahaitu
When I said SQL I meant the data, not the code

Just a few thoughts.
You say:
strMyFilter = "[idnIssueID] = '" & strMyData & "'"
Then immediately afterwards you say:
strMyFilter = "[DocumentName] = '" & strMyData & "'"
So this line reads:
Me.Filter = "[DocumentName] = '" & Me![cboIssue] & "'"
Therefore, the first strFilter seems unnecessary.
This code is on the main form, so you are setting a filter on the main form when you say Me.Filter.
I think you wish to set a filter on the subform composed of data from all three comboboxes. Therefore, I think you need something like this:

strFilter = "txtIssueID = '" & Me.cboIssue & "' And txtSubIssueID ='" & Me.cboSubIssue  & "' And txtSubCategoryID ='" & Me.cboSubCategory & "'"
Me.fsubDevExac.Form.Filter = strFilter
Me.fsubDevExac.Form.FilterOn = True

With appropriate coding for nulls and so on. I hope I have understood your problem ...