Dealing with Replication ID's 1

[JezzaHyde]

Hey folks,

I'm having trouble dealing with the output of Replication ID's. Basically, i'm trying to create a query on the fly, with the "WHERE" clause containing the ID of the current record, and this ID is a Replication ID.

The problem is that when the ID is passed to the SQL string, it comes out as a series of boxes (which are represented in a msgbox as "?????????&quot, even if the ID is stored in a string.

I am just wondering if there is any way around this problem.

Cheers

-Jezza

 

[LittleSmudge]

The ReplicationId is a LONG Integer. If you are treating it like a STRING then that might be the cause of the problem.

There is 'nothing special' about ReplicationIds. They act just like any other LongInt. So if you are having problems - it is something to do with what you are doing to it. Not the fact that it is a RepId.


'ope-that-'elps.

G LS

 

[JezzaHyde]

Thanks LS, but the RepID contains letters as well?!

Here is the code i am using to create the query...perhaps it will give you a better idea of what's going on.

  Dim db As Database
  Dim qdef As QueryDef
  
  Dim strSQL As String
  Dim strSelectSQL As String
  Dim strWhere As String
  
  strSelectSQL = "SELECT MasterList.[CLIENT ID], MasterList.EquipmentID, Manufacturerlookup.Name, Equipment.Model, Equipment.SerialNumber FROM MasterList INNER JOIN (Manufacturerlookup INNER JOIN Equipment ON Manufacturerlookup.[Manufacturer#] = Equipment.[Manufacturer#]) ON MasterList.EquipmentID = Equipment.EquipmentID"
  
  strWhere = vbNullString
  
  strWhere = " WHERE MasterList.[CLIENT ID]= " & Me.CLIENT_ID
  
  strSQL = strSelectSQL & strWhere
  
  Set db = CurrentDb
  db.QueryDefs.Delete "Equipment_Selector"
  
  Set qdef = db.CreateQueryDef("Equipment_Selector", strSQL)

That leaves my WHERE clause looking like this:
"WHERE (((MasterList.[CLIENT ID])=[꣊꣏꣊꣏꣊꣏꣊꣏]));"

Thanks in advance

-Jezza

 

[LittleSmudge]

A clear case of why you should avoid space characters in object names - FAQ700-2190 .

I assume that Client_Id is a control on the current form containing this thing that you call a ReplicationId.
( If is really is a ReplicationId then Access would NOT create it using letters and numbers. A real RepId IS a LONG INT. )

If Client_Id contains a text string - what field is that stored in, in which table ?

If Client_Id contains a text string then your code will need to be

strWhere = " WHERE MasterList.[CLIENT ID]= '" & Me.CLIENT_ID & "'"

However, I'm not sure that any of the above actually explains why you are getting [꣊꣏꣊꣏꣊꣏꣊꣏].

Try the above and let me know what you get back.



G LS

 

[LittleSmudge]

As an afterthought :-

The line
strWhere = vbNullString

is doing exactly nothing for you other than taking up processing time. So get rid of it.

And regardless of the contents of the Client_Id control

strWhere = " WHERE MasterList.[CLIENT ID]= " & Me.CLIENT_ID

will NEVER produce the result
"WHERE (((MasterList.[CLIENT ID])=[꣊꣏꣊꣏꣊꣏꣊꣏]));"

That is the ACTUAL code that you are using to get the Where clause listed ( or what do you actually get from the code listed ) ?


G LS

 

[JezzaHyde]

Hey LS,

I took your advice about the field names, and i went through and fixed them up...and in the process i reverted to using Long Int's as my primary key rather than RepID's (because they were proving extremely ackward to work with) and alas, with i little bit of tweaking, i can now properly create the query. Thanks for the help

On a seperate note, could you possibly help me with something else?? As it stands, i use the dynamically created query as the rowsource for a combo box on a new form. As such, i want to be able to cancel the opening of the new form if the query does not contain anything. Have you got any advice as to what direction i should take to get this working??

Thanks

-Jezza

 

[LittleSmudge]

Okay Jezza,

First off the cancelable event to use is the OnOpen event

Then the question becomes - how to check that records exist for the query created ?

So my solution would be to use Recordsets.
The only problem then is that how one does recordsets depends on the version of access that one is using.

The Solution that follows is for ADO ( Access2k and beyond )
If you are using Access97 or earlier - let me know and I'll convert to DAO - ( if you are unsure. )

Private Sub Form_Open( Cancel As Integer )

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

Dim strSQL As String
' Code in here that dynamically generated the query for the combo box

rst.Open strSQL
If rst.EOF Then
    ' Query has returned ZERO records
    Cancel = True
Else
    ' Query generates 1+ records
    Cancel = False ' Not strictly necessary - but helps SelfDocumentation
    comboBox.RowSource = strSQL
End If

rst.Close
End Sub

QED.

G LS

 

[jpbrault]

I had a similar probleme and it is because the "{}" are some time return with the replication ID. To convert then in a string I use the following code:

Left(Right(StringFromGUID(CodeEntree), 38), 36)

 

[JezzaHyde]

LittleSmudge,

Thanks heaps mate, that worked an absolute treat!!

You get a vote from me...thanks for the help.

Cheers

-Jezza

 

[Laois]

By the way Replication IDs are not Long Integers. Below is a sample Replication ID

{0005AEC1-9408-4EBE-B733-81F9C6E3BD3F}

Dermot

 

[JezzaHyde]

Yeah Laois, "Globally Unique Identifiers".

Look sort of like a Hex number don't they??

-Jezza