Solved

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

Posted on 2014-04-23
25
454 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 = "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
Comment
Question by:jasgot
[X]
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
  • 13
  • 7
  • 3
  • +1
25 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 40017588
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 40017608
change this line

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

with

stLinkCriteria = "[Service_Tag_No] = " & Me!List78
0
 

Author Comment

by:jasgot
ID: 40017610
MacroShadow:
Because that is how it was before I decided to make this change.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Expert Comment

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

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

with

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

Author Comment

by:jasgot
ID: 40017618
Rey:
That causes a "Enter Parameter Value" input box to popup.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40017631
Try with out the brackets.
0
 
LVL 120

Expert Comment

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

Expert Comment

by:MacroShadow
ID: 40017637
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
 

Author Comment

by:jasgot
ID: 40017653
MacroShadow:
Same input box.

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

Author Comment

by:jasgot
ID: 40017657
MacroShadow:
So where do I set the controlsource? The form has to look for that service tag number somewhere?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40017667
@jasgot

what is the Record Source of the Form "Call_Ticket"
0
 

Author Comment

by:jasgot
ID: 40017670
Rey:
It's the OpenCalls query
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40017682
post the SQL statement of the " OpenCalls"  query
0
 

Author Comment

by:jasgot
ID: 40017694
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40017695
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40017698
try this

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

Author Comment

by:jasgot
ID: 40017702
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
 

Author Comment

by:jasgot
ID: 40017706
Rey: It creates a new record rather than showing me the one referenced in List78
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40017710
check if the Data Entry property of the form is set to YES, change it to NO
0
 

Author Comment

by:jasgot
ID: 40017719
Both forms are "No"
The Main Menu we are coming from and the Call_Ticket we are going to.
0
 

Author Closing Comment

by:jasgot
ID: 40017767
<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
 

Author Comment

by:jasgot
ID: 40017777
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
 

Author Comment

by:jasgot
ID: 40017781
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
 

Author Comment

by:jasgot
ID: 40017784
Is it possible to reference the value I need from List78 in the Where part of the SQL code?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40017831
Is it possible to reference the value I need from List78 in the Where part of the SQL code?
Yes.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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