Open a form with record source from combo box

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?

Re: Open a form with record source from combo box

"Bob Sanderson" wrote in message
news:Xns99FC7CF77FDE6centroidincearthlink@69.28.186.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

Re: Open a form with record source from combo box

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