Filtering using primary key

[bcooler]

Hello!

I've recently created an Access front end for SQL server (which uses what I am guessing is called GUID primary keys). In my form, I try to open a report based on the current subform record that is chosen via a command button placed in each record. This opens the report based on the record's primary key:

Private Sub cmdOpen_Click()
    DoCmd.OpenReport "rptTestResults", acViewPreview, , "[ID] = " & Me.txtID
End Sub

This typically works, but am getting a parameter inputbox (in Asian fonts???). In my experience, this means I didn't set the filter up correctly. BUT ASIAN FONT???

Also, when I perform a "watch" on the me.txtID textbox, I get multiple values listed. HUH???

I wanted to troubleshoot down to the weird GUID key, so I tried the following (added a primary key value directly into the code)

Private Sub cmdOpen_Click()
    DoCmd.OpenReport "rptTestResults", acViewPreview, , "[ID] = " & "{CF5DE945-6E0A-406B-95F3-EFC41AD106D5}"

This works.

What am I doing wrong? I tried to use the functions: HEX, GUID, STR, etc to convert the key to something Access can understand better. No luck.

Thanks!
Brian

 

[bcooler]

Sorry to waste your time. I found that this works:

Private Sub cmdOpen_Click()
    DoCmd.OpenReport "rptTestResults", acViewPreview, , "[ID] = " & StringFromGUID(Me.txtID)
End Sub

Not sure what's going on here, but it does work. Thanks to anyone who started looking at this.

 

[PHV]

Not sure what's going on here
Access stores a GUID as an array of bytes and thus you should convert it to string to use it as a filter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bcooler]

I'm guessing this is more about ignorance than anything else as I have only known about Access primary keys until now. Maybe this approach was obvious to everyone else?

 

[bcooler]

After a little more education via Google, I found that Access is a little fussy with GUID. Some have suggested (and I was successful with) an approach where you convert the GUID to a string (as I said before), then even go as far as removing the "{" and "}" from the string. I'm not sure if that last part matters, but it worked with those changes:

Replace(Replace(StringFromGUID([Table]![Field]),"{",""),"}","")

I sometimes used this in the VBA as a filter to open reports, and other times, I built a query that was nothing more than the original SQL table with an added field composed of the converted GUID.

One thing to note, once this item is a string, your VBA has to contain it with quotes:

Private Sub cmdOpen_Click()
    DoCmd.OpenReport "rptTestResults", acViewPreview, , "[ID] = " & Replace(Replace(StringFromGUID(Me.txtID),"{",""),"}","")
End Sub

Anyway, just wanted to complete the loop for others. Have a great day!

 

[PHV]

You meant this, I presume:
DoCmd.OpenReport "rptTestResults", acViewPreview, , "ID=[!]'[/!]" & Replace(Replace(StringFromGUID(Me.txtID),"{",""),"}","") & "[!]'[/!]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bcooler]

Yes, you are correct, except I generally use the 2 double quotes instead of the single.

 

[PHV]

I generally use the 2 double quotes instead of the single
Well, when I play with JetSQL, I prefer to use double quotes for VBA and single quotes for SQL.