Can anyone take a look and guide me where is my mistake?
Code:
Dim strRS As String
Dim lngLen As Long
Dim varItem As Variant
Dim strProd As String
strRS = "SELECT text_en, text_desc FROM qLetter WHERE"
If Not IsNull(Me.cboRecipient) Then
strRS = strRS & " recipient_id = " & Me.cboRecipient & " AND "
End If
If Not Me.lboUser.MultiSelect = 0 Then
For Each varItem In Me.lboUser.ItemsSelected
strProd = DLookup("id", "tblLetterProd", "recipient_id =" & Me.cboRecipient & " AND product_name = """ & Me.lboUser.Column(2, varItem) & """")
strRS = strRS & " product_name_id = " & strProd & " OR "
Next
End If
lngLen = Len(strRS) - 4
strRS = Left$(strRS, lngLen)
Me.lbotext.RowSource = strRS
Me.lbotext.Requery
Basically, I want the lbotext to populate all value based on the combobox, and single or multiple selected value in the listbox.
The code works except when the user select an item in lboUser, it still list all the items from the cboRecipient.
strRS = "SELECT text_en, text_desc FROM qLetter WHERE recipient_id = 1 AND product_name_id = 1 OR product_name_id = 2"
AND has precedence so what you really want might be
Code:
strRS = "SELECT text_en, text_desc FROM qLetter WHERE recipient_id = 1 AND [COLOR="Red"]([/COLOR] product_name_id = 1 OR product_name_id = 2[COLOR="red"])[/COLOR]"
i.e., parenthesis around the ORed part. If this is the case I suggest you take strRS out of the loop that forms the ORed part and put the two parts together with the parenthesis outside the loop.
strRS = "SELECT text_en, text_desc FROM qLetter WHERE recipient_id = 1 AND product_name_id = 1 OR product_name_id = 2"
AND has precedence so what you really want might be
Code:
strRS = "SELECT text_en, text_desc FROM qLetter WHERE recipient_id = 1 AND [COLOR="Red"]([/COLOR] product_name_id = 1 OR product_name_id = 2[COLOR="red"])[/COLOR]"
i.e., parenthesis around the ORed part. If this is the case I suggest you take strRS out of the loop that forms the ORed part and put the two parts together with the parenthesis outside the loop.
I didn't mention this before but an alternative to ORing is using the IN Condition. Would something like the following work for you.
Code:
Dim strRS As String
Dim lngLen As Long
Dim varItem As Variant
Dim strProd As String
Dim strProdCSV As String
strRS = "SELECT text_en, text_desc FROM qLetter WHERE"
If Not IsNull(Me.cboRecipient) Then
strRS = strRS & " recipient_id = " & Me.cboRecipient & " AND "
End If
If Not Me.lboUser.MultiSelect = 0 Then
For Each varItem In Me.lboUser.ItemsSelected
strProd = DLookup("id", "tblLetterProd", "recipient_id =" & Me.cboRecipient & " AND product_name = """ & Me.lboUser.Column(2, varItem) & """")
'form comma separated list of product ids
strProdCSV = strProdCSV & strProd & ","
Next
End If
'remove trailing comma
strProdCSV = Left(strProdCSV, Len(strProdCSV) - 1)
strRS = strRS & "product_name_id IN ( " & strProdCSV & " )"
Me.lbotext.RowSource = strRS
Me.lbotext.Requery
I didn't mention this before but an alternative to ORing is using the IN Condition. Would something like the following work for you.
Code:
Dim strRS As String
Dim lngLen As Long
Dim varItem As Variant
Dim strProd As String
Dim strProdCSV As String
strRS = "SELECT text_en, text_desc FROM qLetter WHERE"
If Not IsNull(Me.cboRecipient) Then
strRS = strRS & " recipient_id = " & Me.cboRecipient & " AND "
End If
If Not Me.lboUser.MultiSelect = 0 Then
For Each varItem In Me.lboUser.ItemsSelected
strProd = DLookup("id", "tblLetterProd", "recipient_id =" & Me.cboRecipient & " AND product_name = """ & Me.lboUser.Column(2, varItem) & """")
'form comma separated list of product ids
strProdCSV = strProdCSV & strProd & ","
Next
End If
'remove trailing comma
strProdCSV = Left(strProdCSV, Len(strProdCSV) - 1)
strRS = strRS & "product_name_id IN ( " & strProdCSV & " )"
Me.lbotext.RowSource = strRS
Me.lbotext.Requery
If only select the combobox without anything in listbox, I get this:
Code:
strRS = SELECT text_fr, text_desc FROM qLetter WHERE recipient_type_id = 1
If I select the listbox, i will get this, which is right:
Code:
strRS = SELECT text_fr, text_desc FROM qLetter WHERE recipient_type_id = 1 AND product_name_id IN ( 11,10 )
But if the recipient_type_id = 1 does not have product_name_id 12, then if I click on the listbox item of 12, it should populate blank right? but in this case, it just still show:
Code:
strRS = SELECT text_fr, text_desc FROM qLetter WHERE recipient_type_id = 1
Since the DLookUp has recipient_id =" & Me.cboRecipient in its condition If the recipient_type_id = 1 does not have product_name_id 12 then DLookup should return Null. I'm don't understand why that's not raising an error but in any case the id wouldn't be in the comma separated list so the result strRS is what would be expected. I guess you want
Code:
strRS = SELECT text_fr, text_desc FROM qLetter WHERE recipient_type_id = 1 AND product_name_id IN ( 12 )
I wonder if this could be made less complicate by changing the row source of the listbox so that this DLookup is eliminated. I suggest trying to create a query joining the applicable tables for the listbox row source that has the id's in it to start with. Typically would would want id in the first column. If you use the wizard to create the listbox from the query it will hide the id for you. If you upload your database I see if I can figure this out.
Also I suggest putting the cboRecipient in the criteria for the query of the row source of the listbox and requerying the listbox in the afterupdate of the cboRecipient combo box. That would reduce the listbox to the applicable values.
To construct SQL on the fly I like to use properties that depend on other properties to construct exactly what I need, directly from the values in the controls.
To start off, we know we need this outcome, right?
Code:
Property Get SQLFinal() As String
SQLFinal = "SELECT text_en, Text_desc FROM qLetter " & Me.SQLWhere
End Property
Now we need to figure out the WHERE clause, and it has two parts, right?
Code:
Property Get SQLWhere As string
dim typeID as string
dim nameID as string
typeID = Me.SQLTypeID
nameID = Me.SQLNameID
if len(typeID) and len(nameID) then
[COLOR="Green"] 'both are present[/COLOR]
SQLWhere = "WHERE " & typeID & "AND " & nameID
elseif len(typeID & nameID) then
[COLOR="Green"] 'at least one is present[/COLOR]
SQLWhere = "WHERE " & typeID & nameID
else
[COLOR="Green"] 'there is no where clause[/COLOR]
end if
end property
And now we need the parts of the where clause . . .
Code:
Property Get SQLTypeID as string
[COLOR="Green"]' returns a partial WHERE clause, or an empty string, as the case may be[/COLOR]
If Not IsNull(Me.cboRecipient) Then
SQLTypeID = "recipient_type_id = " & Me.cboRecipient & " "
End If
End Property
Property Get SQLNameID as string
[COLOR="Green"]' returns a partial where clause based on multiple selected items in the list
' or an empty string if nothing selected
[/COLOR] dim var
dim tmp as string
For Each var In Me.lboUser.ItemsSelected
tmp = tmp & "," & var
Next
if Len(tmp) then tmp = mid(tmp, 2)[COLOR="Green"] 'drop the leading ","[/COLOR]
if Len(tmp) then SQLNameID = "product_name_id IN ( " & tmp & " ) "
End Property
So now anytime we request a value from Me.SQLFinal, that procedure will call the other procedures, and each of those will check the values in the controls to come to solution, so now we have a very responsive SQL generator.
And now all we need to do to update the result is run this...
With this approach, breaking your SQL construction up in into small, logical steps, you can relatively easily construct very complicated and responsive SQL statements.
Mark, I'm curious about the pros and cons of that method vs a function. Is it just personal preference, or ? To me it seems simpler to have it all in a function, since it would all have to run anyway. I'll admit my ignorance regarding using properties, so I'm not criticizing, I'm trying to learn. This old dog can learn new tricks.
Paul:
I like short, succinctly named procedures, functions or properties. My rule of thumb is that a procedure in code should do one job, and be named such that what it does and how it does it, should be obvious in < 10 seconds. I think each of those properties I wrote satisfy those requirements, but even now looking at this again I would rename this . . .
Code:
Property Get SQLTypeID as string
[COLOR="Green"]'to[/COLOR]
Property Get SQLWhereTypeID as string
. . . so that it's clearer at a glance that it is a subroutine of the . . .
Code:
Property Get SQLWhere As String
But properties are easier to debug because they calculate automatically in the locals window, so if you have a object variable--including an Access.Form object--the locals windows evaluates all the properties of the object and displays the type and the value, even if the property exposes another class!!! Any class sub-property of a parent class is a new node in the tree, so you can drill down into layer after layer, and everything is there, live, ready to be inspected.
To do a simple test of this, create and open a DAO.Recordset object . . .
Code:
dim rst as dao.recordset
set rst = currentdb.openrecords("YourTable")
with rst
do while not .eof
[COLOR="Green"] 'set a breakpoint here
'and check out the rst object in the locals window[/COLOR]
.movenext
loop
.close
end with
If you look at the 'rst' object in the locals window. It is a tree and if you click to expand the node, each row in that tree view is a property of the DAO.Recordset class. Subs and Functions DO NOT APPEAR in this view, so you can find the .RecordCount property, but you won't see the .MoveNext or .NextRecordset methods. To expose a value then as a property of a class offers a very rich advantage, particulary if the property exposes another class. Take a look at the .Fields collection of the Recordset. It is also a tree, because it is also a class exposed as a Property, and if you expand that node, each row in that branch is a Property of the Fields class. And in there are a collection of DAO.Field objects, and you can drill right down to each Field.Value if you want to. And your custom classes have all this same IDE infrastructure available, if you expose your data using Properties.
Also, if a Property returns an error, the locals windows shows the Err.Description without killing your code. Open a stand-alone access form, assign it to a variable, and check out the Access.Form.Parent property. You know that would cause an error in code, but in the locals window, looking at your instance of the Form class, the Form.Parent property just shows the Err.Description.
And that is the difference between Functions and Properties in an OOP context in the VBA IDE. Short procedures are always good, IMO, but classes--and Properties exposing data and other classes--offer some pretty remarkable programming power and debugging opportunities.
Does that make sense? Let me know if you have other questions too. I think this is a really interesting and under-used or under-exposed part of VBA programming. Class modules and some OOP principles can be used to very good effect in VBA.
Since the DLookUp has recipient_id =" & Me.cboRecipient in its condition If the recipient_type_id = 1 does not have product_name_id 12 then DLookup should return Null. I'm don't understand why that's not raising an error but in any case the id wouldn't be in the comma separated list so the result strRS is what would be expected. I guess you want
Code:
strRS = SELECT text_fr, text_desc FROM qLetter WHERE recipient_type_id = 1 AND product_name_id IN ( 12 )
I wonder if this could be made less complicate by changing the row source of the listbox so that this DLookup is eliminated. I suggest trying to create a query joining the applicable tables for the listbox row source that has the id's in it to start with. Typically would would want id in the first column. If you use the wizard to create the listbox from the query it will hide the id for you. If you upload your database I see if I can figure this out.
I have cleanup all the useless data, and here is the database with the issue.
Steps:
If you select recipient, Spouse
Then you select English
It will populate 2 rows of data in the "Option" listbox.
If you select the first rows of Beneficiary, which is "RRSP", the Option listbox still shows data which is not suppose to right?
However, if you select the second row only, it will display the right Options.
It will be appreciate if you can take a look and help me to figure out the issue.
I had to import the objects from your database into a new database to get the debugger working. With that working when I choose "RRSP" it results in an invalid use of Null error because this expression
is returning Null because "RRSP" doesn't existing in the tblLetterProd table. So if you add some entries in that table you might get better results but this problem seems to indicate some sort of structural problem. I can't figure out what you need to do. Could you explain in plain words what you are trying to accomplish.
I had to import the objects from your database into a new database to get the debugger working. With that working when I choose "RRSP" it results in an invalid use of Null error because this expression
is returning Null because "RRSP" doesn't existing in the tblLetterProd table. So if you add some entries in that table you might get better results but this problem seems to indicate some sort of structural problem. I can't figure out what you need to do. Could you explain in plain words what you are trying to accomplish.
Thanks for your taking your time to help me out with this issue.
What I am trying to accomplish is to cascade the [Options] depending on the Product that the beneficiary has. It's kinda a go by elimination of choices for the user to choose before they can generate a letter. Because there are so many different product type : RRSP, DCPP, DBPP, TFSA, etc and for each recipient. The results displayed in the Options are the [Field] in Word that I will need to map them later.
So basically, there could be 50 differnt choices of [Field] for all Products, but by selection the beneficiary, it will capture the Product that this beneficiary has, and only display in the Options the necessary [Field] to choose.
Example:
If the user select a beneficiary that has only RRSP, I don't want to display the Options for DCPP.
If the user select multiple beneficiary that has RRSP and DCPP, then we will display the Options that contains RRSP, DCPP.
I am not seeing what the product type determines. If you consider the tables tblLetterRecipient and tblLettterProd and their relationships the recipient_type (Spouse or Member) determines the product type. That doesn't make sense to me.
It appears that you want to select the recipient type, the language, and the product and generate a letter. Could you explain what effect each of these have on the letter?
The product type, recipient, etc are prefilled by me. There is no addition to the data by the user.
Yes, they choose the recipient, language, and the beneficiary then generate a letter.
The option are the "autotext" that is already built in the Word building blocks. So by selecting the autotext, it will create a Word document and insert all the selected autotext.
Current process now:
People open the Word document, and manually type in the autotext name.
Now they will just select from the access list and it will automatically insert to Word.
However, since there are so many autotext, i want to group them per beneficiary selection (by product).
First I suggest you consider the evils of lookup fields. I think they really obscure the structure and this lead to design errors. I'd get rid of them. One thing that seems to be wrong with your structure is the lack of a single table that defines what the autotext should be. If I understand your requirement these are defined by the recipient type, the product, and the language. There should be a table with these three attributes and the autotext. Please open the attached database and look at the tblLetterAutoText in design view. I set this table up that way. Note that the product is short text. I see no point in trying to assign an autonumber to this anywhere as that's not how it is in the tblBene table. The language is a number data type which matches the option group on the form.
If you look at the FilterList code in the sfLetter form module you will see that there is only one initial strRS and the language has been moved to the WHERE clause where is should be. Also the DLookup has been removed from the loop that forms the comma separated list. Instead of ids the list is made up of the left part of the product name.
So I suggest restructuring your data something like this. If not this then at least in normal form and without the lookup fields in the tables. If you have a lot of data that needs to be convert to the new structure let me know and I'll help you with the queries you will need to do this.
