Solved

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

Posted on 2014-04-23
25
436 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now