Solved

Open a query, or condition

Posted on 2008-10-26
10
169 Views
Last Modified: 2012-05-05
Hello experts, I have a qry that needs to open based on a where condtion.   The qry is opening on a double click of a cell and based on LCID but this double click event is on two different forms.  So, based on what form I am on, I need the double click event to recognize this.  
==========
here is the double click event on my two diff forms:
Private Sub Amount_DblClick(Cancel As Integer)
    DoCmd.OpenQuery "qryLCAmountHistory"
End Sub
==========
The qryLCAmountHistory SQL is like this: (take note of the OR condition at end)
I need the qry to ignore one of the two based on which form is open.  
Hope this makes sense....thank you
============
SELECT tblCompany.CoName, tblCompany.coid, tblLCAmountHistory.*
FROM tblCompany INNER JOIN tblLCAmountHistory ON tblCompany.CoID = tblLCAmountHistory.COID
WHERE (((tblLCAmountHistory.letterofcreditID)=[forms]![frmLCPerFacility]![frmLetterOfCreditSubform]![LCID])) OR (((tblLCAmountHistory.letterofcreditID)=[forms]![frmLCPerFacility]![frmLetterOfCreditDetail]![LCID]));

=======
the two forms names are: 1) frmLetterOfCreditSubform 2) frmLetterOfCreditDetail


frmLC

0
Comment
Question by:Petrobras
[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
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22809511
Hi Mike,
You'll have to modify the SQL of the query in code vs the way you're trying to do that. Access will evaluate whether a form is open or not and when it finds that one is not open, it will throw the error.

Use a dynamic QueryDef to set the SQL of the query inserting the name of the form you're on before you execute the query. That should clear up the issue.

J
0
 

Author Comment

by:Petrobras
ID: 22809573
Jeff, thank you.  I must say that I have no programming experience.  I would not be able to use a dynamic queryDef to set the SQL.  Could you show me an example of this?  i would much appreciate it.

thank you
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22823852
Mike,
It goes like this.

-------When you're on the Form frmLetterOfCreditSubform --------------------------------
Private Sub Amount_DblClick(Cancel As Integer)
Dim sSQL as string
Dim qdf As QueryDef    '<<< this is a DAO object so you need that reference
Set qdf = CurrentDb.QueryDefs("qryLCAmountHistory")   'Define the Query Object
 
sSQL = "SELECT tblCompany.CoName, tblCompany.coid, tblLCAmountHistory.*
FROM tblCompany INNER JOIN tblLCAmountHistory ON tblCompany.CoID = tblLCAmountHistory.COID
WHERE (((tblLCAmountHistory.letterofcreditID)=[forms]![frmLCPerFacility]![frmLetterOfCreditSubform]![LCID]));"

qdf.sql = sSQL

DoCmd.OpenQuery "qryLCAmountHistory"
End Sub


just use the other criteria for the other option on the other form.

now when the form opens with the name of your query as its source, it has the correct criteria


0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:Petrobras
ID: 22824028
Jeff, thank you sir...I will implement into the db later today.  I dont have Access on this computer at work.  

pb
0
 

Author Comment

by:Petrobras
ID: 22828066
HI Jeff, sorry it took awhile to get back.  
I think I have a syntax here:
FROM tblCompany INNER JOIN tblLCAmountHistory ON tblCompany.CoID = tblLCAmountHistory.COID
WHERE (((tblLCAmountHistory.letterofcreditID)=[forms]![frmLCPerFacility]![frmLetterOfCreditSubform]![LCID]));"
it highlights in red starting at the FROM to the end of the "WHERE" line.

Let me know what is next.  thanks
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 22837126
Mike,
notice I added a new variable so you can understand where to add your form specific value. I wasn't sure if it was integer or string or what...if its integer or long just declare it that way. if its string, then inside your sSQL you'll need to wrap the value with '        '

-------When you're on the Form frmLetterOfCreditSubform --------------------------------
Private Sub Amount_DblClick(Cancel As Integer)
Dim sSQL as string
Dim qdf As QueryDef    '<<< this is a DAO object so you need that reference
dim MyCriteria as ???? (is this a string or integer?)
MyCriteria = me.[LCID]

Set qdf = CurrentDb.QueryDefs("qryLCAmountHistory")   'Define the Query Object
 
sSQL = "SELECT tblCompany.CoName, tblCompany.coid, tblLCAmountHistory.*
sSQL = sSQL & " FROM tblCompany INNER JOIN tblLCAmountHistory ON tblCompany.CoID = tblLCAmountHistory.COID"
sSQL = sSQL & " WHERE tblLCAmountHistory.letterofcreditID =  " & MyCriteria

qdf.sql = sSQL

DoCmd.OpenQuery "qryLCAmountHistory"
End Sub

Let me know if you still get stuck.
J
0
 

Author Comment

by:Petrobras
ID: 22837395
Jeff, the above works but let me tell you what I need to change.  Previous to this post, I had placed a criteria for [letterofcreditID] in tblLCAmountHistory as:  forms!frmLetterOfCredit!LetterOfCreditID as it is dynamic.
However, now when I open the query in design I do not see forms!frmLetterOfCredit!LetterOfCreditID but instead see a number there which is the LetterOfCreditID.  I flip through deals and it can not save that number in the query but instead must be dynamic and always have the  forms!frmLetterOfCredit!LetterOfCreditID .  Hope that makes sense.

I think it was probably my not explaining correctly.  Probably something to do with the MYCRITERIA part at the end.
***Notice I added the IsNull part.  It is my code and there might be a better way to do it.

Private Sub Amount_DblClick(Cancel As Integer)

   If IsNull(Me!Amount) Then
        MsgBox "An L/C has not been issued.", vbInformation
            Else
        Dim sSQL As String
        Dim qdf As QueryDef    '<<< this is a DAO object so you need that reference
        Dim LetterID As Integer '(is this a string or integer?)
        LetterID = Me.[LCID]

    Set qdf = CurrentDb.QueryDefs("qryLCAmountHistory")   'Define the Query Object
 
    sSQL = "SELECT tblCompany.CoName, tblCompany.coid, tblLCAmountHistory.*"
    sSQL = sSQL & " FROM tblCompany INNER JOIN tblLCAmountHistory ON tblCompany.CoID = tblLCAmountHistory.COID"
    sSQL = sSQL & " WHERE tblLCAmountHistory.letterofcreditID =  " & LetterID

    qdf.SQL = sSQL

    DoCmd.OpenQuery "qryLCAmountHistory"
    End If
End Sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22857417
Mike, you would repeat the same code, changing the source for the criteria variable on the other form. Creating dynamic SQL as you've found doesn't store the criteria as a literal statement like "(tblLCAmountHistory.letterofcreditID)=[forms]![frmLCPerFacility]![frmLetterOfCreditSubform]![LCID])) "
instead, the actual criteria value is used. Each time you select an item on the subform, that value is placed into the query.

This gets rid of the issue you were having for which form you were on.

I'm not sure what you're asking in the last comment. Does this method not work for either situation?
0
 

Author Comment

by:Petrobras
ID: 22858345
OK Jeff, thanks amigo....

(oh and fyi, just for the record my name is Tony...)
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22859559
Ha! That's funny! Sorry, Tony. And here I thought all this time ( 2 years almost ) that your name was mike. My bad.
Let us know if you need anything else.
Later!
J
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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