Convert large IIF Expression into a Function.

[jdusn1978]

Convert large IIF Expression into a Function.

cboboxID has options 1 through 19. There are 25 chkboxes. The text behind the 25 checkboxes would change slightly depending on the cboboxID selected.

The point of the function would be to return text to fill a memo field/textbox in my database by translating the combination of the selections made from the cboboxID, the 25xcheckboxes and 1x freetextfield into text that gets sent to the person fulfilling the request.

This was obviously too large and not the correct, but below is what my newbie brain created so far:

IIF([cboboxID]=1 And [chkbox1]=True,”chkbox1 standard text:”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox1a]=True,”-chckbox1a standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox1b]=True,”-chkbox1b standard text:”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox1c]=True,”-chckbox1c standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2]=True,”chckbox2 standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2a]=True,”-chckbox2a standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2b]=True,”-chckbox2b standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2c]=True,”-chckbox2c standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2d]=True,”-chckbox2d standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2e]=True,”-chckbox2e standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2f]=True,”-chckbox2f standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox2g]=True,”-chckbox2g standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox3]=True,”chckbox3 standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox4]=True,”chckbox4 standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox4a]=True,”-chckbox4a standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox4b]=True,”-chckbox4b standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox4c]=True,”-chckbox4c standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox5]=True,”chckbox5 standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox5a]=True,”-chckbox5a standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox5b]=True,”-chckbox5b standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox5c]=True,”-chckbox5c standard text”,Null) & Chr(13) & Chr(10) &
IIF([cboboxID]=1 And [chkbox6]=True,”-chckbox6 standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox6a]=True,”-chckbox6a standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox6b]=True,”-chckbox6b standard text”,Null) & Chr(13) & Chr(10) & IIF([cboboxID]=1 And [chkbox6c]=True,”-chckbox6c standard text”,Null) & Chr(13) & Chr(10) &
IIF([cboboxID]=2 And [chkbox1]=True,”chckbox1 standard text”,Null) & Chr(13) & Chr(10) & [additionalinfof reetext]

And on and on for each of the 19 cboboxID’s and possible outcomes in order to create this text:
_______________ _______________ ___
Customer has chosen the following:

Chkbox1 standard text:
- Chkbox1a standard text.
- Chkbox1b standard text.

Chkbox2 standard text:
- Chkbox2a standard text.
- Chkbox2b standard text.
- On and on based on the selections made.

Additional info:
Additional info free text here.
_______________ _______________ __

Also, what I created using - Chr(13) & Chr(10) - doesn’t remove spaces in the text when the return is Null. How can I include newlines and correct space formatting within a function? I ended up with:

Customer has chosen the following:

Chkbox1 standard text:
-Chkbox1a standard text.
<weird space>
-Chgkbox1c Standard text.

I think the key might be to create a separate function for each cboboxID. So, 19 different functions,

[twinnyfo]

jdusn1978,

I'm not quite sure what you mean by

cboboxID has options 1 through 19. There are 25 chkboxes. The text behind the 25 checkboxes would change slightly depending on the cboboxID selected.

But, what I think you mean is that cboboxID's row source has 19 options. And, based upon which check boxes were checked, different text would be displayed in the memo, based on the checkboxes? Again, I'm still not sure exactly what that means.

However, you could create a function that manages this memo creation:

Private Function CreateMemo()
On Error GoTo EH
    Dim strMemo As String

    strMemo = "Customer has chosen the following:" & vbCrLf & vbCrLf

    Select Case Me.cboboxID
        Case 1
            If Me.chkbox1 Then
                strMemo = strMemo & "Chkbox1 standard text:" & vbCrLf
            Else
                strMemo = strMemo & ""
            End If
            If Me.chkbox1a Then
                strMemo = strMemo & "-chkbox1a standard text:" & vbCrLf
            Else
                strMemo = strMemo & ""
            End If

[etc.]

