opening a form to a specific record

I

ifoundgoldbug

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
 
R

Rick Brandt

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
Click to expand...

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
 
D

Douglas J. Steele

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 & " ' "
 
I

ifoundgoldbug

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.

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 & " ' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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.

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 & "
' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
Click to expand...
 
I

ifoundgoldbug

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
#]? 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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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.

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 & "
' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
Click to expand...
 
I

ifoundgoldbug

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"


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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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


Douglas J. Steele wrote:
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 & "
' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
Click to expand...
 
I

ifoundgoldbug

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
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"


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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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


Douglas J. Steele wrote:
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 & "
' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Brandt

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
Click to expand...

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?
 
D

Douglas J. Steele

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.

--
Doug Steele, Microsoft Access MVP

(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"


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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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


Douglas J. Steele wrote:
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 &
"
' "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Criteria when opening form 1
If Statements 4
stLinkCriteria 3
stLinkCriteria Statement with multiple criteria 4
Opening form to specific record 3
Launching a Form to Another Form to Create a New Record 12
Code error when opening a form 2
Open with Link Criteria and Columns in Datasheet 1

Top