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
Solved

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

Posted on 2014-04-23
25
446 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 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