Problem with complex query coding

I wish to use a form to show the results of a query between customer and
equipment serial no activated by a control button. Then control button runs
the query based on data entered in the form this I can do. But then getting
the query to open a variety of forms based on the result I can not do

Search form fields– [customer] and [serial]

If both match in underlying query then bring up a form/query that displays
data {form1}

If only customer match then bring up another form/query {form2}

If neither match then bring up {form3}

I have little knowledge of this and would also need to know where to place
any coding
Many thanks for any help

CP wrote:
I wish to use a form to show the results of a query between customer and
equipment serial no activated by a control button. Then control button runs
the query based on data entered in the form this I can do. But then getting
the query to open a variety of forms based on the result I can not do

Search form fields– [customer] and [serial]

If both match in underlying query then bring up a form/query that displays
data {form1}

If only customer match then bring up another form/query {form2}

If neither match then bring up {form3}

I have little knowledge of this and would also need to know where to place
any coding
Many thanks for any help


Hi CP,

You can do this by adding a bit of (VBA) code in the button's click
event. You will need a little knowledge of VBA but the code below may
help you to sing along.

To find the click event open your form in design mode, right click the
button, and look at Properties. In the Properties window select the
Event tab, click some place in "On Click" and then click the "..." that
appears at right.

How this works: When the click event fires you assign the query result
to a recordset object. You then examine what's in the recordset and take
appropriate action. This assumes only one record is returned by the query.

Watch out for line wrap.

Private Sub MyButton_Click()
Dim RS As DAO.Recordset
Dim Customer As Variant
Dim Serial As Variant
Set RS = DBEngine(0)(0).OpenRecordset("MyQuery")
' in the line below "Customer" refers to the field name in the query
Customer = RS.Fields("Customer")
Serial = RS.Fields("Serial")
' in the line below Me.Customer refers to the field name on the form
If Customer = Me.Customer.Value And Serial = Me.Serial.Value Then
DoCmd.OpenForm ("{form1}")
ElseIf Customer = Me.Customer.Value Then
DoCmd.OpenForm ("{form2}")
Else
DoCmd.OpenForm ("{form3}")
End If
Set RS = Nothing
End Sub

HTH
--
Smartin

Thank will give this a try

"Smartin" wrote:

CP wrote:
I wish to use a form to show the results of a query between customer and
equipment serial no activated by a control button. Then control button runs
the query based on data entered in the form this I can do. But then getting
the query to open a variety of forms based on the result I can not do

Search form fields– [customer] and [serial]

If both match in underlying query then bring up a form/query that displays
data {form1}

If only customer match then bring up another form/query {form2}

If neither match then bring up {form3}

I have little knowledge of this and would also need to know where to place
any coding
Many thanks for any help


Hi CP,

You can do this by adding a bit of (VBA) code in the button's click
event. You will need a little knowledge of VBA but the code below may
help you to sing along.

To find the click event open your form in design mode, right click the
button, and look at Properties. In the Properties window select the
Event tab, click some place in "On Click" and then click the "..." that
appears at right.

How this works: When the click event fires you assign the query result
to a recordset object. You then examine what's in the recordset and take
appropriate action. This assumes only one record is returned by the query.

Watch out for line wrap.

Private Sub MyButton_Click()
Dim RS As DAO.Recordset
Dim Customer As Variant
Dim Serial As Variant
Set RS = DBEngine(0)(0).OpenRecordset("MyQuery")
' in the line below "Customer" refers to the field name in the query
Customer = RS.Fields("Customer")
Serial = RS.Fields("Serial")
' in the line below Me.Customer refers to the field name on the form
If Customer = Me.Customer.Value And Serial = Me.Serial.Value Then
DoCmd.OpenForm ("{form1}")
ElseIf Customer = Me.Customer.Value Then
DoCmd.OpenForm ("{form2}")
Else
DoCmd.OpenForm ("{form3}")
End If
Set RS = Nothing
End Sub

HTH
--
Smartin