Open a form with record source from combo box

[Bob Sanderson]

I want to select a Job Number and Revision from a combo box (cboSelectBOM).
I then want to open a form (frmBoM) and display the record for that Job
Number and Revision

When I double-click on the job in the combo box, I am running the following
code:

JobNum = cboSelectBOM.Column(0)
Rev = cboSelectBOM.Column(1)
DoCmd.OpenForm "frmBoM"

This derives the job number and revision from the combo box. How then, do I
pass this information to the form so that only that record is selected?

[Stuart McCall]

"Bob Sanderson" <ne**@LUVSPAMbobsanderson.comwrote in message
news:Xn**********************************@69.28.18 6.158...

>I want to select a Job Number and Revision from a combo box (cboSelectBOM).
I then want to open a form (frmBoM) and display the record for that Job
Number and Revision

When I double-click on the job in the combo box, I am running the
following
code:

JobNum = cboSelectBOM.Column(0)
Rev = cboSelectBOM.Column(1)
DoCmd.OpenForm "frmBoM"

This derives the job number and revision from the combo box. How then, do
I
pass this information to the form so that only that record is selected?

Use the WhereCondition parameter in your OpenForm call:

strCriteria = "[Job Number]=" & JobNum & " And [Revision]=" & Rev
DoCmd.OpenForm "frmBoM", WhereCondition:=strCriteria

Note this assumes [Job Number] and [Revision] are your actual field names,
and that they sre both numeric. If one or both of them are type text then
surround the values with quotes, eg:

strCriteria = "[Job Number]=" & JobNum & " And [Revision]=" & Chr(34) & Rev
& Chr(34)
DoCmd.OpenForm "frmBoM", WhereCondition:=strCriteria

[fredg]

On Tue, 04 Dec 2007 17:16:58 GMT, Bob Sanderson wrote:

I want to select a Job Number and Revision from a combo box (cboSelectBOM).
I then want to open a form (frmBoM) and display the record for that Job
Number and Revision

When I double-click on the job in the combo box, I am running the following
code:

JobNum = cboSelectBOM.Column(0)
Rev = cboSelectBOM.Column(1)
DoCmd.OpenForm "frmBoM"

This derives the job number and revision from the combo box. How then, do I
pass this information to the form so that only that record is selected?

Depends. What is the datatype of the [JobNum] and [Rev] fields?
Number datatype?

Private Sub cboSelectBom_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmBoM" , , , "[JobNum] = " &
Me![cboSelectBom].Column(0) & " And [Rev] = " &
Me![cboSelectBOM].Column(1)
End Sub

Then again, doesn't each record have it's own unique prime key
identifier? It should. Then all you would need do is include that
identifier field in the combo box (as a hidden value, bound column)
and just refer to that field. You would still show, in the combo box,
the JobNum and Rev fields for proper identification of the record.

DoCmd.OpenForm "frmBoM" , , , "[RecordID] = " & Me![cboSelectBoM]

A simpler method might be to simply filter the current form and
display just that record.

Me.Filter = "RecordID] = " & Me.cboSelectBoM
Me.FilterOn = True
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail