open form/subform at specific record

  • Follow


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]
0
Reply Utf 2/11/2010 6:24:01 PM

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]
0
Reply Utf 2/11/2010 9:16:01 PM

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]
0
Reply Utf 2/11/2010 9:38:09 PM

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]
0
Reply Utf 2/12/2010 12:33:01 AM

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

0
Reply KenSheridan 2/12/2010 6:08:40 PM

4 Replies
1548 Views

(page loaded in 0.103 seconds)

6/12/2014 8:27:39 PM


Reply: