Prev: subform question
Next: Average Age
From: john on 11 Feb 2010 13:24
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]
From: KARL DEWEY on 11 Feb 2010 16:16
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.


"john" wrote:

> 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]
From: john on 11 Feb 2010 16:38
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.

"KARL DEWEY" wrote:

> 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.
>
>
> "john" wrote:
>
> > 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]
From: KARL DEWEY on 11 Feb 2010 19:33
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.


"john" wrote:

> 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.
>
> "KARL DEWEY" wrote:
>
> > 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.
> >
> >
> > "john" wrote:
> >
> > > 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]
From: KenSheridan via AccessMonster.com on 12 Feb 2010 13:08
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

john wrote:
>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]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

 | 
Pages: 1
Prev: subform question
Next: Average Age