Solved

Open a query, or condition

Posted on 2008-10-26
10
167 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 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