Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Can't find replication id's from controls on form?

    Here is something interesting:

    I had created a database for use in the office. For it I used autonumbers for creating unique record ID's. I would use these as references when making changes, etc, and as criteria in combo box queries. This worked fine.

    Now, I needed to create another database for different use, but the structure of certain tables and forms were the same (customers, locations, and contacts). However because this will be used both within the office and externally I decided to reconfigure and use replicationID's for the unique record identifiers. This so I could either try the replication method, or even find a way to build my own synchronization routine. This seems to work fine for the most part - EXCEPT:

    When I try to lookup data based on the unique record identifier using a query it can't find it. Here's what I mean:
    I have a customer lets say the record identifier is {123}
    I have locations associated with that customer, and I have contacts associated with that customer. So, to keep some integrity in the database, I don't want to reenter the locations, I want the user to select from the available locations for customer number {123}.

    Now we know that the record integrity is there because if I make the control holding customerid visible, it shows {123}, but when I create a query (whether from the form or from the query window) if I try to pull data from that control it comes up with nothing. And, if I use stringfromGUID([CustomerUID]) I get only a dash (or two, I can't remember). I used exactly this method with autonumbers, and it worked fine.

    Similarly, when using a dlookup with a replicationID, I found that direct use of that ID was unsuitable. I fixed that problem, however by doing something like:
    Private Sub eContactUID_Change()
    Dim IDstring As String
    Dim length As Integer

    IDstring = StringFromGUID([eContactUID])
    length = Len(IDstring)
    IDstring = Trim(Mid(IDstring, 6, length - 6))

    Me.EmployeeName = DLookup("[firstname]", "tblcontactmaster", "[contactuid]='" & IDstring & "'") & " " & DLookup("[lastname]", "tblcontactmaster", "[contactuid]='" & IDstring & "'")
    End Sub
    So, I thought I could do something similar with the queries - but thus far, no luck.

    Any ideas?

  2. #2
    Join Date
    Oct 2003
    Posts
    103

    A little more info

    OK, maybe I didn't provide enough information:

    I am trying to use a combo box to select the "location" for a particular customer. I have three basic tables in use, Customer, Contact, and Location, each of which has a unique ID which is a replication ID.

    Location and Contact are subforms on seperate tabs within the Customer form. Location works fine, as I am entering data directly, and the parent/child relationships work find. Contact form works fine except for the locationUID selection control (combo box), for which I want to query the location table, but limit the list to the current customer (parent!customeruid), or explicitly [forms]![frmCompanyLocationsandContacts]![CustomerUID].

    Now, when the database is using integers for the CustomerUID field this works fine. But, in this new copy of the database, where I use replicationID I come up nothing (I assume it's a NULL).

    Is there a trick to working with replication ID's.

    Thanks

  3. #3
    Join Date
    Oct 2003
    Posts
    103

    Solution found

    Ok, so for some reason access will not recognize the guid in a query or dlookup when referenced from a form. Microsoft recommended setting the parameter in the query to text, but that didn't work either.

    However, this got me to thinking, and I created a dummy control - ctformlocationtxt, and set the control source to
    =Trim(Mid(stringfromguid([ctformLocationUID]),6,39))
    this looks exactly the same as the data in ctformlocationuid, but access recognizes it as text.

    Now I am able to reference that control successfully with a statementment such as: =DLookUp("[Address1]","tblLocationMaster","[LocationUID]= '" & [ctformlocationuidtxt] & "'") in a control. This WORKS!

    It's a bit more work, but that solves the problem.

    Perhaps reading this someone else will have a more elegant solution, but I wanted to post what I found for anyone who is interested.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •