open form/subform at specific record

[john]

I have a form (repair status) the has the same field (work order #) as
another form which is actually a subform(work order detail) to a main form
(Customer data). What I want to do is double click on the field (work order
#) and have it open the form (customer data)/(work order detail) at that
specific record.

I selected On Dbl Click to run a macro that opens the form where both field
match. That works but it pulls the customer data form and the work order
detail but it goes to the first work order detail associated to that customer
not the specific on i clicked on. Any idea what i'm doing wrong.

This is the condition i'm using in the macro.

[Work Order Number]=[Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

 

[KARL DEWEY]

Instead of condition try adding the following actions --
GoToControl --- [Work Order Number]
FindRecord ---- [Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

 

[john]

I tried this but the field i'm looking to match and open to is in the
subform. When I try this i get an error stating there is no field call Work
Order Number.

Instead of condition try adding the following actions --
GoToControl --- [Work Order Number]
FindRecord ---- [Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

--
Build a little, test a little.

I have a form (repair status) the has the same field (work order #) as
another form which is actually a subform(work order detail) to a main form
(Customer data). What I want to do is double click on the field (work order
#) and have it open the form (customer data)/(work order detail) at that
specific record.

I selected On Dbl Click to run a macro that opens the form where both field
match. That works but it pulls the customer data form and the work order
detail but it goes to the first work order detail associated to that customer
not the specific on i clicked on. Any idea what i'm doing wrong.

This is the condition i'm using in the macro.

[Work Order Number]=[Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

 

[KARL DEWEY]

Try telling it to go to subform the same way you reference the subform --
[Forms]![Frm Work Order Status]![Qry Open Work Orders].[Form]![Work Order
Number]

--
Build a little, test a little.

I tried this but the field i'm looking to match and open to is in the
subform. When I try this i get an error stating there is no field call Work
Order Number.

Instead of condition try adding the following actions --
GoToControl --- [Work Order Number]
FindRecord ---- [Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

--
Build a little, test a little.

I have a form (repair status) the has the same field (work order #) as
another form which is actually a subform(work order detail) to a main form
(Customer data). What I want to do is double click on the field (work order
#) and have it open the form (customer data)/(work order detail) at that
specific record.

I selected On Dbl Click to run a macro that opens the form where both field
match. That works but it pulls the customer data form and the work order
detail but it goes to the first work order detail associated to that customer
not the specific on i clicked on. Any idea what i'm doing wrong.

This is the condition i'm using in the macro.

[Work Order Number]=[Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]

 

[KenSheridan via AccessMonster.com]

I can't say how, or if, this could be done in a macro, but the following is
adapted from some code of my own which does a similar thing. You'd enter the
code as the DblClick event procedure of the control in your repair status
form:

Dim rst As Object
Dim frm As Form
Dim fsub As Form
Dim lngWorkOrderNumber As Long
Dim lngCustomerID As Long

lngWorkOrderNumber = Me.[Work Order Number]
lngCustomerID = Me.[CustomerID]

' open form at current work order's customer
DoCmd.OpenForm "Customer Data", _
WhereCondition:="[CustomerID] = " & lngCustomerID

Set frm = Forms("Customer Data")
Set fsub = frm.Controls("Work Order Detail").Form
Set rst = fsub.Recordset.Clone

' move to current work order in subform
With rst
.findFirst "[Work Order Number] = " & lngWorkOrderNumber
If Not .NoMatch Then
fsub.Bookmark = .Bookmark
End If
End With

I've had to make a few assumptions:

1. That the recordset underlying the form in whose module the code is
running includes a field CustomerID of number data type, i.e. it’s a foreign
key in the orders table which references the primary key of the customer data
table.

2. The primary key of the customer data table is also a number data type and
called CustomerID.

3. That the subform control on the customer data form, i.e. the control
which houses the subform, is named Work Order Detail.

4. That the Work Order Number field is a number data type.

If the code is in the module of a subform in the orders form, and the
CustomerID field is in the recordset of its parent form you'd need to amend
the code so it gets the value from the parent form like so:

lngCustomerID = Me.Parent.[CustomerID]

Ken Sheridan
Stafford, England

I have a form (repair status) the has the same field (work order #) as
another form which is actually a subform(work order detail) to a main form
(Customer data). What I want to do is double click on the field (work order
#) and have it open the form (customer data)/(work order detail) at that
specific record.

I selected On Dbl Click to run a macro that opens the form where both field
match. That works but it pulls the customer data form and the work order
detail but it goes to the first work order detail associated to that customer
not the specific on i clicked on. Any idea what i'm doing wrong.

This is the condition i'm using in the macro.

[Work Order Number]=[Forms]![Frm Work Order Status]![Qry Open Work
Orders].[Form]![Work Order Number]