asked on

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 = "OpenCalls"
    Me.Caption = "You are Editing Open Calls"
End If

Open in new window

The problem is that I am seeing the first record in the "OpenCalls" query defined in Me.RecordSource

I want to see the record specified by the List78 value from the form where I clicked.

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jasgot

ASKER

MacroShadow:
Because that is how it was before I decided to make this change.

Rey Obrero (Capricorn1)

change this line

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

with

stLinkCriteria = "[Service_Tag_No] = " & Me.List78

jasgot

ASKER

Rey:
That causes a "Enter Parameter Value" input box to popup.

Joe Howard

Try with out the brackets.

Rey Obrero (Capricorn1)

what does the input box say?

check the name of the field "Service_Tag_No" , does it have an underscore?

try this too

stLinkCriteria = "[Service Tag No] = " & Me.List78

Joe Howard

Anyhow, if you want the records to be filtered based on the listbox value, you have to remove the code that populates the form when it opens.

jasgot

ASKER

MacroShadow:
Same input box.

Rey:
See attached image. Yes for the UnderScores. No underscores is the same popup.
Capture.JPG

jasgot

ASKER

MacroShadow:
So where do I set the controlsource? The form has to look for that service tag number somewhere?

Rey Obrero (Capricorn1)

@jasgot

what is the Record Source of the Form "Call_Ticket"

jasgot

ASKER

Rey:
It's the OpenCalls query

Rey Obrero (Capricorn1)

post the SQL statement of the " OpenCalls" query

jasgot

ASKER

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.Parts, Main.[Project Name], Main.Total, Main.EstHours, Main.Keep4Today
FROM Main
WHERE (((Main.[Call_Close Time]) Is Null) AND ((Main.Type_Support)<>"Proposal / Quote"))
ORDER BY Main.CompanyName;

Open in new window

aikimark

I think you missed the comment I posted in your prior related question:

stLinkCriteria = "[Service_Tag_No] = " & List78.Column(3)

Open in new window

http:Q_28418058.html#a40017394

Rey Obrero (Capricorn1)

try this

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

jasgot

ASKER

aikmark:
You provided that because you thought I was binding the wrong column. I am not.
Besides, it ask me to input a value for Service_Tag_No. See the image attached to https://www.experts-exchange.com/questions/28418288/stLinkCriteria-is-not-causing-the-DoCmd-OpenForm-to-open-the-right-record.html?anchorAnswerId=40017653#a40017653

jasgot

ASKER

Rey: It creates a new record rather than showing me the one referenced in List78

Rey Obrero (Capricorn1)

check if the Data Entry property of the form is set to YES, change it to NO

jasgot

ASKER

Both forms are "No"
The Main Menu we are coming from and the Call_Ticket we are going to.

jasgot

ASKER

<embarrassed font>I was using the wrong Query.
I should have been using OpenQuotes.
I'm sorry to have wasted your time. </embarrassed font>

jasgot

ASKER

Actually it's not working. I just happened to choose a record in List78 that was the first record in the OpenQuotes Query.

I'm still not getting the record I want.

jasgot

ASKER

Here is the right 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

jasgot

ASKER

Is it possible to reference the value I need from List78 in the Where part of the SQL code?

aikimark

Is it possible to reference the value I need from List78 in the Where part of the SQL code?

Yes.