greenguy
asked on
Dealing with Replication ID data types on a form: Trouble finding records on a form with a ComboBox using a Replication ID ("type mismatch" error)
I have an Access 2000 database with a table (Tbl_People) whose Primary Key (PeopleID) is an Autonumber with the field size set to Replication ID (to create a GUID).
I created a form (frm_People), and I would like to have a combobox that finds a record on the form based on the value of the combobox. However, I receive a "type mismatch" error when I select a value from the combobox.
Here is the code that the wizard generated to find the record: (the combobox is cboRcdSelect, the Replication ID field is PeopleID)
Private Sub cboRcdSelect_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PeopleID] = " & Str(Nz(Me![cboRcdSelect], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
The problem seems to be that I can't convert cboRcdSelect to a string, which is where the "type mismatch" error comes from.
Does anybody know how I can deal with Replication ID values on a form like this? I will need to create over 15 forms with similar structures, so I am looking for a flexible, relatively uncomplicated way to fix it.
I can't continue working on the project until I figure this out, so I would greatly appreciate any help.
Jeff
PS - Would it be easier to create a Function that creates the GUID and insert it into the primary key field as a string through the form? I know how to do that, but the Replication ID seems convenient and I'd like to learn how to work with it more - but I'm open.
I created a form (frm_People), and I would like to have a combobox that finds a record on the form based on the value of the combobox. However, I receive a "type mismatch" error when I select a value from the combobox.
Here is the code that the wizard generated to find the record: (the combobox is cboRcdSelect, the Replication ID field is PeopleID)
Private Sub cboRcdSelect_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PeopleID] = " & Str(Nz(Me![cboRcdSelect], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
The problem seems to be that I can't convert cboRcdSelect to a string, which is where the "type mismatch" error comes from.
Does anybody know how I can deal with Replication ID values on a form like this? I will need to create over 15 forms with similar structures, so I am looking for a flexible, relatively uncomplicated way to fix it.
I can't continue working on the project until I figure this out, so I would greatly appreciate any help.
Jeff
PS - Would it be easier to create a Function that creates the GUID and insert it into the primary key field as a string through the form? I know how to do that, but the Replication ID seems convenient and I'd like to learn how to work with it more - but I'm open.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For this example use:
rs.FindFirst "cstr([PeopleID]) = '" & cStr(Nz(Me![cboRcdSelect], 0)) & "'"
Pete
rs.FindFirst "cstr([PeopleID]) = '" & cStr(Nz(Me![cboRcdSelect],
Pete
ASKER
Thanks Pete,
I accpeted your first answer, and I appreciate that you humored me.
The Cstr function didn't convert the GUID correctly, but I liked your idea. I ended up with:
rs.FindFirst "StringFromGUID([PeopleID] ) = " & Nz(StringFromGUID(Me![cboR cdSelect]) , 0)
It works like a charm, but I'm afraid that the Replication ID is still not a good idea. If you wouldn't mind, what are some of the other problems (aside from the memory usage)?
Thanks,
Jeff
I accpeted your first answer, and I appreciate that you humored me.
The Cstr function didn't convert the GUID correctly, but I liked your idea. I ended up with:
rs.FindFirst "StringFromGUID([PeopleID]
It works like a charm, but I'm afraid that the Replication ID is still not a good idea. If you wouldn't mind, what are some of the other problems (aside from the memory usage)?
Thanks,
Jeff
Sorry for the mislead there. I could remember that you had to convert to a string but forgot that there was a specific command. From memory, the issues all involve key matching - so things like combo boxes, list boxes, relationships, subform links all require special handling.
There is an article here.
http://www.trigeminal.com/usenet/usenet011.asp
Mind you, I'm a bit surprised no one has put forward a contrary view.
Pete
There is an article here.
http://www.trigeminal.com/usenet/usenet011.asp
Mind you, I'm a bit surprised no one has put forward a contrary view.
Pete
ASKER
Pete,
Thanks for the link, I appreciate your extra help - that's exactly the information I was looking for about possible problems.
Jeff
Thanks for the link, I appreciate your extra help - that's exactly the information I was looking for about possible problems.
Jeff
ASKER
I am beginning to see how difficult it might be to use a GUID, but I'm still interested in finding a solution (for curiosity's sake).
Anyone have any other ideas?
Is there any benefit to having a Replication ID GUID at all?