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
771 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 450 total points
Comment Utility
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
Comment Utility
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
Comment Utility
For this example use:

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

Pete
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Author Comment

by:greenguy
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now