On Click text box, directed to same record other form

[SoxPats83]

I have an odd dilemma on my hands... I have a form in my db, let's call it "staffed employees". on this "staffed employees" form, i have a sub form, let's call that "employees w/o". the "employees w/o" form is designed in data view and one of the rows is a hyperlink (done in form design view properties, not in table design view). there is also a third form, not linked to either of these first two, called "close w/o". in the "employees w/o" form there are fields in the record source and displayed on the form itself that are identical to fields in the record source and displayed on the "close w/o" form. my goal here is to click on a hyperlink for a specific record on the "employees w/o" form, say "w/o # 54564" and for that to direct me to the form "close w/o", but not only bring me to that form but to filter all records, so only the record with the matching w/o # is displayed.

i apologize for the running on and possibly losing someone here. this problem is really driving me crazy. any help would be appreciated. Thanks!

 

[pbaldy]

I would probably use the double click event and this technique:

http://www.baldyweb.com/wherecondition.htm

 

[SoxPats83]

i appreciate the response, and the link you gave me looks like it may do the trick, however, the values between the two forms that match are pulled from a table, meaning the matching values in the text boxes are bound. so in the code, rather than telling access to find the matching value of "1234", i would have to tell it to find the matching value of"=[value], or something to that affect. am i making any sense?

 

[John Big Booty]

First up welcome to the forum.

The following code in the On Click event of the hyper-link should do the trick;

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "YourSecondFormName"
    
    stLinkCriteria = "[EmployeeWO]=" & Me![FieldName] [COLOR="SeaGreen"]'Replace FieldName with the name  
    'of the field on the current form that holds the employees w/o[/COLOR]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[SoxPats83]

thank you. i will give this one a try as well. if there are multiple resolutions to my problem i would liove to hear them all as i would like to make sure that i accomplish what i am setting out to do.

 

[pbaldy]

That actually uses the same method I posted, just using variables. Have you tried it?

 

[SoxPats83]

i continue to experience problems with the coding. specifically with the stLinkCriteria = "[EmployeeWO] ...... part

 

[pbaldy]

What are the problems? What is your exact code? What is the data type of EmployeeWO?

 

[SoxPats83]

alright. i used the second code provided in the Baldy Web page and it brings me to the proper form, and is filiered to a single record. however, it is showing a blank record and not the matching one.

I am using:

DoCmd.OpenForm "Destination Form", , , "Field Name i wish to Look Up on the destination Form = '" & Me.Field name of matching field on current Form & "'"

 

[pbaldy]

Can you post the db?

 

[SoxPats83]

sorry, i am unable to do that. something i may be confused on is the sifference between the field name and control name?

 

[pbaldy]

Then will you clarify what the data type of EmployeeWO is, and post the exact code? The field name is the name of the field in the table. The control name is the name of the textbox, combo box, etc on the form. They might be the same.

 

[SoxPats83]

i have done some changing to tables and forms names to remove and spaces of \ and # characters. the below code is located in a form in a text box as an on click event.

Private Sub CloseWO_Click()
DoCmd.OpenForm "CloseWO", , , "WOnumber = '" & Me.WOnumber & "'"
End Sub

 

[SoxPats83]

also, dtata type for all fields is text

 

[pbaldy]

That should work presuming WOnumber is the name of the field in the table and the control on the form, and that the field in the table is text. You might set a breakpoint so you can make sure the value coming from the form is what you expect:

http://www.baldyweb.com/Debugging.htm

 

[SoxPats83]

the thing is, when i enter the debug the private sub line is highlighted yellos, and then on the docmd line, the Me.WOnumber is selected.

 

[pbaldy]

Make sure Option Explicit appears at the top of the form module. It should look like this:

Option Compare Database
Option Explicit

Then click on Debug/Compile...

That will point out any compile errors, which it sounds like there might be. If you find and fix one, compile again until there are no errors. If it's highlighting that, then it is likely not the correct name of the textbox.

 

[SoxPats83]

i may be fighting a losing battle here. i appreciate all of your help. but i am continuing to get the same problem. in my case the field name and the control name are the same, and i may be confused because i am having trouble picturing a scenario where they will be different...

 

[SoxPats83]

in the compile error pop up it is telling me: "Method or data member not found"

 

[boblarson]

Hopefully it is okay for me to interject. Change the control name to txtWOnumber and then use:

DoCmd.OpenForm "CloseWO", , , "WOnumber = '" & Me.txtWOnumber & "'"

OR if you aren't going to change the name of the text box then use:

DoCmd.OpenForm "CloseWO", , , "WOnumber = '" & Me!WOnumber & "'"