Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2014-04-23
Medium Priority
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
  • 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)
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

578 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