I’m trying to put together a little database of my own and learn Access into the bargain so forgive me if I don’t use the correct terminology.

I can pull a report that is created using a StartDate & EndDate function. I freely admit that I got it from the net as I was struggling to write something myself, even so it works very nicely.

Now what I’d like to do is add an “If” statement to it. I presume this would be the right thing to do given what I'm trying to achieve.

My Customer forms have a text box entry named txtEECust and if there is a “Yes” in the box then I’d like the function to pull only the reports that are within the start and end dates along with the txtEECust = Yes. Any forms that are within the date selection criteria and txtEECust = No are excluded.

I tried adding If (Me.EECust = "Yes") Then..... to the below but it didn't work.

Hi, welcome to UA.

As a general rule, any time you have a Where string, you can enclose it in parentheses and add: " AND (other conditions)" to filter it further.

Based on your current procedure, you might put something like this in there:




"Yes" and "No" are simply textual values to make it look nice to the user. The actual values understood by VBA and Expression Service (queries) are True and False (no quotes around them)

(actually the actual values are 0 and nonzero, where 0=False and nonzero = true... (-1 is generally used for True, though any nonzero number is fine) this is the basis for just about every programming languange and can be used in place of True or False if you so desire).

Hope that helps,

Thanks for the explanations and code Jack.

Not quite sure where I need to place said code but I’m sure trial and error will overcome that minor issue.

Glad to help. Let us know if you get stuck.

Cheers,

Tried for ages to get it to work but with no joy. The more I read/tried different things the more confused I got. I get the feeling I'm trying to run before I can walk!!

I should have pointed out that I have a two forms. One form holds all the data and the other holds all the reports so a "Me." in the code isn't going to work if I need it to point at the data form. Is that right?

I tried this along with countless other attempts thinking it would do the trick but not so.

If Forms![frmCustomers]![txtEECust] = True Then
strWhere = "(" & strWhere & ") AND (EECust = True)"
End If

As you can no doubt tell the txtEECust is the data field name and the EECust is the table name.
I presume I've got that bit right if nothing else!

Let's make sure we've got the object names/terminology correct:

