Using GUID in a query

[Guest]

I have a replicated database, using Replication ID (rather than Long
Interger) for the primary keys.

I am trying to use the Replication ID (GUID) in a select query, referencing
an open subform. When i run the query with the form closed and enter the
guid in the parameters box that pop up there is no problem, but it will not
run properly referencing the open form - I just get blank results as it seems
the query is interpreting the inpt as a string.

I have tried to use GUIDfromString as follows "GUIDfromString
([forms]![student basic]![student suggested courses]![studentid]" but I get
an error saying that the expression is too long to evaluate.

Help appreciated!!!!

 

[Jeff Boyce]

Not sure about GUID specifics, but the expression for referring to a form's
control only has three elements (unless you are referring to a subform's
control).

Forms!YourFormName!YourControlName

 

[Sylvain Lafontaine]

GUID - a good idea translated into a bad one by the MS staff. For a start:

http://www.trigeminal.com/usenet/usenet011.asp
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9
http://www.trigeminal.com/code/guids.bas

For accessing the controls on your subform, [student suggested courses] is
probably the name of your subform control and is not the subform itself; so
you must add the property .Form before accessing the controls on your
subform, something like:

[forms]![student basic]![student suggested courses].Form![studentid]

S. L.

 

[Guest]

Thanks for your reply.

Referencing the control is not the issue, I don't think - I can use the
Expression Builder to do that and it fills the control's identifier for me.

The problem is that the query is not using the information from the control
on the subform correctly.

I think I'm right that query needs to evaluate a GUID using "{guid {.....}}"
- the problem is I cant use this with the identifier for the control.

When I type something like "{guid {[Forms]![student basic]![studentid]}} (or
other permutation it tells me that the GUID is not in the right format. When
I try to use {guid {StringfromGUID([Forms]!.....)}} it tells me that the
expression is too complex to evaluate.

I losing what little hair I have left.

Can you help again or does anyone else have experience of doing this?

Thanks.

GUID - a good idea translated into a bad one by the MS staff. For a start:

http://www.trigeminal.com/usenet/usenet011.asp
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9
http://www.trigeminal.com/code/guids.bas

For accessing the controls on your subform, [student suggested courses] is
probably the name of your subform control and is not the subform itself; so
you must add the property .Form before accessing the controls on your
subform, something like:

[forms]![student basic]![student suggested courses].Form![studentid]

S. L.

I have a replicated database, using Replication ID (rather than Long
Interger) for the primary keys.

I am trying to use the Replication ID (GUID) in a select query,
referencing
an open subform. When i run the query with the form closed and enter the
guid in the parameters box that pop up there is no problem, but it will
not
run properly referencing the open form - I just get blank results as it
seems
the query is interpreting the inpt as a string.

I have tried to use GUIDfromString as follows "GUIDfromString
([forms]![student basic]![student suggested courses]![studentid]" but I
get
an error saying that the expression is too long to evaluate.

Help appreciated!!!!

 

[Sylvain Lafontaine]

I'm sorry but I never used GUID, so I can't help you any longer.

If I have some time tomorrow, I will try to make some tests but a possible
solution for you might be to add a second column to your table and store
there the string representations of your GUIDs. This will give you the
possibility of easily making Select queries; until you find the answer to
this enigma.

S. L.

Thanks for your reply.

Referencing the control is not the issue, I don't think - I can use the
Expression Builder to do that and it fills the control's identifier for
me.

The problem is that the query is not using the information from the
control
on the subform correctly.

I think I'm right that query needs to evaluate a GUID using "{guid
{.....}}"
- the problem is I cant use this with the identifier for the control.

When I type something like "{guid {[Forms]![student basic]![studentid]}}
(or
other permutation it tells me that the GUID is not in the right format.
When
I try to use {guid {StringfromGUID([Forms]!.....)}} it tells me that the
expression is too complex to evaluate.

I losing what little hair I have left.

Can you help again or does anyone else have experience of doing this?

Thanks.

GUID - a good idea translated into a bad one by the MS staff. For a
start:

http://www.trigeminal.com/usenet/usenet011.asp
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9
http://www.trigeminal.com/code/guids.bas

For accessing the controls on your subform, [student suggested courses]
is
probably the name of your subform control and is not the subform itself;
so
you must add the property .Form before accessing the controls on your
subform, something like:

[forms]![student basic]![student suggested courses].Form![studentid]

S. L.

I have a replicated database, using Replication ID (rather than Long
Interger) for the primary keys.

I am trying to use the Replication ID (GUID) in a select query,
referencing
an open subform. When i run the query with the form closed and enter
the
guid in the parameters box that pop up there is no problem, but it will
not
run properly referencing the open form - I just get blank results as it
seems
the query is interpreting the inpt as a string.

I have tried to use GUIDfromString as follows "GUIDfromString
([forms]![student basic]![student suggested courses]![studentid]" but I
get
an error saying that the expression is too long to evaluate.

Help appreciated!!!!

 

[Sylvain Lafontaine]

Maybe the following article will put some light:
http://support.microsoft.com/kb/210612/en-us

S. L.

I'm sorry but I never used GUID, so I can't help you any longer.

If I have some time tomorrow, I will try to make some tests but a possible
solution for you might be to add a second column to your table and store
there the string representations of your GUIDs. This will give you the
possibility of easily making Select queries; until you find the answer to
this enigma.

S. L.

Thanks for your reply.

Referencing the control is not the issue, I don't think - I can use the
Expression Builder to do that and it fills the control's identifier for
me.

The problem is that the query is not using the information from the
control
on the subform correctly.

I think I'm right that query needs to evaluate a GUID using "{guid
{.....}}"
- the problem is I cant use this with the identifier for the control.

When I type something like "{guid {[Forms]![student basic]![studentid]}}
(or
other permutation it tells me that the GUID is not in the right format.
When
I try to use {guid {StringfromGUID([Forms]!.....)}} it tells me that the
expression is too complex to evaluate.

I losing what little hair I have left.

Can you help again or does anyone else have experience of doing this?

Thanks.

GUID - a good idea translated into a bad one by the MS staff. For a
start:

http://www.trigeminal.com/usenet/usenet011.asp
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9
http://www.trigeminal.com/code/guids.bas

For accessing the controls on your subform, [student suggested courses]
is
probably the name of your subform control and is not the subform itself;
so
you must add the property .Form before accessing the controls on your
subform, something like:

[forms]![student basic]![student suggested courses].Form![studentid]

S. L.

I have a replicated database, using Replication ID (rather than Long
Interger) for the primary keys.

I am trying to use the Replication ID (GUID) in a select query,
referencing
an open subform. When i run the query with the form closed and enter
the
guid in the parameters box that pop up there is no problem, but it
will
not
run properly referencing the open form - I just get blank results as
it
seems
the query is interpreting the inpt as a string.

I have tried to use GUIDfromString as follows "GUIDfromString
([forms]![student basic]![student suggested courses]![studentid]" but
I
get
an error saying that the expression is too long to evaluate.

Help appreciated!!!!

 

[Sylvain Lafontaine]

And this one:
http://support.microsoft.com/kb/282977/en-us

S. L.

Maybe the following article will put some light:
http://support.microsoft.com/kb/210612/en-us

S. L.

I'm sorry but I never used GUID, so I can't help you any longer.

If I have some time tomorrow, I will try to make some tests but a
possible solution for you might be to add a second column to your table
and store there the string representations of your GUIDs. This will give
you the possibility of easily making Select queries; until you find the
answer to this enigma.

S. L.

Thanks for your reply.

Referencing the control is not the issue, I don't think - I can use the
Expression Builder to do that and it fills the control's identifier for
me.

The problem is that the query is not using the information from the
control
on the subform correctly.

I think I'm right that query needs to evaluate a GUID using "{guid
{.....}}"
- the problem is I cant use this with the identifier for the control.

When I type something like "{guid {[Forms]![student basic]![studentid]}}
(or
other permutation it tells me that the GUID is not in the right format.
When
I try to use {guid {StringfromGUID([Forms]!.....)}} it tells me that the
expression is too complex to evaluate.

I losing what little hair I have left.

Can you help again or does anyone else have experience of doing this?

Thanks.



:

GUID - a good idea translated into a bad one by the MS staff. For a
start:

http://www.trigeminal.com/usenet/usenet011.asp
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9
http://www.trigeminal.com/code/guids.bas

For accessing the controls on your subform, [student suggested courses]
is
probably the name of your subform control and is not the subform
itself; so
you must add the property .Form before accessing the controls on your
subform, something like:

[forms]![student basic]![student suggested courses].Form![studentid]

S. L.

I have a replicated database, using Replication ID (rather than Long
Interger) for the primary keys.

I am trying to use the Replication ID (GUID) in a select query,
referencing
an open subform. When i run the query with the form closed and enter
the
guid in the parameters box that pop up there is no problem, but it
will
not
run properly referencing the open form - I just get blank results as
it
seems
the query is interpreting the inpt as a string.

I have tried to use GUIDfromString as follows "GUIDfromString
([forms]![student basic]![student suggested courses]![studentid]" but
I
get
an error saying that the expression is too long to evaluate.

Help appreciated!!!!

 

[John Spencer (MVP)]

I believe that

StringFromGuid adds the {Guid etc to the string that is created. So if you
do, you will end up with {guid {guid ...


SNIP

me.

The problem is that the query is not using the information from the
control
on the subform correctly.

I think I'm right that query needs to evaluate a GUID using "{guid
{.....}}"
- the problem is I cant use this with the identifier for the control.

When I type something like "{guid {[Forms]![student basic]![studentid]}}
(or
other permutation it tells me that the GUID is not in the right format.
When
I try to use {guid {StringfromGUID([Forms]!.....)}} it tells me that the
expression is too complex to evaluate.

I losing what little hair I have left.

Can you help again or does anyone else have experience of doing this?

Thanks.

 

[wbowman]

I thought this tread was left hanging so I decided I would post this work around.

When ever I have a problem using a the value of a form control in a query, In the form I save the value of the control to a global variable. Then I write a one line function to get the value of the variable. I then use the get function as my query criteria.

In this case I have a form with employeeID, every time the employeeID changes I update the varable glbEmployeeID by putting the following code in the On Current Event.

Private Sub Form_Current()
glbEmployeeID = Me.EmployeeID
End Sub

In all my database I have a module called Global which is where I define any global variables and the get functions that retrieve the global variables. The get functions are required because queries do not recognize the global variable names. In module Global declare the variables

Public glbEmployeeID As Variant 'note this is a GUID

Then in module Global create the “Get” function

Public Function GetglbEmployeeID() As Variant
GetglbEmployeeID = glbEmployeeID
End Function

Now in the query grid you just enter

GetglbEmployeeID()

You need the empty brackets or the query will not recognize it as a function.

Often you will find you do not need to change the variable type or anything to get it to work.

This works on Access database and Access database connected to MS SQL Server. I learned this on the net and it works with GUID so I thought I would contribute it here.