Combo box based on another combo box

A

acccessaccess2003

I have two combo boxes, cboCategory and cboLookup. With the selection of a
category in cboCategory will filter the items in cboLookup. I made reference
to http://allenbrowne.com/ser-27.html Option 2: Pop up a form using another
event such as a DblClick event.
My problem is when I select a particular category in cboCategory and it
happens that there's no items in cboLookup for that category, upon double
clicking will bring me to the form that I want. However, if that category has
items in cboLookup and upon double clicking, the form that is supposed to be
opened, does not appear. I tried using a text box to debug whether upon
double clicking the item present in that category, will the executor enter
the subroutine DblClick event but apparently, it doesn't.

The Row Source of cboCategory is as such:
SELECT tblCategory.[Category ID], tblCategory.[Category Name] FROM
tblCategory ORDER BY [Category Name];

The Row Source of cboLookup is as such:
SELECT tblGasInfo.[Component ID], tblGasInfo.[Gas System], tblGasInfo.Size,
tblGasInfo.Grade, tblGasInfo.Manufacturer FROM tblGasInfo WHERE
(((tblGasInfo.[Category ID])=Forms!frmCombo!cboCategory)) ORDER BY [Component
ID];

Component ID is of text type and the subroutine is as follows:
Private Sub cboLookup_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strItemSelected As String
Const strcTargetForm = "frmGasInfo"

' Set up to search for the current Component ID.
If Not IsNull(Me.cboLookup) Then
strItemSelected = "[Component ID] = '" & Me.cboLookup.Column(0) & "'"
Me.txtDebug.Value = strItemSelected
End If

'Open the editing form.
If Not CurrentProject.AllForms(strcTargetForm).IsLoaded Then
DoCmd.OpenForm strcTargetForm
End If
With Forms(strcTargetForm)
' Save any edits in progress, and make it the active form.
If .Dirty Then .Dirty = False
.SetFocus

If strItemSelected <> vbNullString Then

' Find the record that matches the combo.
Set rs = .RecordsetClone
rs.FindFirst strItemSelected
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
End If
Else
' Combo was blank, so go to a new record.
RunCommand acCmdRecordsGoToNew
End If
End With
Set rs = Nothing
End Sub

I did the exact same thing for Form_AfterUpdate.
Can anyone tell me what's wrong? Any help is appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top