Solved

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

Posted on 2014-04-23
6
1,568 Views
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
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
Comment
Question by:jasgot
  • 4
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40018232
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
 

Author Comment

by:jasgot
ID: 40018240
Rey:
It open up to the first record in the query.
0
 

Author Comment

by:jasgot
ID: 40018263
Rey:
calling the code from Private Sub Form_Open(Cancel As Integer)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 120

Accepted Solution

by:
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
               me.bookmark=.bookmark
              else
              msgbox "Record not found!"
        end if
         
    end with
end if

end sub
0
 

Author Comment

by:jasgot
ID: 40018299
Now that Rocks! thank you!
0
 

Author Closing Comment

by:jasgot
ID: 40018300
Works a treat!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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