Solved

Open a query, or condition

Posted on 2008-10-26
10
163 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
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Petrobras
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
OK Jeff, thanks amigo....

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

Expert Comment

by:jefftwilley
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

11 Experts available now in Live!

Get 1:1 Help Now