[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Form

Posted on 2011-11-01
21
Medium Priority
?
338 Views
Last Modified: 2012-05-12
Experts,

I am making this query form.
I have 2 unbound text boxes txtCoName, txtLCNo.
A user is to enter either a Co Name or and LC No and hit button and form opens with criteria.
The form with the results is frmLCissued_TradeChannel

I know this is a little tricky.  I did get this idea from an expert on this site awhile ago but something is missing.

Th e criteria I am after:
If the txtCoName is null and the txtLCNo is populated I want the form to open to those parameters.  But if the LCNo is null and the txtCoName is populated I need the form to open to those parameters.  

I thought I had it correct in the below SQL but seems like something is missing.
Do you see it?
 (The pic might make more sense)


SELECT tblEndUser.[End User], tblLetterOfCredit.LCNo, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.Amount, tblLetterOfCredit.LCtype
FROM tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID

WHERE (((tblEndUser.[End User]) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName],"") & "*") AND ((tblLetterOfCredit.LCNo) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo],"") & "*"))
OR (((tblEndUser.[End User]) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName],"") & "*" And (tblEndUser.[End User])<>"false") AND ((tblLetterOfCredit.LCNo) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo],"") & "*"
And (tblLetterOfCredit.LCNo)<>"false")
AND ((IsNull([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo]))=True)
AND ((IsNull([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName]))=True));
 screen shot 1 of 2untitled.JPG
0
Comment
Question by:pdvsa
  • 11
  • 10
21 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 37062574
Is this the query that returns the recordset for frmLCissued_TradeChannel?

If so, leave those criteria out of the forms recordset.  Instead, use a command button on the first form, to set the criteria, something like:

Private Sub cmd_LCIssued_TradChannel_Click

    Dim strCriteria as string

    if Len(me.txtCoName & "") > 0 then
        strCriteria = "tblEndUser.[End User] = " & chr$(34) & me.txtCoName & chr$(34)
    endif

    if len(me.txtLCNo & "") > 0 then
        if LEN(strCriteria) > 0 then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is numeric, not text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & me.txtLCNo
    endif

    Docmd.Openform "frmLCissued_TradeChannel",,,strCriteria

End Sub
0
 

Author Comment

by:pdvsa
ID: 37062787
ahhh...that sounds like a great idea.  

Let me do that...

thank you
0
 

Author Comment

by:pdvsa
ID: 37062846
fyed, I have one small request.  The LCNo is actually text and not numeric.  I have letters in the LCno as well as numbers so I have to have it as text.

How to modify your code?

much appreciated sir...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 37062950
You need to wrap the value of the LCNo textbox in quotes, similar to the way I did with txtCoName

strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & chr$(34) & me.txtLCNo & chr$(34)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37063050
Personally, I actually use a function (fnWrap) to add the quotes (single or double), or even # for dates to strings.  
Public Function fnWrap(WrapWhat As Variant, Optional WrapWith As Variant = """") As String

    fnWrap = WrapWith _
           & IIf(Nz(WrapWith, "") = Chr$(34) Or Nz(WrapWith, "") = Chr$(39), _
                 Replace(WrapWhat, Nz(WrapWith, ""), Nz(WrapWith, "") & Nz(WrapWith, "")), _
                 WrapWhat) _
           & WrapWith
    
End Function

Open in new window


This accepts a value (WrapWhat) and a parameter which defines what character(s) to wrap around the value that was passed.  The WrapWhat is defined as a variant to allow you to pass a null value and get that value wrapped in the appropriate delimiters.  One of the nice things about this function is that if WrapWhat contains characters like the WrapWith characters (single or double quotes) it will actually replace those values in the text with two of that same character, which is recognized by most databases as a single instance.

In your case, I would use it like:

strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & fnWrap(me.txtLCNo)

With a date field, I would do something like:

strCriteria = strCriteria & "[DateField] = " & fnWrap(me.txtStartDate, "#")

0
 

Author Comment

by:pdvsa
ID: 37063485
fyed:
I have to use a different SQL now.  I thought that it would not make a difference.  
When I click the button to open the form, I do not get any results.  

With this new SQL of the form that is opening with the results (shown below), do you see any issues?  I do not see any.  The tables referenced are the same. The unbound text boxes names are correct too.


SELECT tblEndUser.[End User], tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType, tblLetterOfCredit.Comments

FROM (tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37063533
First thing I would do is verify that the SQL actually returns records, when not linked to the form.

I hadn't thought about it, but in the Click event, when you are building the strCriteria, you need to remove any reference to the table that the fields are coming from.  So it should look more like:

Private Sub cmd_LCIssued_TradChannel_Click

    Dim strCriteria as string

    if Len(me.txtCoName & "") > 0 then
        strCriteria = "[End User] = " & chr$(34) & me.txtCoName & chr$(34)
    endif

    if len(me.txtLCNo & "") > 0 then
        if LEN(strCriteria) > 0 then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is numeric, not text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & chr$(34) & me.txtLCNo & chr$(34)
    endif

    Docmd.Openform "frmLCissued_TradeChannel",,,strCriteria

End Sub

Open in new window


Also, in your original query you were using Like in the query.  I was unsure whether this was intentional because you wanted to be able to enter part of the Co Name, or whether it was because you wanted to return all records for that field in the associated textbox was empty (but I assumed it was the latter).
0
 

Author Comment

by:pdvsa
ID: 37064317
fyed:
<First thing I would do is verify that the SQL actually returns records, when not linked to the form.
It does return records. I tested this.  The text i have in the unbound fields is actual data in the table too.  

I still am not getting any results though.  
below is how I have it now.  
Only change is the form that is opening.  I did change that.  
I tried it with and witout the tblLetterOfCredit (bolded below) as you did remove the tblEndUser.

Any other suggestions?  The names are correct on the unbound fields.  

Private Sub cmdOpenRpt_Click()
   
    Dim strCriteria As String
 
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
 
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
 
    DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
 


End Sub


here is the sql again (it is the same from above though) from the form that is opening to the criteria on the query form.

SELECT tblEndUser.[End User], tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType, tblLetterOfCredit.Comments

FROM (tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37064406
Oops,  you were right to remove the "tblLetterOfCredit." refernece.

The next thing I would do is:

1.  Make sure the forms "filterOnLoad" property in the Data tab of the properties dialog is set to no.
2.  Use the following code, it adds a debug.print to print the value of strCriteria to the debug window, and also opens the form without the criteria.

 
Private Sub cmdOpenRpt_Click()
    
    Dim strCriteria As String
  
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
  
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If

    debug.print strCriteria
    'DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient"

End Sub

Open in new window


If that opens the form with data, then in the immediate window, type:

forms!frmLCIssued_toClient.filter = "paste the strCriteria here"
forms!frmLCIssued_toClient.filterON = true

NOTE: on the filter line above, make sure you use the criteria string that the debug command printed.

If that doesn't work there may be a problem with strCriteria.  Post that string back in the next post.
0
 

Author Comment

by:pdvsa
ID: 37064880


forms!frmLCIssued_toClient.filter = "paste the strCriteria here"
forms!frmLCIssued_toClient.filterON = true

Please see pic.  I closed and reopened teh form with that Immediate window info and it gave me an syntax error 'Missing Operator' in 'gs cal'  upon opening of the form.

You can see the other strcriteria there that teh system added based on what I input in the query form.

What do you think now?
 Immediate Windowuntitled.JPG
0
 

Author Comment

by:pdvsa
ID: 37064907
did not completly finish that

<If that opens the form with data, then in the immediate window, type:
it does open but to all data and not based on the filter.

the filter on load is turned off.  
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37064966
Try:

forms!frmLCIssued_toClient.filter = "[End User] = 'gs cal'"
forms!frmLC_Issued
0
 

Author Comment

by:pdvsa
ID: 37065458
dangit...still get syntax error on End User
here is copy past of immediate window.  
What next?  
I iknow it is difficult if you cant see the db.

[End User] = "gs cal"
[LCNo] = "ctcs"
[LCNo] = "ctcs"
forms!frmLCIssued_toClient.filter = "[End User] = 'gs cal'"
forms!frmLCIssued_toClient.filterON = true
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37065540
Are you saying that a single debug.print strCriteria is returning three rows, like:

[End User] = "gs cal"
[LCNo] = "ctcs"
[LCNo] = "ctcs"

You did say that the query runs fine, and that the form opens (unfiltered) properly, correct?

Can you copy and paste the current code you are using.
0
 

Author Comment

by:pdvsa
ID: 37066031
I think I had some corruption.

I get this in the immediate window now:

[End User] = "gs cal"  and this is what is in the query form.

here is the button code:
Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
   
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
   
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
 
    Debug.Print strCriteria
    'DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient"
 
End Sub
0
 

Author Comment

by:pdvsa
ID: 37066042
I still do not have any results in the data form but that record "GS Cal" is in the data set under End User.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37066509
is the entry in the recordset exactly "GS Cal" or does it have preceding or following characters.

Try:

forms!frmLCIssued_toClient.filter = "[End User] Like '*gs cal*'"

do you have an Option Compare statement at the top of your code module? If so, what does it say?

Most people use:

Option Compare Database
0
 

Author Comment

by:pdvsa
ID: 37067133
fyed:  
<is the entry in the recordset exactly "GS Cal" or does it have preceding or following characters.
It does not have any preceding characters but does have some after.  It is definitely a record though.  

I do have option compare in both forms (filter and form opening).

I figure it is something that  has been overlooked.  I appreciate you sticking with me on this because I really want this to work. I have been looking for a "clean" way to use a filter form with >1 unbound field.  

Maybe if you see the db  it will be more clear.
I have ul it and the filter form opens auto.  

Please take a crack at it when you get a sec.  I checked the names and all and i dont believe it is something simple like that.  I hope it is not something stupid I did ;) but that is very possible.  

Please type in "GS Cal" for GS Caltex and I believe 51 records appear which is all the records but for GS Cal it should be only 2 or so.  

thanks for spending the time...really appreciate the expert help.

DatabaseNew.accdb
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37067426
OK< as I suspected, you really did want to use the LIKE operator in your criteria string for the [End User] field.  So, try the following code; it worked for me.
Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
    
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] Like " & Chr$(34) & "*" & Me.txtCoName & "*" & Chr$(34)
    End If
    
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
  
    Debug.Print strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
  
End Sub

Open in new window


This will actually filter on records that contain "gs cal" anywhere in the [End User] field.  If you want it to only select those records where "gs cal" appears at the beginning of the field, then use:

strCriteria = "[End User] Like " & Chr$(34) & Me.txtCoName & "*" & Chr$(34)
0
 

Author Comment

by:pdvsa
ID: 37067485
yessir that was it!   I was thinking that the LIKE was in the code but I did not read it very closely.  

thank you for helping me.  I would give you more pts if I could.

pdvsa
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37069142
I'm just glad we got it figured out.
0

Featured Post

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!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

872 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