Solved

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

Posted on 2014-04-23
25
432 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 26

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 26

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 26

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
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…

746 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

13 Experts available now in Live!

Get 1:1 Help Now