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.


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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For all practical purposes other than creating unique record ids for replicated systems, GUIDs are a disaster area.
The ONLY realistic solution to your problem is to remove the GUID field and create a different PK.  You are seeing just one of a list of problems as long as your arm.

If you are using GUIDs because you are replicating then you have to come up with a unique key of your own, in addition to the GUID.

Sorry for that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greenguyAuthor Commented:
Thanks Pete,

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?
For this example use:

rs.FindFirst "cstr([PeopleID]) = '" & cStr(Nz(Me![cboRcdSelect], 0)) & "'"

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

greenguyAuthor Commented:
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![cboRcdSelect]), 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)?


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.

Mind you, I'm a bit surprised no one has put forward a contrary view.


greenguyAuthor Commented:

Thanks for the link, I appreciate your extra help - that's exactly the information I was looking for about possible problems.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.