• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Open a query, or condition

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
Petrobras
Asked:
Petrobras
  • 5
  • 5
1 Solution
 
jefftwilleyCommented:
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
 
PetrobrasAuthor Commented:
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
 
jefftwilleyCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
PetrobrasAuthor Commented:
Jeff, thank you sir...I will implement into the db later today.  I dont have Access on this computer at work.  

pb
0
 
PetrobrasAuthor Commented:
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
 
jefftwilleyCommented:
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
 
PetrobrasAuthor Commented:
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
 
jefftwilleyCommented:
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
 
PetrobrasAuthor Commented:
OK Jeff, thanks amigo....

(oh and fyi, just for the record my name is Tony...)
0
 
jefftwilleyCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now