TextBox Value Issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Branden Johnson - 12 Dec 2005 02:55 GMT

Using Microsoft Access 2002 (10.2627.2625)
Windows XP Pro SP1

I have a form called frmCustomerDetail. On this form I have included the
Primary Key for the base table, CUSTOMERID, in a hidden TextBox:
txtCustomerID.

I am trying to place a button on this form that opens another form,
frmSystemDetail, showing the record in the SYSTEMDETAIL table that
corresponds to the record being displayed on frmCustomerDetail.

I know this sounds simple. I have spent hours trying to get this to work! I
have tried writing my own Sub Routine, creating a Macro, and using the
Control Wizard. None of these has worked. Here is the code generated by the
Control Wizard:

Private Sub cmdSysDetail_Click()

On Error GoTo Err_cmdSysDetail_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmSystemDetail"

stLinkCriteria = "[CUSTOMERID]=" & Me![txtCustomerID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSysDetail_Click:

Exit Sub

Err_cmdSysDetail_Click:

MsgBox Err.DESCRIPTION

Resume Exit_cmdSysDetail_Click

End Sub

I am getting the following error when I click on the CommandButton:

Syntax error in query expression '[CUSTOMERID]= '.

I have tried making txtCustomerID visible (correctly shows the CUSTOMERID)
and still does not work.

Final Note: All my Primary Keys and Foreign Keys are AutoNumber-Replication
ID format. Maybe this is where I am running into trouble.

Any help, or suggestions, would be greatly appreciated. Thanks, in advance.

Branden Johnson

Reply to this Message

Marshall Barton - 12 Dec 2005 03:59 GMT

>Using Microsoft Access 2002 (10.2627.2625)
>Windows XP Pro SP1
[quoted text clipped - 41 lines]
>
>Any help, or suggestions, would be greatly appreciated. Thanks, in advance.

The error message indicates that txtCustomerID contains Null

OTOH, I think a Replication Autonumber is a Text value so
you would need to use:

stLinkCriteria = "CUSTOMERID=""" & Me!txtCustomerID & """"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Branden Johnson - 12 Dec 2005 05:57 GMT

Thank you for the reply. That is what I assumed; I guess I don't understand
why I would be getting a NULL value...

I know that the CUSTOMER record exists; I created it several days ago. I
also know the SYSTEMDETAIL record for the CUSTOMER exists. When I hardcode
the CUSTOMERID as shown here ==> stLinkCriteria = "[CUSTOMERID]=
'{6CBC5B32-0D1F-4327-B12F-0EFBC4C7369E}'" <== frmSystemDetail opens with the
correct filter in place.

As I said in the first post, I made the bound txtCustomerID visible and it
displays the correct ID. I have tried several variations in syntax on the
"stLinkCriteria" line without any positive results. How could I be getting
a NULL value? I did, in one of my several attempts, get the frmSystemDetail
to open in AddNew mode with no data on the form.

Isn't there a function GUIDToString? Maybe that is not even what I would
need to use; regardless, I have a brain blockage on how to use it. I get an
error of "Unrecognized function call" when I attempted to place it in my
code.

I found a cludgy workaround, but I would like to know why this is not
working! I made txtCustomerID visible and shrunk it down. Then I removed the
border and set the BackColor to the Form's BackColor. I changed my Sub
Routine to the following and it works:

Private Sub cmdSystemDetail_Click()
On Error GoTo Err_cmdSystemDetail_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim sCustomerID As String

txtCustomerID.SetFocus
stDocName = "frmSystemDetail"

stLinkCriteria = "[CUSTOMERID]=" & txtCustomerID.Text
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSystemDetail_Click:
Exit Sub

Err_cmdSystemDetail_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdSystemDetail_Click

End Sub

Reply to this Message

John Spencer - 12 Dec 2005 13:22 GMT

The StringFromGUID function converts a GUID, which is an array of type Byte,
to a string.

The counterpart is

> Thank you for the reply. That is what I assumed; I guess I don't
> understand
[quoted text clipped - 47 lines]
>
> End Sub

Reply to this Message

John Spencer - 12 Dec 2005 13:31 GMT

Whoops! wrong key.

The StringFromGUID function converts a GUID, which is an array of type Byte,
to a string.

The counterpart is

The GUIDFromString function converts a string to a GUID, which is an array
of type Byte.

> The StringFromGUID function converts a GUID, which is an array of type
> Byte, to a string.
>
> The counterpart is

SNIP>>

Reply to this Message

Marshall Barton - 12 Dec 2005 17:27 GMT

>Thank you for the reply. That is what I assumed; I guess I don't understand
>why I would be getting a NULL value...
[quoted text clipped - 42 lines]
>
>End Sub

The fact that this works implies to me that a GUID is
neither a text string or a number. I don't know enough
about GUID's to have an intelligent discussion on the
subject.

I see that John seems to know more than I do about this, so
I should bow out here and let others try to deal with your
question.

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Spencer - 12 Dec 2005 18:27 GMT

Branden,
At this point I am unsure whether you have something that is working for you
or not. Do you want/need further information/discussion on this topic?

John

> Thank you for the reply. That is what I assumed; I guess I don't
> understand
[quoted text clipped - 47 lines]
>
> End Sub

Reply to this Message

Branden Johnson - 12 Dec 2005 21:36 GMT

I have a working solution, referred to as a "cludgy workaround" in one of my
previous posts.

I would like to know why this is not working, of course, but I have moved on
and accepted my workaround for now.

Thank you Marshall and John for your assistance.

Respectfully,
Branden Johnson

Reply to this Message