Solved

Open a query, or condition

Posted on 2008-10-26
10
164 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
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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

17 Experts available now in Live!

Get 1:1 Help Now