Why am I being prompted for parameter?

[DKO]

This is in Access 2003.

In my form (continuous, if that matters), when the user clicks on the [UIC] field, I want to open a subform and display the record containing the same UIC (unit identification code).

This is my current code on the click event:

Private Sub UIC_Click()
On Error GoTo Err_UIC_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "sf_STRENGTH"
 
    stLinkCriteria = "[UIC]=" & Me![UIC]
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_UIC_Click:
    Exit Sub
Err_UIC_Click:
    MsgBox Err.Description
    Resume Exit_UIC_Click
End Sub

And the query that the second form is based on:

SELECT q_AUTH.UIC, q_AUTH.SumOfAUTH AS AUTH, q_ASSIGNED.CountOfSSN AS ASSIGNED, [CountOfSSN]/[SumOfAUTH] AS STRENGTH
FROM q_AUTH LEFT JOIN q_ASSIGNED ON q_AUTH.UIC=q_ASSIGNED.UIC;

Now - when I click on the UIC field, I'm prompted for a parameter. But instead of asking me for "Me!UIC" like I'd expect....it already lists the text that is stored in the UIC field.

Ie - if the text in the UIC field is "XXX", it lists "XXX" as the requested parameter instead of "Me!UIC"....and then when I enter "XXX" as the criteria that I want, it opens the form correctly.

So any ideas on why it's prompting me for the parameter instead of just opening the form with the correct criteria?

 

[neileg]

I don't know if this will solve the problem, but things can go very strange when you use the same name for different objects. As it stands, you have a form, a table and a control all called UIC.

 

[Scooterbug]

What is the data type for UIC in your table? If it's text, you need to add quotes around the criteria.


I generally use chr(34), cause for the life of me I cant remember the rule for using quotes inside of quotes. So I would do it this way:

stLinkCriteria = "[UIC]=" & chr(34) & Me![UIC] & chr(34)

 

[namliam]

I generally use chr(34), cause for the life of me I cant remember the rule for using quotes inside of quotes. So I would do it this way:

stLinkCriteria = "[UIC]=" & chr(34) & Me![UIC] & chr(34)

What he said....
Alternatives, if you like them:
stLinkCriteria = "[UIC]=""" & Me![UIC] &""""
stLinkCriteria = "[UIC]='" & Me![UIC] &"'"

Good luck !

 

[Insane_ai]

HAve you tried referencing the source table for the form you want to open? Example: stLinkCriteria = "[TableOrQuery]![UIC]= " & Me.[UIC]
This will help Access identify a specific domain for each instance of the [UIC] field.

 

[DKO]

Ah, brilliant. Thanks everyone. Sorry for the slow reply.

Scooterbug and namliam had the right idea. I ended up using: stLinkCriteria = "[UIC]=""" & Me![UIC] &""""

Works perfectly.