?
Solved

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)

Posted on 2003-11-29
6
Medium Priority
?
935 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:greenguy
  • 3
  • 3
6 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1350 total points
ID: 9842005
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.

Pete

0
 
LVL 4

Author Comment

by:greenguy
ID: 9843040
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?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9843459
For this example use:

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

Pete
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Author Comment

by:greenguy
ID: 9844146
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)?

Thanks,

Jeff  
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9844835
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


0
 
LVL 4

Author Comment

by:greenguy
ID: 9846513
Pete,

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

Jeff
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question