......

        Case 2
[etc.]

......


    End Select


    Me.txtMemoField = strMemo

    Exit Function
EH:
    MsgBox "There was an error creating the memo!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Function
End Function

However, I'm still not sure what the "Chkbox1 standard text:" is supposed to mean, as check boxes only have a True or False Value. However, if you have a series of text values that are added to your memo based on the 19 options and the selection of the 25 various check boxes, then this would still work.

Once you complete this function, just add this into the AfterUpdate property of all the controls:

=CreateMemo()

Hope this hepps--if I understood your question properly.

:-)

[jdusn1978]

Thank you and I'm looking forward to trying this tomorrow. I think you did understand. CboboxID was just a fake name I used an an example for a combo box that has 19 selections to chose from in the drop down on a form. Chkbox1 through 6c all represent an option that I need to verbalize in a report that gets forwarded.

Say the user selected cboboxID option 4 from the drop down. The 4 is theprimary ID for category "Water pipe fittings". Chkbox(s) represent the available options available for Water pipe fittings. Chkbox1, 1a, 1c, and 2 being true could mean the user wants those options. But I don't want to type what that means for every user.

imagine that Waterpipe fittings along with 1, 1a, 1c, 2, 2ameans that:

Customer wants to know the following about Waterpipe fittings:
Location and type (1)
- confirm the location of this water pipe.
- (if you look above, 1b wasn't selected so this space would be empty in my expression. I would like this line to not be here.)
- confirm the function and nature of this Waterpipe. (1c)

Status (2)
- confirm the operational status of this Waterpipe. Operational or non-operational.

Now, if a different category from one of the 19 available in my cbobox labeled cboboxID was selected, the checkbox text return would change. ComboboxID option 19 is "Airframe Sheet Metal". Now all the text from the chkbox options needs to return text associated to "Airframe Sheet Metal" instead of waterpipes.

Make sense?

I'm the middle man creating away to record and pass info between two people. The guy on my left clicks boxes of available options. By the way, depending on the category ID selected from the cboboxID, the description text next to the check boxes on the form changes too. You and I, the computer dudes, know the value is either yes/no but the user thinks he is selecting text. In a way he is if we set the program function up. The guy on my right just wants to read a text message of what the user wants and that's what I'm trying to auto create so I don't have to hand jam it myself every time.

[jdusn1978]

Just found out I cannot use this. My network has macros and functions disabled :( Going to have to ask some new questions.

[PhilOfWalton]

Just a thought. Firstly your remark the the The text behind the 25 checkboxes would change slightly depending on the cboboxID selected, is confusing. There is no such thing as slightly, the text would be different for each checkbox depending on the option chosen from the combo box. I dread to think how many combinations this could be.
I can understand how the person setting up the memo field gets the first bit of information from the Combo Box, but how does he know what the results of selecting any particular Check Box are going to be before actually selecting it?
Can you confirm that your Memo might contain up to 26 separate bits of information. Sounds more like a book than a request!!!
Phil

[jdusn1978]

Phil,
The point of this database is that users on my left have questions and the people who can get the answer are on my right and need to receive the questions. I’m in the middle to facilitate this transaction and creating a database to help me make it happen easier. I don’t want to have to do everything myself, its 475 different combinations. This is just one part of what I’m working on and it looks like this.

Combobox on Main Table Form:
[ItemCategory] (these are not the real names btw)
- 1. Popcorn Factories
- 2. Dog Kennels
- 3. Jeff’s mom’s house
- Etc to number 19.

Sub-form has check boxes. Imagine a check box next to each one of these:

Choose from the below.
1. Determine Location and Type
1a. Confirm Where
1b. Confirm What
1c. Confirm Function
2. Status
2a. Time of current status.
2b. Is it A
2c. Does it B
2d. Can it C
2e. Will it X.
2f. Should it Y
2g. Made of Z
3. Associated equipment
4. Number of employees
4a. Low
4b. High
4c. Medium
5. How to get there
5a. Primary location
5b. Secondary location
5c. Through the middle
6. Assessments
6a. It’s delicious
6b. It’s bad
6c. It’s neutral

These are the types of questions the people on my right can answer regarding 1 of the 19 combo box items selected above. I’ve standardized them, there is a free text field to add additional info, but this makes it easier for everyone in involved. The user can click boxes, I do nothing in the middle, and the people getting the answer get a standard question instead of something stupid and made up. You get a lot of weird and hard to understand questions here, what if all the ones that could be answered here had a generic checkbox? Category Access. Check those that apply 1. Fields, 1a. Primary Key Fields 1b. Memo fields. 1c. Calculated Fields. Mine's not that simple but pretty close.

You, the user on the left who selected a category. Let’s say you choose category 1. Popcorn Factories from the combo box on the main form and check boxes 1, 1a. 1b. 2. 2a. 2b. 2c. 3. 5. 5a on the sub-form.

You would know as a user that you want to know about Popcorn Factories with the following generic checkbox questions: Confirmation of their location and type: What are the specific locations of the popcorn factories, What type of popcorn factories are they? What’s its status? Is it operational and working? Does it have the correct licenses? Does it produce? What type components do their machines use? How do we get there? How do we get to the primary location? How do we get to the secondary location? Can I get there straight?

You’ve made your selections, which in my database are just “yes/no” but, I know that Category Popcorn Factories and checkbox’s mean a question to the people on my right.

Here’s what might be behind those check boxes and what would actually get sent to the people on my right who are going to survey and answer this question request:

Memo Field (10 sentences, not a book):
Please provide the current status of the popcorn factory including:
- When did the popcorn factory receive this it’s fluffy status?
- Is the popcorn factory currently operational capable, i.e. is it able to produce at least 100 popcorn machines per week?
- Does the factory produce fluffy, salty, or buttery popcorn making machines?
- Can it make nacho machines to compliment the popcorn machines too?
Please name all the brands of popcorn machine equipment produced at this factory.
How can I travel to this popcorn factory?
-How do I get to the Cheese Wiz primary headquarters factory?
- How do I get to the oompa loompa popcorn workers living quarters?

Now, what if you select category two and it’s “Dog Kennels”. You can click the 1., 1a. 1b., etc. But I’ve put different text there that will be sent to a dog kennel surveyor. The question would come out as:

Memo Field (standard questions that my kennel surveyors would answer:
Please provide the current status of the dog kennel including:
- Is it clean or poopy? Poop type and size, and toys located in the poop.
- Is the kennel currently operational? How many dogs, chew toys, and tennis balls are there?
- Etc etc etc.

This memo field portion is what I give to the people on my right via a work order (report). It includes other specifics but this is the only part I am having trouble producing. I have a huge IIF statement written but it requires in its current form that I break it up between 3 text boxes. I can get 1-2d, 2d-4c, 5-6c in separate text boxes. How can I push this unbound text info into a memo field for my report generation? As stated, I learned I am not allowed to enable functions and macros on my network. Now each category probably needs its own table and memo fields.

Just a thought. Firstly your remark the the The text behind the 25 checkboxes would change slightly depending on the cboboxID selected, is confusing. There is no such thing as slightly, the text would be different for each checkbox depending on the option chosen from the combo box. I dread to think how many combinations this could be.
I can understand how the person setting up the memo field gets the first bit of information from the Combo Box, but how does he know what the results of selecting any particular Check Box are going to be before actually selecting it?
Can you confirm that your Memo might contain up to 26 separate bits of information. Sounds more like a book than a request!!!
Phil

[PhilOfWalton]

I understand what you want, and I still think the solution I suggested would be the easiest way, but if you are not allowed to use macros or VBA I can't see how you can achieve anything. For example, if you use a check box, and I certainly don't think it's the way to go with your project, you need a mechanism to check when it has changed from True to False, and AFIK that has to be a macro or VBA code.
I'm sorry, but with this limitation, I can offer no further help.

Phil