Note the difference between a form Control, and a Field. A Field is part of the Recordset which the form is Bound to, whereas a Control is bound to a Field of the Recordset (generally speaking at least - this is usually true when we're considering bound forms).

The value of the Field is a direct representation of the data that's stored in the table. The value of a Control can be considered a middleman between the user and the stored data, giving us the option to validate before saving, etc. The point to remember here is that the value of the Control and the value of the Field may not be the same, if the form is dirty (i.e. - not saved). Until the form saves the record, any changed values of controls will not yet be reflected if we reference the Field itself.

Furthermore, this can be a bit confusing because by default, Access gives both the Field and the Control which is bound to it the same name. Thus, some people (myself included), prefer to rename all controls to something different than the fields (I generally accomplish this by prefixing with "ctl" or the like, as you seem to have done in at least a few cases with "txt").

Also: Tables are raw containers of data and shouldn't be interacted with directly. Instead, Forms provide a layer of interacting with table data.



Based on the above overview of objects, I'm not sure your statement is correct? Or if it is, we'll need to change some things around. Is txtEECust the Field of the Form's recordset, or is it a Control which is bound to a field? (the "txt" prefix generally indicates a textbox control). Is EECust the Table name or the Form name?

Sorry if this seems a bit out of context, but we'll need this squared away to proceed further (besides which, this is fundamental stuff that will give you a good understanding of many hows and whys in Access).

You may want to read a little bit on Naming Conventions. Consistent use of naming conventions are invaluable to keeping all these different objects straight and will make your life much easier.

After we've confirmed the object names and types, we can proceed with finding the problem with the code.

Cheers,

I'm confusing myself let alone you, sorry.

Table Name = tblCustomers
Query Name = qryCustomers
Form Names = frmCustomers & frmReports
Report Name = rptEEBoilerSerivceLetter

txtEECust resides in the form frmCustomers. Am I right in saying that it's a Control (a Text Box)? I always thought of it just as a field.
Its Control Source is EECust (taken from the drop down list on the data tab in properties (generated by the query qryCustomers))
I amended its name to txtEECust in the Other tab under properties.

The button that fires off the report resides in, not unsurprisingly....... frmReports (no prizes for guessing that one).

I hope the above makes more sense.
I'm going to do some more reading of naming conventions etc as this is obviously a massive gap in my limited knowledge.

Cheers.

That makes much more sense, yes. You seem to be well on the right track, thanks for clarifying.

Unfortunately I've been slammed with real work today and it may be a little while before I can revisit this (if not this afternoon then some point tonight).

Cheers,

No worries Jack, I've got some serious reading to do as you know!!
Your help and assistance is greatly appreciated.

Can you post the SQL string (or table fields) for each form that you're working with? Provided the object names are correct, the following is the correct syntax for reading the value of another form's field or control value:



This seems to be correct in syntax in the example code you've shown.

Are you receiving any errors, or is the recordset not returning the expected records? Can you comment out that Debug.Print strWhere line as well, and post that for verification?

I suspect an issue with field/table/query/form/control naming references, because otherwise every looks fine.

Thanks,

I've attached the table field list for table tblCustomers that relates to the form frmCustomers.

There is nothing of any interest for the form frmReports. It just has two unbound text boxes, one for start date and the other for end date. The only other thing is the button to generate the report.

The other attachment is the error message when the button gets clicked.

I've checked over everything for typo's and can't understand why it errors looking for the form frmCustomers.

Hang on a second. Ignore the error message I'm getting. I just closed the program and restarted it and when I tested the button again the report opened. I changed nothing - honest!!!

That said, the report is not working as it should. It shows me pages I should not be seeing. It's like it's ignoring the If statement.

Have you run a Compact/Repair on the database any time recently? Sometimes if things start acting a little screwy this helps... it should be done on a regular basis, especially when making design changes. Be sure to back up the db before you do (just a copy of the backend file is fine). (more info in the wiki: Compact and Repair) Also make sure that nothing is open in design view before attempting to test (forms tables queries or reports, they should all be closed or opened as required in their standard runtime views).

What's the final Where clause from the string before the report is opened. Can you post that please?

Sorry Jack, I don't understand what you mean.





Did a C&R but to no avail. Still just seems to ignore the If statement.

Toward the end of your code, you have the following:



The "Where String" is the criteria which limits the records of your reports. By uncommenting the line above as noted, it will print that line to the Immediate Window (CTRL+G to show, or View -> Immediate Window (you might want to read up at least a tad on that if you're not familiar with it... for debugging it's the prime tool)).

The Debug.Print statement prints the variable contents for you to check. That'll be the Where clause... our intent here is to find out if that extra "(EECust = True)" is being added in as required.

Let me know!

Cheers,

So I add dates to my start and end date fields. In this case I added as a start date 01/04/2012 and an end date as 30/04/2012.
I clicked the button to fire off the report and then pressed CTRL+G and in the Immediate box I was presented with.....

([NextBoilerServiceDate] >= #04/01/2012#) AND ([NextBoilerServiceDate] < #05/01/2012#)

Looks kinda wrong even to me but the report gave me two listings that I was expecting to find. i.e. they were within the start and end date ranges.

You're right about it being a prime tool. First I've ever heard of it and one I'll be using a lot I would guess!!!!!

This string is being built in VBA and limits your records accordingly. It appears that it should have an additional clause in there to limit based on EECust being True, which is not happening for whatever reason.

Can you possibly upload a stripped down copy of the database for me to take a look?

The system won't let me upload. I get an error......

Upload failed. You are not permitted to upload this type of file

It has to be zipped, the accb* filetypes aren't allowed directly

Attached.

Hi,

Sorry, I just now got a chance to look at this.

Question: do you compile your code? Based on what I've found, I'm going to take a guess at no, but that's ok, we'll get you squared away

First a quick note about compiling your code. Before running, or even saving, your code should always be compiled. Then you make changes to code, there's a text version that is then uncompiled, and the compilation process coverts that text version to the machine code version that we can't read but Access does. Furthermore, compilation is a first means of debugging code. If your code does not compile, you have what's called a "compile time error." A compile time error is syntax errors or reference errors in the code you've written that don't match up like they're supposed to. For instance, when I try to compile the database you've provided, I get the following error:

"Method or Data Member not found."

With the following portion of code highlighted:



This means that Access/VBA was not able to find the "txtBiolerSSStartDate" property or object (such as a control) that it would expect in that context.

Well, I guess that wasn't so quick, but anyway, the bottom line is that your code MUST compile without error before it's to be of any use at all. All bets are off otherwise.

The task then is to go through the code and attempt to compile, fixing each error that comes up, until it compiles without error. Sometimes it's a quick fix, sometimes not.

One more thing before I set you to the task... Option Explicit. It is very, very highly recommended to put an Option Explicit statement at the beginning of every single code module in the database. Also, Option Compare Database is standard as well, though it's missing from three of the four modules in the db you provided. Look at the code for the form fsubDairy and you'll see those two lines at the very top of the module. Copy/Paste those and put them in the same spot (the very top) on all other modules (both form modules and the standard module (MonthandDate)).

Then, go to the Debug menu in the VBA IDE (editor) and the top entry will be "Compile [YourProjectName]" Fix errors until it compiles fully. Stop back with help (include specific error messages and the highlighted text!) as required.

(sorry, it's a bit of work, but once get through this type of exercise once, you'll have a very large stepping stone in the learning curve out of the way)

Good luck!

Undaunted by your last post I have pressed on.

Each module now has the Option Explicit statement added as you advised.

I also ran the Debug compiler and fixed all the errors.

She’s still not playing the game though.

See attachment for your approval.

That's better

A couple more things:

Rather than refering to just [txtBoiler] or [txtLastBoilerServiceDate] in the code, it's a better practice to put the "Me" keyword in front of the control. "Me" refers to the current instance of the form, and VBA provides intellisense, which is much help in writing code. So, rather than just [txtBoiler], use Me.txtBoiler. When you type the period after Me, intellisense will provide a dropdown list of all the available controls and properties for the form. This is much help in ensuring that we don't have typos, basically, but also makes general code writing quite a bit easier.

Also note that the square brackets around control/field names in code are only required if one uses spaces in object names, which is not good practice, which you are not doing anyway, so we can get rid of all that.

So, as an example, your code would go from this:



to this:



I think you'll find that a much more friendly way to do things, and it will especially help down the road.

Next, I'll want you to look into debugging practices. Particularly Breakpoints and Stepping through Code. Google "VBA Breakpoints" for more info, but here's the basic rundown: click in the grey bar on the left on a particular line, and the line will be highlighted in red. This is a breakpoint. When the code runs, it will stop here and let you execute it line by line. During this time, you can use the immediate window to check the value of variables, or you change them even, or you can hover the mouse over a variable or control name to see it's value. This is of great help in trying to determine if the procedure is processing the way it should be. Press F8 to execute the next line. Press Ctrl+Shift+F8 to stop debugging and run the procedure the rest of the way through. See the attached screenshot for a visual example of code stopping on a breakpoint. The yellow highlighted line is the line that's about to be executed.

Click to view attachment

By doing this you can follow along with how the code is executing and what the values are. To check a variable that's in a procedure (for example, if we want to use the Immediate window to see the full value of strWhere), type "?VariableName" in the immediate and hit enter... the contents of the string wil be displayed below:



(as a side note, you can change some things via the immediate window also):




Anyway, this should give you a much better grasp on how to manage VBA code... a definate requirement if you're going to be digging into Access with any sort of depth.


Now we'll touch base on the EECust field. This is set up as a text field, in which you intended to put a "YES" in there to indicate that they were assigned by EE. Rather than a text field (though it would work), this should probably instead be a Yes/No field. I would suggest changing your table field to a YesNo field instead, removing the txtEECust field from the form, and then drag/dropping it from the field list again. Now you will see a textbox instead of a textbox. Much easier to work with for simple yes/no stuff.

Once you're familiar with debugging technique and have the EECust field modified to a checkbox instead of a textbox, we can take a look at the reporting end of things.

To confirm... your intent with the reports is to print multiple reports narrowed down by a range of dates? Or did you want to be able to by default print just the letter associated with the current customer in the frmCustomers. Let me know which you're looking to do here, and I'll advise how to approach whichever you need (or both, if you like). The current approach that you're trying (reading the EECust field from the Customers form) isn't quite correct, but I'll get into that after you've answered... you should have enough going on for the moment!

Cheers,

Lordy, debugging - that’ll keep me quiet for a while I bet!

I’ll make the appropriate changes to the VBA code regarding the Me. keyword as per your example.

Re your question.


Correct but also narrowed down by what will now be the EECust CHECKBOX.
i.e. If form dates are within date range and EECust Checkbox is ‘ticked’ then print form(s).

Your second suggestion is a good idea.



I’d like to have a go at trying that for myself without any assistance to see if
(a) I can do it
(b) Make sure I’m doing it in the right fashion

Of course I’d need you to oversee my finished attempt if that’s ok?

The reason I asked was because the integration between the narrowed down range of reports to run and the Customer form's current record is incorrect. Consider that you're taking a base set of records directly from the table, and would be filtering by date. Also, you want the option to filter them by EE assignment as well, though this really doesn't have anything to do with what record they're on in the Customers form. (also consider that it's possible the Customers form may not be open while the report is being run... it may be rare, but in your setup now it's entirely possible for the user to close the customer form, thus creating an error when the report tries to read that field on the non-opened form).

I would think you'll want a checkbox on your Reportrs form, which would serve the same purpose: show me "EE" customers, or don't show me "EE" customers. Judging by the reports in the db, I'm going to further guess that you want to run, say, report A for EE customers, and report B for non-EE assigned customers. Thus, the selection for EE is a function of the reporting form, and has nothing really to do with the Customer form and whether that current record happens to be an EE assigned customer.

Now, let's leave the report filtering behind for a moment, and consider the Customers form, and displaying ONLY the service letter for that customer. This is entirely possible, by filtering the report to include the record(s) which have the same ID as the currently opened Customer. At this point, the actual Customer EE field does indeed make a difference, because you need to run a separate report if it's EE. Here, you would check the value of the EECust field, and run the approriate report.

Here's some psuedo-code for opening only a single service letter from the Customer form:




And considering you have put a checkbox on the Reports form to filter via EE customers, here's some psuedo-code for filtering by the date range:



(the above examples are not usable... there's syntax errors and naming errors etc, and are meant to show concept only)

If you follow all that, you should be well on your way to setting up both implementations of reports.

Good luck! Stop back if you need further help.

Cheers,

Thanks Jack, I'll give it my best shot.

Might take me a while to do as I have a few jobs that need attending to but as Arnie says........

I'll be back!!!

Well I've tried and tried but alas

Managed to get the single service letter to work but as for multiple letters!!!

In desperation I put everything on to the frmCustomers form but when I ran the code I got a box up headed Add Parameter Value ctlEECust.

The immediate box had what looked like the right information in it - start date, end date, ctlEECust

I'm beginning to wonder if you can have a criteria of two dates and another value or if you can this is not the way to do it.

Hi,

I'm out of town for a couple days and limited to iPad or a phone. I'll put a request in for someone else to drop by and take a look but it may be until midweek before I can take another look at it.

Cheers,

No worries Jack, I'll just keep banging my head against this brick wall

I’ve finally got it working how I envisaged it. Had to adjust the “If” statement as per the below.

Reason being, if the ctlEECust checkbox was NOT “checked” on the last open (viewable) form in frmCustomers I got back a one page blank report.

If said checkbox was "checked" it then worked, so by adding the False Or True statement fixed the issue whether ctlEECust was "checked" or not.

Does that make sense, not that it matters now I guess.




It goes without saying that I would have never achieved this without your help Jack so thank you very much for your time, patience and ultimately your knowledge.

Cheers,
Dean.

Excellent news Dean! Glad you got it worked out. I had intended on doing a working sample for you today when I had a few, but it's always better when someone can find the way on their own (teach a man to fish...)

Glad to help!