stLinkCriteria is not causing the DoCmd.OpenForm to open the right record

I have a Private Sub that is supposed to open a form with one record as the Control or Row source

This is the code that is supposed to open the form and show me the record I clicked on.

Private Sub List78_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Call_Ticket"
    stWhoCalled = "DirectAccess"
    stLinkCriteria = "Forms!Call_Ticket!Service_Tag_No = " & Me!List78
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

Open in new window


This is the code that populates the form when it opens. It is my understanding that stLinkCriteria will cause the record I want to be selected as the rowsource.
If stWhoCalled = "DirectAccess" Then

    Me.RecordSource = "OpenQuotes"
    Me.Caption = "You are Editing Open Quotes"
End If

Open in new window


This is the OpenQuotes Query
SELECT DISTINCTROW Main.CompanyName, Main.Real_Name, Main.Billable, Main.Distance, Main.Phone_Number, Main.Problem, Main.Call_Open_Time, Main.[Call_Close Time], Main.Installation_Date, Main.Service_Tag_Number_Auto, Main.Type_Support, Main.Address_1, Main.Address_2, Main.City, Main.State, Main.Zip_Code, Main.QBInvoiceNumber, Main.Billed, Main.Keep4Today, Main.Parts, Main.[Project Name], Main.Total, Main.EstHours
FROM Main
WHERE (((Main.[Call_Close Time]) Is Null) AND ((Main.Type_Support)="Proposal / Quote"))
ORDER BY Main.CompanyName;

Open in new window


When I dblclick on an item in Me!List78, I expect the Call Ticket for to open to the record that has the same Service_Tag_No.

Thanks for your help.
jasgotAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
instead of using this line

 DoCmd.OpenForm stDocName, , , stLinkCriteria

use the openargs option

 DoCmd.OpenForm stDocName, OpenArgs:=Me.List78


now, use the Load event of the form "Call_Ticket"

private sub form_load()

if me.openargs & ""<>"" then
    with me.recordsetclone
         .findfirst "[Service_Tag_Number_Auto] = " & Me.OpenArgs
         if not .nomatch then
               me.bookmark=.bookmark
              else
              msgbox "Record not found!"
        end if
         
    end with
end if

end sub
0
 
Rey Obrero (Capricorn1)Commented:
change this line

stLinkCriteria = "Forms!Call_Ticket!Service_Tag_No = " & Me!List78

with

stLinkCriteria = "[Service_Tag_Number_Auto] = " & Me!List78



where are you calling this code
If stWhoCalled = "DirectAccess" Then

    Me.RecordSource = "OpenQuotes"
    Me.Caption = "You are Editing Open Quotes"
End If

Open in new window

0
 
jasgotAuthor Commented:
Rey:
It open up to the first record in the query.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jasgotAuthor Commented:
Rey:
calling the code from Private Sub Form_Open(Cancel As Integer)
0
 
jasgotAuthor Commented:
Now that Rocks! thank you!
0
 
jasgotAuthor Commented:
Works a treat!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.