TextBox Value Issue

  • Thread starter Thread starter Branden Johnson
  • Start date Start date
B

Branden Johnson

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
 
Branden said:
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.
Click to expand...


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 & """"
 
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
 
The StringFromGUID function converts a GUID, which is an array of type Byte,
to a string.

The counterpart is
 
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.
 
Branden said:
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
Click to expand...


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.
 
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
 
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
 
Back
Top