[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • 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 = "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.
0
jasgot
Asked:
jasgot
  • 13
  • 7
  • 3
  • +1
2 Solutions
 
MacroShadowCommented:
I don't understand. If you want the record source to be the record you clicked on in List78, why do you on opening the form change the record source?!?
0
 
Rey Obrero (Capricorn1)Commented:
change this line

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

with

stLinkCriteria = "[Service_Tag_No] = " & Me!List78
0
 
jasgotAuthor Commented:
MacroShadow:
Because that is how it was before I decided to make this change.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
change this line

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

with

stLinkCriteria = "[Service_Tag_No] = " & Me.List78
0
 
jasgotAuthor Commented:
Rey:
That causes a "Enter Parameter Value" input box to popup.
0
 
MacroShadowCommented:
Try with out the brackets.
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
MacroShadowCommented:
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.
0
 
jasgotAuthor Commented:
MacroShadow:
Same input box.

Rey:
See attached image. Yes for the UnderScores. No underscores is the same popup.
Capture.JPG
0
 
jasgotAuthor Commented:
MacroShadow:
So where do I set the controlsource? The form has to look for that service tag number somewhere?
0
 
Rey Obrero (Capricorn1)Commented:
@jasgot

what is the Record Source of the Form "Call_Ticket"
0
 
jasgotAuthor Commented:
Rey:
It's the OpenCalls query
0
 
Rey Obrero (Capricorn1)Commented:
post the SQL statement of the " OpenCalls"  query
0
 
jasgotAuthor Commented:
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

0
 
aikimarkCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:
try this

stLinkCriteria = "[Service_Tag_Number_Auto] = " & Me.List78
0
 
jasgotAuthor Commented:
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 http://www.experts-exchange.com/Database/MS_Access/Q_28418288.html#a40017653
0
 
jasgotAuthor Commented:
Rey: It creates a new record rather than showing me the one referenced in List78
0
 
Rey Obrero (Capricorn1)Commented:
check if the Data Entry property of the form is set to YES, change it to NO
0
 
jasgotAuthor Commented:
Both forms are "No"
The Main Menu we are coming from and the Call_Ticket we are going to.
0
 
jasgotAuthor Commented:
<embarrassed font>I was using the wrong Query.
I should have been using OpenQuotes.
I'm sorry to have wasted your time. </embarrassed font>
0
 
jasgotAuthor Commented:
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.
0
 
jasgotAuthor Commented:
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

0
 
jasgotAuthor Commented:
Is it possible to reference the value I need from List78 in the Where part of the SQL code?
0
 
aikimarkCommented:
Is it possible to reference the value I need from List78 in the Where part of the SQL code?
Yes.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 13
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now