finding record from autonumber field

[blanche]

Howdy! I am trying to figure out how to pull up a record based on the
autonumber primary key. Mine is a database for tracking orders with each
order having an autonumber primary key (OrderID) and most having a
user-entered purchase order number (PurchaseOrderNumber). I have a search
form that allows me to search for records based on the vendor or item. On
this search form I have included a button that allows me to go directly to
the full version of the order (frmOrders). If I use my field
PurchaseOrderNumber and the following code the button works great:

Private Sub cmdGoToPO_Click()
On Error GoTo Err_cmdGoToPO_Click

Dim stDocName As String
Dim stLinkCriteria As String
If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

stDocName = "frmOrders"
stLinkCriteria = "[PurchaseOrderNumber]=" & "'" &
Me![PurchaseOrderNumber] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
MsgBox "No criteria", vbInformation, "Nothing to do."
End If

Exit_cmdGoToPO_Click:
Exit Sub

Err_cmdGoToPO_Click:
MsgBox Err.Description
Resume Exit_cmdGoToPO_Click

End Sub

However, not all of my orders have a purchase order number and I can't seem
to make this same type of event work to find records based on the OrderID
field (I've tried defining the stLinkCriteria as an integer, but that didn't
do the job). I'm not very experienced writing code. Any help would be
greatly appreciated. thanks

 

[John W. Vinson]

However, not all of my orders have a purchase order number and I can't seem
to make this same type of event work to find records based on the OrderID
field (I've tried defining the stLinkCriteria as an integer, but that didn't
do the job). I'm not very experienced writing code. Any help would be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values would
be concealed from user view. Would you have it exposed, or use a Combo Box on
the form allowing a record to be selected, or what?

 

[blanche]

Thanks for your prompt response.
On the search form various fields are filled in automatically. So if you
search by item (ex: dish) you get a list of every item with dish in the item
description that you can scroll through. On the same rowas the full item
description is the vendor name, order date, purchase order #, and order ID
number. At the end of each row I have the button to go to that particluar
record of frmOrders. Does that make sense? Sounds like my problem might be
solved by defining the LinkCriteria as an integer and leaving off the quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche

However, not all of my orders have a purchase order number and I can't seem
to make this same type of event work to find records based on the OrderID
field (I've tried defining the stLinkCriteria as an integer, but that didn't
do the job). I'm not very experienced writing code. Any help would be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values would
be concealed from user view. Would you have it exposed, or use a Combo Box on
the form allowing a record to be selected, or what?

 

[Douglas J. Steele]

No, that doesn't look right. The link criteria is a Where clause without the
word Where at the beginning: it has to be a string, and you have to have the
field name in there. You need to use John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your prompt response.
On the search form various fields are filled in automatically. So if you
search by item (ex: dish) you get a list of every item with dish in the
item
description that you can scroll through. On the same rowas the full item
description is the vendor name, order date, purchase order #, and order ID
number. At the end of each row I have the button to go to that particluar
record of frmOrders. Does that make sense? Sounds like my problem might
be
solved by defining the LinkCriteria as an integer and leaving off the
quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche

However, not all of my orders have a purchase order number and I can't
seem
to make this same type of event work to find records based on the
OrderID
field (I've tried defining the stLinkCriteria as an integer, but that
didn't
do the job). I'm not very experienced writing code. Any help would be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be
delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values
would
be concealed from user view. Would you have it exposed, or use a Combo
Box on
the form allowing a record to be selected, or what?

 

[blanche]

I tried the following but got an error message of Type Mismatch. I feel like
I'm making this harder than it should be. any help is greatly appreciated:

Private Sub cmdGoToPO_Click()
On Error GoTo Err_cmdGoToPO_Click

Dim stDocName As String
Dim LinkCriteria As Integer
If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

stDocName = "frmOrders"
LinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , LinkCriteria

Else
MsgBox "No criteria", vbInformation, "Nothing to do."
End If

Exit_cmdGoToPO_Click:
Exit Sub

Err_cmdGoToPO_Click:
MsgBox Err.Description
Resume Exit_cmdGoToPO_Click

--
blanche

No, that doesn't look right. The link criteria is a Where clause without the
word Where at the beginning: it has to be a string, and you have to have the
field name in there. You need to use John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your prompt response.
On the search form various fields are filled in automatically. So if you
search by item (ex: dish) you get a list of every item with dish in the
item
description that you can scroll through. On the same rowas the full item
description is the vendor name, order date, purchase order #, and order ID
number. At the end of each row I have the button to go to that particluar
record of frmOrders. Does that make sense? Sounds like my problem might
be
solved by defining the LinkCriteria as an integer and leaving off the
quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche

On Wed, 10 Jun 2009 16:09:01 -0700, blanche

However, not all of my orders have a purchase order number and I can't
seem
to make this same type of event work to find records based on the
OrderID
field (I've tried defining the stLinkCriteria as an integer, but that
didn't
do the job). I'm not very experienced writing code. Any help would be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be
delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values
would
be concealed from user view. Would you have it exposed, or use a Combo
Box on
the form allowing a record to be selected, or what?

 

[Douglas J. Steele]

You forgot to change the declaration of LinkCriteria to String.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried the following but got an error message of Type Mismatch. I feel
like
I'm making this harder than it should be. any help is greatly
appreciated:

Private Sub cmdGoToPO_Click()
On Error GoTo Err_cmdGoToPO_Click

Dim stDocName As String
Dim LinkCriteria As Integer
If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

stDocName = "frmOrders"
LinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , LinkCriteria

Else
MsgBox "No criteria", vbInformation, "Nothing to do."
End If

Exit_cmdGoToPO_Click:
Exit Sub

Err_cmdGoToPO_Click:
MsgBox Err.Description
Resume Exit_cmdGoToPO_Click

