Using GUIDs in MS Access

[jhard...@gmail.com]

I have a form in Access 2007 that can switch a subform view between Form View and Datasheet view, but the selected record doesn't persist when the view change is made.

I have been trying to grab the ID from the subform and then FindFirst that ID after the view has been changed. The problem I am running into is that the ID for that subform is a GUID in SQL Server, so I am having a tough time converting it or getting FindFirst to work

Here is my code:

Private Sub frmFormView_AfterUpdate()

DBGuidFld = Me.subBilling.Form.RecordsetClone!ID
strGUID = StringFromGUID(Me.subBilling.Form.RecordsetClone.Fields("ID"))

If frmFormView.value = 1 Then
Me.subBilling.SetFocus
DoCmd.RunCommand acCmdSubformFormView
ElseIf frmFormView.value = 2 Then
Me.subBilling.SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End If

Forms!FrmBilling!subBilling.Form.Recordset.FindFirst "StringFromGUID(" & DBGuidFld & ") = " & strGUID

RefreshForm

End Sub

I get an error on the FindFirst line

[JB Guernsey]

Hi.

strGUID is a string variable and has to be enclosed in quotes for the find to work. There are several ways to do this. I find multiple quote marks confusing so I use chr(34) instead of quotes (chr(34) is the quote character).

So

Forms!FrmBilling!subBilling.Form.Recordset.FindFirst "StringFromGUID(" & DBGuidFld & ") = " & strGUID

should be

Forms!FrmBilling!subBilling.Form.Recordset.FindFirst "StringFromGUID(" & DBGuidFld & ") = " & chr(34) & strGUID & chr(34)

Now I never use the Recordset feature you have used so I don't know if that's a valid construction. I would have used recordsetclone and bookmarks:

dim rsClone as recordset
set rsClone = Forms!FrmBilling!subBilling.recordsetclone
rsClone.findfirst "MyIDField = " & chr(34) & strGUID & chr(34)

'where MYIDField is the field containing the string you are searching for

if rsClone.nomatch then

else

end if

[JB Guernsey]

On Wednesday, June 19, 2013 9:29:09 PM UTC+1, jhard...@gmail.com wrote:

Sorry about that - my system decided to send my reply before I'd finished. Trying again:

Forms!FrmBilling!subBilling.Form.Recordset.FindFirst "StringFromGUID(" & DBGuidFld & ") = " & strGUID

should be

Forms!FrmBilling!subBilling.Form.Recordset.FindFirst "StringFromGUID(" & DBGuidFld & ") = " & chr(34) & strGUID & chr(34)

Now I never use the Recordset feature you have used so I don't know if that's a valid construction. I would have used recordsetclone and bookmarks: WARNING - AIR CODE!

dim rsClone as recordset
set rsClone = Forms!FrmBilling!subBilling.recordsetclone
rsClone.findfirst "MyIDField = " & chr(34) & strGUID & chr(34)

'where MYIDField is the field containing the string you are searching for

if rsClone.nomatch then

msgbox "Cannot find strGUID"
'etc
else
Forms!FrmBilling!subBilling.bookmark = rsClone.bookmark
end if

'any other code you might want.

HTH

JB

[JB Guernsey]

On Wednesday, June 19, 2013 9:29:09 PM UTC+1, jhard...@gmail.com wrote:

Here we go again - didn't read your post carefully enough.

If Forms!FrmBilling!subBilling is a subform you will need:

set rsClone = Forms!FrmBilling!subBilling.Form.recordsetclone

Not in a position to check whether searching in a subform will need any extra tweaks. It may be that you'll have to read the Parent's (key)ID value from the subform's recordsetclone and then manipulate the parent form via its own recordsetclone to get to the correct record.

JB

[jhard...@gmail.com]

Here is my current code:
Getting a Complile Error on the "FindFirst" line, "Method or Data Member Not Found". Is there an object library that I need to enable in Resources?

Private Sub frmFormView_AfterUpdate()
Dim rsClone As ADODB.Recordset
Set rsClone = Forms!FrmBilling!subBilling.RecordsetClone

dbGuidFLD = Me.subBilling.Form.RecordsetClone!ID
Debug.Print "DBGUIDFld = " & dbGuidFLD

strGUID = StringFromGUID(Me.subBilling.Form.RecordsetClone.Fields("ID"))

Debug.Print "strGUID = " & strGUID

If frmFormView.value = 1 Then
Me.subBilling.SetFocus
DoCmd.RunCommand acCmdSubformFormView
ElseIf frmFormView.value = 2 Then
Me.subBilling.SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End If

rsClone.FindFirst "StringFromGUID(" & dbGuidFLD & ") = " & Chr(34) & strGUID & Chr(34)
If rsClone.NoMatch Then
MsgBox "Cannot find strGUID"
Else
Forms!FrmBilling!subBilling.Bookmark = rsClone.Bookmark
End If

RefreshForm

End Sub

[jhard...@gmail.com]

Changed Dim rsClone As ADODB.Recordset to Dim rsClone As DAO.Recordset, which cleared the FindFirst error, but now I get the same error on the
Set rsClone = Forms!FrmBilling!subBilling.RecordsetClone line

[jhard...@gmail.com]

On Wednesday, June 19, 2013 1:29:09 PM UTC-7, jhard...@gmail.com wrote:

Changed to Set rsClone = Forms!FrmBilling!subBilling.Form.RecordsetClone and get a "Type Mismatch" error now.

[JB Guernsey]

Hi.

Can you post your latest code please. I've lost track of what you're currently working with.

JB