?
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
?
856 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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