Another Dbl Click Conundrum

[JohnLute]

I'm trying to code a combo box to open a form. Here's my code:

Private Sub cbSupplierContact_DblClick(Cancel As Integer)
If IsNull(Me!cbSupplierContact) Then
' no supplier contact selected; show all ...
DoCmd.OpenForm "frmPKSuppliers", _
WhereCondition:="txtName='" & Me!cbSupplierContact.Column(5) & "'"
Else
' show selected supplier contact ...
DoCmd.OpenForm "frmPKSuppliers", _
WhereCondition:="txtName='" & Me!cbSupplierContact.Column(5) & "'"
End If
End Sub

RowSource:
SELECT tblSupplierContacts.FirstName, tblSupplierContacts.LastName, tblSupplierContacts.Title, tblSupplierContacts.WorkPhone, tblSupplierContacts.numContactID, tblSupplierNames.txtName FROM tblSupplierNames INNER JOIN tblSupplierContacts ON tblSupplierNames.txtName=tblSupplierContacts.txtName ORDER BY tblSupplierContacts.FirstName;

How can I change this so that frmPKSuppliers opens and also filters for the selected tblSupplierContacts.numContactID in column 5?

THANKS!!!

 

[Douglas J. Steele]

You don't indicate what's wrong with your code (i.e. do you get an error
when you run it?)

One thing that's wrong is that you're setting the WhereCondition even when
cbSupplierContact is null. That's going to produce a WhereCondition of
"txtName = ''". Remove that part of the OpenForm.

Now, is numContactID numeric or text? The code you have assumes it's text.
If it's in fact numeric, change it to WhereCondition:="txtName=" &
Me!cbSupplierContact.Column(5)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to code a combo box to open a form. Here's my code:

Private Sub cbSupplierContact_DblClick(Cancel As Integer)
If IsNull(Me!cbSupplierContact) Then
' no supplier contact selected; show all ...
DoCmd.OpenForm "frmPKSuppliers", _
WhereCondition:="txtName='" & Me!cbSupplierContact.Column(5) & "'"
Else
' show selected supplier contact ...
DoCmd.OpenForm "frmPKSuppliers", _
WhereCondition:="txtName='" & Me!cbSupplierContact.Column(5) & "'"
End If
End Sub

RowSource:
SELECT tblSupplierContacts.FirstName, tblSupplierContacts.LastName,

tblSupplierContacts.Title, tblSupplierContacts.WorkPhone,
tblSupplierContacts.numContactID, tblSupplierNames.txtName FROM
tblSupplierNames INNER JOIN tblSupplierContacts ON
tblSupplierNames.txtName=tblSupplierContacts.txtName ORDER BY
tblSupplierContacts.FirstName;

How can I change this so that frmPKSuppliers opens and also filters for

the selected tblSupplierContacts.numContactID in column 5?

 

[JohnLute]

Thanks, Doug!

The code works fine - it opens the correct form. I need it to filter the form for the numContactID in column 5 of the cbSupplierContact.

As it is now, the form opens (frmPKSuppliers) but the contact (sfrmSupplierContacts) opens NOT to the selected contact but to the first contact record. I'm trying to see how the code can be altered to filter to the selected numContactID.

 

[Douglas J. Steele]

If you've passing the correct information to the WhereCondition, only the
record(s) with the selected numContactID should appear in frmPKSuppliers
with the syntax you're using. How are frmPKSuppliers and the subform
sfrmSupplierContacts linked?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug!

The code works fine - it opens the correct form. I need it to filter the

form for the numContactID in column 5 of the cbSupplierContact.

As it is now, the form opens (frmPKSuppliers) but the contact

(sfrmSupplierContacts) opens NOT to the selected contact but to the first
contact record. I'm trying to see how the code can be altered to filter to
the selected numContactID.

 

[Steve Schapel]

John,

The column numbering begins at 0 so the 5th column is column 4. But I
am still a bit unclear about what you're asking. Is this it?...
WhereCondition:="numContactID=" & Me.cbSupplierContact.Column(4)