I'm SO close...stLinkCriteria

[Guest]

Hi there! Using A02 on XP. Thought I had it. Can't get it to work. Probably
something SO simple. I have a text field on my form [PolNum] and want to open
a query of records related to [PolNum] (I have the field in my query also).
The quotes get me every time!

Private Sub Command41_Click()

On Error GoTo Err_Command41_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "qEnrollFormsByPlan#"
stLinkCriteria = "PolNum=""" & Me![PolNum] & """"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly


Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub

Compile on above runs fine but when I click the button, I get ALL the
records. Ugh!

Also tried this line instead of all that above:

DoCmd.OpenQuery "qEnrollFormsByPlan#", acViewPreview, acReadOnly, PolNum
= Me.PolNum

On Compile, I get this error - Compile Error: wrong number of arguments or
invalid property assignment. So I can't even test this one.

Sorry to bother you guys with this, I should know these better by now but
setting the criteria quotes kills me. Any help or advice would be greatly
appreciated. Thanks in advance for your time.

 

[Douglas J Steele]

You can't use a criteria with a query with the OpenQuery method.

You either need to refresh the SQL associated with the query, or make the
query a parameter query.

 

[Roger Carlson]

If PolNum is a TEXT field, then your stLinkCriteria is correct.

If it is actually a number, it should be:
stLinkCriteria = "PolNum=" & Me![PolNum]

However, your problem may be the query name. The # symbol at the end may be
confusing Access. It is always best to use ONLY letters, numbers, and the
underscore character when naming Access objects. Try renaming the query
before doing anything else.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks for the info. That explains the error message on my second set. I'll
create a form for the query and then I should be okay. I really appreciate
your help.
--
Bonnie

You can't use a criteria with a query with the OpenQuery method.

You either need to refresh the SQL associated with the query, or make the
query a parameter query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there! Using A02 on XP. Thought I had it. Can't get it to work. Probably
something SO simple. I have a text field on my form [PolNum] and want to open
a query of records related to [PolNum] (I have the field in my query also).
The quotes get me every time!

Private Sub Command41_Click()

On Error GoTo Err_Command41_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "qEnrollFormsByPlan#"
stLinkCriteria = "PolNum=""" & Me![PolNum] & """"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly


Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub

Compile on above runs fine but when I click the button, I get ALL the
records. Ugh!

Also tried this line instead of all that above:

DoCmd.OpenQuery "qEnrollFormsByPlan#", acViewPreview, acReadOnly, PolNum
= Me.PolNum

On Compile, I get this error - Compile Error: wrong number of arguments or
invalid property assignment. So I can't even test this one.

Sorry to bother you guys with this, I should know these better by now but
setting the criteria quotes kills me. Any help or advice would be greatly
appreciated. Thanks in advance for your time.

 

[rkc]

Sorry to bother you guys with this, I should know these better by now but
setting the criteria quotes kills me. Any help or advice would be greatly
appreciated. Thanks in advance for your time.

You might want to take a look at the Application.BuildCriteria Function.
Search for BuildCriteria either from help in the VBA IDE or in the
Object Browser.