Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
891 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

618 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