If ComboBox IsNull selects a value (1 Viewer)

[Malcolm17]

Hi,

I am trying to use 4 combo boxes to allow users to create and print a report. If combox 2,3 and 4 is null then it will print Report1(has only 1 column), if 3 and 4 are null then it will print Report2 (2 columns) otherwise it will print Report4 (4 columns)

I am trying to use an if statement to decide which report to print depending upon the PRep value.

I have tried many variations in my code for the on click print button, however this is the one that I am seeing as most sense, however I am missing something as it keeps generating errors.

UG added Code Tags

If IsNull(Me.Text4) Then PRep = "4"
Else
If IsNull(Me.Text3 And Me.Text4) Then PRep = "2"
Else
If IsNull(Me.Text4 And Me.Text3 And Me.Text2) Then PRep = "1"
Else
PRep = "4"
End If

After the PRep number has been generated then it will select the report using code something like below:
Select Case Me.PRep
  Case "1"
   StrDocument = "Report1"
  Case "2"
   StrDocument = "Report2"
  Case "4"
   StrDocument = "Report4"
  End Select
DoCmd.OpenReport StrDocument, acNormal

Hope this makes some sense, please help as this has been driving me mad for weeks!!

Malcolm

 

[Micron]

I don't get it. Why aren't all the choices in one combo list? Might help if you gave some examples of the combo list data. I'd say your approach has more possibilities than you are allowing for, unless you're enabling disabled combos as you go. Even then, that would be unreliable.

