opening a form to a specific record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ifoundgoldbug@gmail.com - 31 Jul 2006 19:06 GMT

Private Sub Work_Order___DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "work order"

stLinkCriteria = [Form_action item].Work_Order__.Value
DoCmd.OpenForm stDocName, , , "[work order #] = stLinkCriteria"
End Sub

I have gotten this to quasi work. when it hit the open form it pops up
a
stlinkcriteria window asking for parameters and if you put in the work
order number it will work. but for some unknown reason it will not hold
the W/O number in stlinkcriteria. any ideas?

thank you for your time

Gold Bug

Reply to this Message

Rick Brandt - 31 Jul 2006 19:13 GMT

> Private Sub Work_Order___DblClick(Cancel As Integer)
> Dim stDocName As String
[quoted text clipped - 15 lines]
>
> Gold Bug

Private Sub Work_Order___DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "work order"

stLinkCriteria = "[work order #] = " & Forms![Form_action
item]!Work_Order
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 31 Jul 2006 19:15 GMT

You need to use the value of strLinkCriteria, not its name. That means
putting it outside of the quotes.

If [work order #] is numeric, you can use:

DoCmd.OpenForm stDocName, , , "[work order #] = " & stLinkCriteria

If it's text, you need to include quotes:

DoCmd.OpenForm stDocName, , , "[work order #] = " & Chr$(34) &
stLinkCriteria & Chr$(34)

or

DoCmd.OpenForm stDocName, , , "[work order #] = '" & stLinkCriteria & "'"

Exagerated for clarity, the 2nd one is:

DoCmd.OpenForm stDocName, , , "[work order #] = ' " & stLinkCriteria & " ' "

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Private Sub Work_Order___DblClick(Cancel As Integer)
> Dim stDocName As String
[quoted text clipped - 15 lines]
>
> Gold Bug

Reply to this Message

ifoundgoldbug@gmail.com - 31 Jul 2006 20:08 GMT

Doug

I have tried you code already and it has a problem it runs without
error but it doesn't actually open up the proper record it opens a
blank one.

Rick

when i tried your code snipet

Private Sub Work_Order___DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "work order"

stLinkCriteria = "[work order #] = " & Forms![action item]![Work
Order #]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

i get a run time error 2501 "the OpenForm action was canceled"

thank you for your time gentlemen this problem is really annoying me.

Gold Bug

> You need to use the value of strLinkCriteria, not its name. That means
> putting it outside of the quotes.
[quoted text clipped - 40 lines]
> >
> > Gold Bug

Reply to this Message

Douglas J. Steele - 31 Jul 2006 20:16 GMT

Surprisingly enough, an error of "the OpenForm action was canceled" usually
means that you mistyped something. You sure the field name is [work order
#]? Even if it is, try removing the special character (#). Get rid of the
blanks while you're at it too.

What form is this code from? If it's in form [action item], all you need is
Me![Work Order #].

If it's still not working, check exactly what's in stLinkCriteria. After you
assign the value to the variable, but before you open the form, put either

MsgBox stLinkCriteria

or

Debug.Print stLinkCriteria

so that you can see what's being assigned to the variable. Assuming you use
Debug.Print (so that it's written to the Immediate Window), copy and paste
the result back here.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug
>
[quoted text clipped - 68 lines]
>> >
>> > Gold Bug

Reply to this Message

ifoundgoldbug@gmail.com - 31 Jul 2006 20:35 GMT

the field and form names are correct and what ends up in the vaiable is
"[Work Order]![Work Order #] = 61105"

> Surprisingly enough, an error of "the OpenForm action was canceled" usually
> means that you mistyped something. You sure the field name is [work order
[quoted text clipped - 94 lines]
> >> >
> >> > Gold Bug

Reply to this Message

ifoundgoldbug@gmail.com - 31 Jul 2006 20:48 GMT

i added forms! to [Work Order]![Work Order #] = 61105 and now i am back
to the point where i was before it will run error free but it will
bring up a blank form instead of the proper one. i am very perplexed.

as always thanks for your time

Gold Bug

> the field and form names are correct and what ends up in the vaiable is
> "[Work Order]![Work Order #] = 61105"
[quoted text clipped - 97 lines]
> > >> >
> > >> > Gold Bug

Reply to this Message

ifoundgoldbug@gmail.com - 31 Jul 2006 21:59 GMT

ok i have an interesting problem

Private Sub Work_Order___DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim test As Double

stDocName = "work order"

stLinkCriteria = Me![work order #]
DoCmd.OpenForm stDocName, , , "[work order #] = stLinkCriteria"
End Sub

stlinkCriteria is set to 65421 but when it hits the docmd line it
brings up a dialog box asking for the value stlinkcriteria then i enter
in any WO number (in this case 61105) and it works it brings up the
value that I entered into the dialog box but when i look up the value
of stlinkcriteria in the instant window it is still 65421.

any help would be appreciated
thanks for your time

Gold Bug

> i added forms! to [Work Order]![Work Order #] = 61105 and now i am back
> to the point where i was before it will run error free but it will
[quoted text clipped - 104 lines]
> > > >> >
> > > >> > Gold Bug

Reply to this Message

Rick Brandt - 31 Jul 2006 22:29 GMT

> ok i have an interesting problem
>
[quoted text clipped - 17 lines]
> any help would be appreciated
> thanks for your time

The variable name goes outside the quotes. The way you have it the filter
is lterally looking for a record where the work order number is equal to the
word "stLinkCriteria".

The command button wizard would write this code for you. Have you tried
that?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 31 Jul 2006 23:33 GMT

The code you've posted couldn't possibly generate that value for
stLinkCriteria, since you don't have [Work Order]! in the code anywhere.

What's the RecordSource for the Work Order form you're trying to open?
That's what needs to have [Work Order #] as a field in it.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> the field and form names are correct and what ends up in the vaiable is
> "[Work Order]![Work Order #] = 61105"
[quoted text clipped - 108 lines]
>> >> >
>> >> > Gold Bug

Reply to this Message