Solved

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

Posted on 2014-04-23
25
441 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
object variable or with block not set 6 28
deduplicating based on criteria 2 21
Reference Controls on subforms 7 27
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 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