--
blanche

No, that doesn't look right. The link criteria is a Where clause without
the
word Where at the beginning: it has to be a string, and you have to have
the
field name in there. You need to use John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your prompt response.
On the search form various fields are filled in automatically. So if
you
search by item (ex: dish) you get a list of every item with dish in the
item
description that you can scroll through. On the same rowas the full
item
description is the vendor name, order date, purchase order #, and order
ID
number. At the end of each row I have the button to go to that
particluar
record of frmOrders. Does that make sense? Sounds like my problem
might
be
solved by defining the LinkCriteria as an integer and leaving off the
quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche


:

On Wed, 10 Jun 2009 16:09:01 -0700, blanche

However, not all of my orders have a purchase order number and I
can't
seem
to make this same type of event work to find records based on the
OrderID
field (I've tried defining the stLinkCriteria as an integer, but that
didn't
do the job). I'm not very experienced writing code. Any help would
be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be
delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values
would
be concealed from user view. Would you have it exposed, or use a Combo
Box on
the form allowing a record to be selected, or what?

 

[blanche]

Yeah finally that worked. My problem was this line:

If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

which was kicking out an error message when there was no PO Number. When I
changed that to check the txtOrderID field for a value everything worked
great. I think that was my problem all along. I should have seen that one.
Thanks to all for your help. Any suggestions for a good book on Visual Basic
for Access? I think I could use one. Cheers
--
blanche

You forgot to change the declaration of LinkCriteria to String.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried the following but got an error message of Type Mismatch. I feel
like
I'm making this harder than it should be. any help is greatly
appreciated:

Private Sub cmdGoToPO_Click()
On Error GoTo Err_cmdGoToPO_Click

Dim stDocName As String
Dim LinkCriteria As Integer
If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

stDocName = "frmOrders"
LinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , LinkCriteria

Else
MsgBox "No criteria", vbInformation, "Nothing to do."
End If

Exit_cmdGoToPO_Click:
Exit Sub

Err_cmdGoToPO_Click:
MsgBox Err.Description
Resume Exit_cmdGoToPO_Click

--
blanche

No, that doesn't look right. The link criteria is a Where clause without
the
word Where at the beginning: it has to be a string, and you have to have
the
field name in there. You need to use John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your prompt response.
On the search form various fields are filled in automatically. So if
you
search by item (ex: dish) you get a list of every item with dish in the
item
description that you can scroll through. On the same rowas the full
item
description is the vendor name, order date, purchase order #, and order
ID
number. At the end of each row I have the button to go to that
particluar
record of frmOrders. Does that make sense? Sounds like my problem
might
be
solved by defining the LinkCriteria as an integer and leaving off the
quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche


:

On Wed, 10 Jun 2009 16:09:01 -0700, blanche

However, not all of my orders have a purchase order number and I
can't
seem
to make this same type of event work to find records based on the
OrderID
field (I've tried defining the stLinkCriteria as an integer, but that
didn't
do the job). I'm not very experienced writing code. Any help would
be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be
delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber values
would
be concealed from user view. Would you have it exposed, or use a Combo
Box on
the form allowing a record to be selected, or what?

 

[Douglas J. Steele]

Jeff Conrad has a list that points to good lists of recommended books at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Books

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yeah finally that worked. My problem was this line:

If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

which was kicking out an error message when there was no PO Number. When
I
changed that to check the txtOrderID field for a value everything worked
great. I think that was my problem all along. I should have seen that
one.
Thanks to all for your help. Any suggestions for a good book on Visual
Basic
for Access? I think I could use one. Cheers
--
blanche

You forgot to change the declaration of LinkCriteria to String.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried the following but got an error message of Type Mismatch. I feel
like
I'm making this harder than it should be. any help is greatly
appreciated:

Private Sub cmdGoToPO_Click()
On Error GoTo Err_cmdGoToPO_Click

Dim stDocName As String
Dim LinkCriteria As Integer
If Not IsNull(Me.txtSearchPurchaseOrderNumber) Then

stDocName = "frmOrders"
LinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , LinkCriteria

Else
MsgBox "No criteria", vbInformation, "Nothing to do."
End If

Exit_cmdGoToPO_Click:
Exit Sub

Err_cmdGoToPO_Click:
MsgBox Err.Description
Resume Exit_cmdGoToPO_Click

--
blanche


:

No, that doesn't look right. The link criteria is a Where clause
without
the
word Where at the beginning: it has to be a string, and you have to
have
the
field name in there. You need to use John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your prompt response.
On the search form various fields are filled in automatically. So
if
you
search by item (ex: dish) you get a list of every item with dish in
the
item
description that you can scroll through. On the same rowas the full
item
description is the vendor name, order date, purchase order #, and
order
ID
number. At the end of each row I have the button to go to that
particluar
record of frmOrders. Does that make sense? Sounds like my problem
might
be
solved by defining the LinkCriteria as an integer and leaving off
the
quotes:

LinkCriteria = [OrderID] & Me![OrderID]

Does that look right?
--
blanche


:

On Wed, 10 Jun 2009 16:09:01 -0700, blanche

However, not all of my orders have a purchase order number and I
can't
seem
to make this same type of event work to find records based on the
OrderID
field (I've tried defining the stLinkCriteria as an integer, but
that
didn't
do the job). I'm not very experienced writing code. Any help
would
be
greatly appreciated. thanks

For one thing, a Number field (including Autonumber) would not be
delimited
with quotes:

stLinkCriteria = "[OrderID]=" & Me![OrderID]

But... how would the user know the OrderID? Normally autonumber
values
would
be concealed from user view. Would you have it exposed, or use a
Combo
Box on
the form allowing a record to be selected, or what?