Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1832
  • Last Modified:

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.
0
jasgot
Asked:
jasgot
  • 4
  • 2
1 Solution
 
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
 
jasgotAuthor Commented:
Rey:
calling the code from Private Sub Form_Open(Cancel As Integer)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now