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

Posted on 2014-04-23
Last Modified: 2014-04-23
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
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.
Question by:jasgot
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40018232
change this line

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


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


Author Comment

ID: 40018240
It open up to the first record in the query.

Author Comment

ID: 40018263
calling the code from Private Sub Form_Open(Cancel As Integer)
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 40018278
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
              msgbox "Record not found!"
        end if
    end with
end if

end sub

Author Comment

ID: 40018299
Now that Rocks! thank you!

Author Closing Comment

ID: 40018300
Works a treat!

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 23 hours left to enroll

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question