control button issue

[Luvnevryminutofit]

Please forgive me for my Noobieness - I am trying to create a control button that will open a form based on particular field on my main form. I tried to use the button wizard but that didn't work so I copied the event procedure code from another control button and edited it to point to the correct form to open when clicked. Here is the code:

__________________________________________
Private Sub Fred_Click()
On Error GoTo Err_Fred_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Basler BE125 SETTINGS"

stLinkCriteria = "RELAY![RELAY #]=" & Me![RELAY #]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Fred_Click:
Exit Sub
Err_Fred_Click:
MsgBox Err.Description
Resume Exit_Fred_Click

End Sub
___________________________________________

Fred was the unique identifier I chose so it would stand out better when I was looking at all the code. The way the control button is suppossed to work, you select a particular Relay # on the Relay Tab Sheet and then click on the BE-125 button to open to a single filtered record on the Basler BE125 Setting form. Instead, I get a pop up box asking for the Relay# and once I enter it, I still get all 84 items in my table. Any ideas? Thanks!!!

[Robeen]

Instead of this:

 
DoCmd.OpenForm stDocName, , , stLinkCriteria

Try something like this:

 
DoCmd.OpenForm stDocName, , , "Relay # = '" & Me.[Relay #] & "'"

Using spaces and # signs in field names is dodgy. Better to avoid them. It just simplifies things.

Let me know if this helps.

[Luvnevryminutofit]

Syntax error in date in query expression 'Relay # = '11885". is the error message I receive now. The 11885 is the actual field data from the Relay Tab Sheet.

Should I have removed or altered the stLink Criteria line?

As for the spaces and # symbol, this DB was created by another user and dumped on me to use/maintain. I don't know if I'm quite up to the challange of correcting mistakes like that.

[Robeen]

Yeah . . . that's why I don't like spaces and reserved words [#] in field names.

As a test, try changing the field names on both your Forms to RelayNum and do this:

 
DoCmd.OpenForm stDocName, , , "RelayNum = '" & Me.RelayNum & "'"

and see if that works.

[Luvnevryminutofit]

I'm not sure I can do that - my data tables are located on a SQL Server and I don't have access to alter the field names. I'll have to put through a request in order to do that and I'm concerned other users will have issues once this change happens.

Did I need to alter the stLinkCriteria line at all?

[boblarson]

The correct syntax (with the spaces since you have them) is:

stLinkCriteria = "[RELAY #]=" & Me![RELAY #]

But if Relay # is text then it would be

stLinkCriteria = "[RELAY #]=" & Chr(34) & Me![RELAY #] & Chr(34)

[Robeen]

You don't have to alter the field names in your Tables.

Just change the Names of the Text Boxes on your Forms. They will still point to the same Table field names.

[boblarson]

You don't have to alter the field names in your Tables.

Just change the Names of the Text Boxes on your Forms. They will still point to the same Table field names.

Good suggestion.

[Luvnevryminutofit]

OK - that got rid of the date error issue but I'm right back where I started from. I get a popup called RelayNUM, when I enter the relay number and click OK, I get the correct form to open but all the records in the table. Here is my code now:

Private Sub Fred_Click()
On Error GoTo Err_Fred_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Basler BE125 SETTINGS"

stLinkCriteria = "[RELAYNUM]=" & Me![RelayNUM]
DoCmd.OpenForm stDocName, , , "RelayNUM = '" & Me.[RelayNUM] & "'"
Exit_Fred_Click:
Exit Sub
Err_Fred_Click:
MsgBox Err.Description
Resume Exit_Fred_Click

End Sub

[boblarson]

You aren't using the correct code again, you need:

stLinkCriteria = "[RELAY #]=" & Me![RelayNUM]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The FIRST part you have to use the FIELD name (red) and then the Control name in the second part (blue) and then you have to use the variable which you just created (green), or else it shouldn't be there and the value should be just in the WhereCondition of the DoCmd.OpenForm code.

[Luvnevryminutofit]

GOT IT - IT WORKS!!!!!!!!!!!!!!!!!!!!!

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!