Open current record in form, from list view subform

[Mick]

Here's what I've got, a 'call management' type database which has a
main form with the users information in it, then a sub form in list
view of all their service calls. We want to be able to double click on
the call number (ID ) and have it open that record in a separate form
in form view. I setup the 'on doubleclick' action to run a macro which
opens the form with the criteria:

[WorkorderID]=[Forms]![WorkordersbyCustomerSubform]![WorkorderID]

So that it will open the new form to the work order that I clicked on.
I used this in a previous database and it works well, but in that DB I
have the 1st form in form view rather than data sheet view. It seems
like with the new DB since it is in data sheet view it needs some code
to specify that on double click it should open the other form applying
the criteria above but also specify that it should be the workorderid
of the current record, but I can't find a way to specify that. I tested
by switching the subform into form view and it worked as expected.

Does any of this make sense? Anybody got a solution? Thanks!

Mick

 

[tina]

the problem is probably your syntax. if the macro is running from within the
subform, you should be able to just refer to the WorkorderID field in the
subform's RecordSource, rather than using the full syntax.

if that doesn't work, then try the full syntax, as

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]![NameofControlWithinT
heSubform]

the above goes all on one line, of course. and make sure that
[NameOfSubformControl] is the name of the "container" control within the
main form that "holds" the subform - NOT the name of the subform object as
you see it in the database window. the two names are sometimes the same, but
sometimes not.

hth

 

[Mick]

Thanks, I corrected the macro with the criteria:

[Forms]![Workorders by Customer]![Workorders by Customer
Subform].[Form]![WorkorderID]

The problem now is if I envoke this macro, I get the form with the
latest work order visible, and I then can record through all recors
that they have. What I'm looking to do is run the macro and have it
open the currently selected record. The way it is, if I wanted to open
the third workorder in the subform, I'd need to run the macro and then
record through to the third record, I want to open it and have JUST the
selected record available in the form view.

I think I've got the 'base' of the macro correct at this point, but
more than ever, it sems like I need some way of selectign the current
record

Thansk again!!

Mick

the problem is probably your syntax. if the macro is running from within the
subform, you should be able to just refer to the WorkorderID field in the
subform's RecordSource, rather than using the full syntax.

if that doesn't work, then try the full syntax, as

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]![NameofControlWithinT
heSubform]

the above goes all on one line, of course. and make sure that
[NameOfSubformControl] is the name of the "container" control within the
main form that "holds" the subform - NOT the name of the subform object as
you see it in the database window. the two names are sometimes the same, but
sometimes not.

hth

Here's what I've got, a 'call management' type database which has a
main form with the users information in it, then a sub form in list
view of all their service calls. We want to be able to double click on
the call number (ID ) and have it open that record in a separate form
in form view. I setup the 'on doubleclick' action to run a macro which
opens the form with the criteria:

[WorkorderID]=[Forms]![WorkordersbyCustomerSubform]![WorkorderID]

So that it will open the new form to the work order that I clicked on.
I used this in a previous database and it works well, but in that DB I
have the 1st form in form view rather than data sheet view. It seems
like with the new DB since it is in data sheet view it needs some code
to specify that on double click it should open the other form applying
the criteria above but also specify that it should be the workorderid
of the current record, but I can't find a way to specify that. I tested
by switching the subform into form view and it worked as expected.

Does any of this make sense? Anybody got a solution? Thanks!

Mick

 

[tina]

well, when you said "criteria", i assumed you were using the WHERE clause
argument in the macro. if that's what you're using, but that's not what
you're getting, then the macro isn't working as you intended.

personally, i've always had problems using subform references in macros -
why, i don't know. but i find it easier to just use a DoCmd.OpenForm action
in VBA instead, and use the WHERE clause argument in the code. run the code
from the subform's module, and you can just refer to the control on the
subform, without having to use a full reference, as

DoCmd.OpenForm "FormName", , , _
"WorkorderID = " & Me!WorkorderID

the above assumes that WorkorderID is a number data type. if it's a text
data type, then the syntax would be

DoCmd.OpenForm "FormName", , , _
"WorkorderID = '" & Me!WorkorderID & "'"

if you don't know how to create a VBA procedure, then go to
http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link for illustrated step-by-step instructions.

hth

Thanks, I corrected the macro with the criteria:

[Forms]![Workorders by Customer]![Workorders by Customer
Subform].[Form]![WorkorderID]

The problem now is if I envoke this macro, I get the form with the
latest work order visible, and I then can record through all recors
that they have. What I'm looking to do is run the macro and have it
open the currently selected record. The way it is, if I wanted to open
the third workorder in the subform, I'd need to run the macro and then
record through to the third record, I want to open it and have JUST the
selected record available in the form view.

I think I've got the 'base' of the macro correct at this point, but
more than ever, it sems like I need some way of selectign the current
record

Thansk again!!

Mick

the problem is probably your syntax. if the macro is running from within the
subform, you should be able to just refer to the WorkorderID field in the
subform's RecordSource, rather than using the full syntax.

if that doesn't work, then try the full syntax, as

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]![NameofControlWithinT
heSubform]

the above goes all on one line, of course. and make sure that
[NameOfSubformControl] is the name of the "container" control within the
main form that "holds" the subform - NOT the name of the subform object as
you see it in the database window. the two names are sometimes the same, but
sometimes not.

hth

Here's what I've got, a 'call management' type database which has a
main form with the users information in it, then a sub form in list
view of all their service calls. We want to be able to double click on
the call number (ID ) and have it open that record in a separate form
in form view. I setup the 'on doubleclick' action to run a macro which
opens the form with the criteria:

[WorkorderID]=[Forms]![WorkordersbyCustomerSubform]![WorkorderID]

So that it will open the new form to the work order that I clicked on.
I used this in a previous database and it works well, but in that DB I
have the 1st form in form view rather than data sheet view. It seems
like with the new DB since it is in data sheet view it needs some code
to specify that on double click it should open the other form applying
the criteria above but also specify that it should be the workorderid
of the current record, but I can't find a way to specify that. I tested
by switching the subform into form view and it worked as expected.

Does any of this make sense? Anybody got a solution? Thanks!

Mick