EDIT - Forgot to ask that you post indented code within code tags (# on forum menu bar) to make it easier to read - and always state the error message and where it appears. I imagine it's on lines like this
IsNull(Me.Text4 And Me.Text3 And Me.Text2)
because you cannot group logic or apply functions like that.

 

[Uncle Gizmo]

I agree with Micron, I could sit here for 1/2 an hour scratching my head, trying to work out your logic, but I have the distinct feeling you don't really know what you're doing... If I've got that wrong then convince me! Tell me what you're doing, and where you are going with it. Then we should be able to make some headway..

 

[Malcolm17]

Hi,

The user can select up to 4 options\fields to display on the report and also a date range, effectively it is a diary system. At the moment it prints one report with 4 columns regardless of how many columns are required, however I wanted to go a step further and have a 1 column, 2 column and 4 column report as it can be lengthy when printed with just 1 column for a number of dates, if just 1 or 2 columns then there is alot of wasted paper.

Your right the errors have been on #IsNull(Me.Text4 And Me.Text3 And Me.Text2)#, it doesn't like multiple if statements.

I'm trying to achieve that if combobox1 only has been selected from the list then it will print Report 1, which only has 1 wide column, if comboboxes 1 and 2 on the form is selected then it will print Report2 which has 2 columns, and if 1 and 2 and 3\4 comboboxes have been selected then it will print Report4 which has 4 narrow columns.

I've attached a screenshot to try to help show what I am trying to achieve.

I understand why you both say that, however I have been working on this DB for about 2 - 3 years and its coding is huge, I'm sorry, I'm maybe just not explaining myself very well for this part.

Hope this help to explain a little better.

Malcolm

 

[moke123]

It may be helpful if you upload a copy of your DB. Strip out anything not needed to show us whats wrong. Leave a few records to work with but change any identifying info (names,email addresses,etc)

 

[vba_php]

I'm trying to achieve that if combobox1 only has been selected from the list then it will print Report 1, which only has 1 wide column, if comboboxes 1 and 2 on the form is selected then it will print Report2 which has 2 columns, and if 1 and 2 and 3\4 comboboxes have been selected then it will print Report4 which has 4 narrow columns.

Malcom,

I believe if you want to communicate with a programmer, you might want to change those words to something like:

I have 4 text boxes on my form where I am entering the index numbers assigned to 4 different reports that have different names. I want my code to detect which texboxes are NULL and assign a report name to a variable based on which ones are NULL and which ones are not.

That's exactly what it sounds like you mean, based off everything you've said. you posted this:

If IsNull(Me.Text4) Then PRep = "4"
Else
If IsNull(Me.Text3 And Me.Text4) Then PRep = "2"
Else
If IsNull(Me.Text4 And Me.Text3 And Me.Text2) Then PRep = "1"
Else
PRep = "4"
End If

but your words I quoted at the top of this post describes a completely different process. the entire code snippet you posted has absolutely nothing to do with combo boxes. perhaps you were mistaken regarding the difference between a text box and a combo box? personally I don't see anything wrong with what you want. I've seen many people do this same thing and they all did fine. your PDF shows an extremely unorthodox way of doing something like this. not only are you making things un necessarily difficult for yourself, but if your requirement is to dynamically change the architecture/structure of your report object at the time it is opened (or even if each open instance is a different report), it suggests that this whole thing is not even close to being setup the right way in terms of table structure. You need to re-evaluate it. If you don't want to though, the way you can get this done is to create 4 different report objects, each one having "fixed" positioning with regard to the field controls. that's much easier than trying to change that stuff dynamically. you can try "canGrow" and "canShrink" properties, but I doubt it will get you anywhere. Another thing....based off of your explanation so far, there doesn't seem to be any need whatsoever for the 4 combo boxes on your form. they are a duplication of what your pulling from your 4 textboxes.

to relate another concept to the struggle you're having.....if I was to offer a solution to someone who wanted to populate a dynamic array by running a vba loop but didn't understand how to do it, I would tell them to populate the array the easier way which is easier to understand, like this:

array(0) = "value1"
array(1) = "value2"
etc....

instead of frustrating him with:

for x = 0 to 10
    'throw here
next x

I call this approach "simple-stupid", and I employ it all the time in apps I create for a variety of reasons. The purpose is to make a life easier. An example of something that serves the opposite purpose but employs the same concept regarding achieve a goal is Muhammad Ali's "rope a dope" technique he used to defeat so many of his opponents.

but I have the distinct feeling you don't really know what you're doing... If I've got that wrong then convince me!.

lets be nicer to the newbies guys. I realize I'm the biggest jerk here with regard to issuing harsh words but I promised everyone, Richard and myself that I would be dedicated to improving. let us instill confidence in them so they can achieve what furthers their growth professionally!

 

[Uncle Gizmo]

Hi,

The user can select up to 4 options\fields to display on the report and also a date range, effectively it is a diary system. At the moment it prints one report with 4 columns regardless of how many columns are required, however I wanted to go a step further and have a 1 column, 2 column and 4 column report as it can be lengthy when printed with just 1 column for a number of dates, if just 1 or 2 columns then there is alot of wasted paper.

Your right the errors have been on #IsNull(Me.Text4 And Me.Text3 And Me.Text2)#, it doesn't like multiple if statements.

I'm trying to achieve that if combobox1 only has been selected from the list then it will print Report 1, which only has 1 wide column, if comboboxes 1 and 2 on the form is selected then it will print Report2 which has 2 columns, and if 1 and 2 and 3\4 comboboxes have been selected then it will print Report4 which has 4 narrow columns.

I've attached a screenshot to try to help show what I am trying to achieve.

I understand why you both say that, however I have been working on this DB for about 2 - 3 years and its coding is huge, I'm sorry, I'm maybe just not explaining myself very well for this part.

Hope this help to explain a little better.

Malcolm

Well I couldn't make head nor tail of the original code you provided. You mention Combo Boxes but the code shows text boxes, so I've gone with text boxes... The main problem is you need to to check for NULL for each individual text box.

I started out by adapting the code you provided hence the numbers returned in "PRep" don't appear to match your later explanation, but they do match your first explanation/code provided ... (I think)...

I've attached a demo database CustomReport.zip

and a Video https://youtu.be/lksnl1JXm58 so you can see what happens...

 

[Micron]

The main problem is you need to to check for NULL for each individual text box.

We see this all the time, yes? I explain it this way so that they don't fall into the same trap with something else down the road:

Code, and for that matter, computers, cannot interpret things the way we can; logical operators must be able to stand alone. If I said "Are you tired or thirsty or hungry" you would understand.

Code "sees" 3 different parts:
1) "Are you tired? (an expression or function is evaluated), answer = yes/no
2) "Or thirsty" What the heck does that mean?
3) "Or hungry" What??
To put it another way, if I said just 2) or 3) to you, you would not know if I'm asking about you, me or someone else.

You have to apply that thinking to any future code that involves such multiple processing.

EDIT - my apologies to Uncle if the vid explains all that. I confess I didn't view it.

 

[vba_php]

I hope we haven't overloaded this person's brain with so many responses....