Inputting a date and producing a report from it.

OK, so this one's a little complex.

Imagine. You have a query which requires you to enter something into a parameter - like a date or something. That query is then used to generate a report. But you'd like the search criteria you entered into the box to go on the report.

So, say there's a list of appointments for a particular day for a dentist or whatever. It would make sense to put the date the appointments are for on the report.

Step 1 - First create a form:

The entire purpose of this form is to enter a date (or two dates, or whatever you're searching for).

• You need a textbox (and it must be a textbox!) on the form to type the date into.
• Right click on the textbox and go into the properties. Change the name of the textbox to txtDate (or something similar).
  

• Save the form as something like frmGetDate

Step 2 - Next you need a query:

Say to list the appointments someone has on that a day.

Like this:
BUT – instead of using a standard parameter criteria in the date column ([Enter the date] say) you type:

[forms]![frmGetDate]![txtDate]

What this does is put the date you enter in txtDate on frmGetDate into the parameter search. Clever eh? Like this:

But, if you run the query it won’t play. There are good reasons for this.

Step 3 - Create a report based on the query:

Just use a wizard for now, you can go back and work on this later.

Step 4 - Now the clever bit:

What you have to do is run the report from the form.

So go back to the form and put a Command Button in it which runs the query – you do this under Report Operations.

This should work – but it won’t show the date you typed into the textbox on the report. Yet.

Step 5 - The other clever bit:

What you need to do is go back to the report in Design view.

Create a textbox on the report somewhere – probably best to put it in the header of the report by the way

Right click on it and in the Data tab you need to type:

=Forms!frmGetDate!txtDate

into the control source box. Like this:

Step 6 - Now, try it out:

Open the form, hit the button and see what goes on!!This is a bit tricky of course, but you should be able to get there.

There’s no reason why you can’t enter two dates in the form – just give them different names (like txtDateStart and txtDateFinish). You could then stick two dates into the query using these names and then the report. Logically.

There’s also no reason why you couldn’t use a calendar control or spinners to populate the textboxes on the form for that matter – you could also set default values and/or input masks on the form (right click and properties) of course.

Total amounts of well coolness.

Blanking Stuff on Forms

You have a form. You want the user to do stuff on it but there are some records you need the user to not be able to change. How good would it be to be able to blank those fields so that the user couldn't change them (and, while they're at it, screw the entire database up...)?

Quite easy.

• Get the form in design view and select the field you want to stop the user changing
• Click the Properties button
• Find the Enabled property. It'll say Yes. Bet you can't guess what comes next?
• Change the enabled property to No (I bet you got that right...)
  

Now run the form and check it works!

The Order Form Dilemma

Dear Deidrie,

I have a bit of a dilemma with my ordering system. I try and I try but it just won't work.

Can you help me get it all fixed and lovely?

Worried of Saxmundham

Hi Worried,

No problem you, we'll have you sorted in a jiffy.

To start with, this is how your table might want to look:

Notice that there's only one set of information in the tblOrderLine. I've cut off the screenshot to make it more visible - there's a lookup table over on the right for those of you not familiar with the admittedly unusual example Worried has chosen here.

You'll notice as well that the referential integrity has been enforced. I always think you can never have too much referential integrity.

What might not be obvious at first glance is that there's no Primary Key in tblOrderLine...

Once you have your tables sorted it's plain sailing. The juice covered textbook has information on or about page 250 which might come in jolly handy.

It will involve setting up a query a bit like this one.

Notice there's more than one table involved there. There's also a calculated field at the end for the cost: Cost:[Price]*[Quantity] or something similar.

Oh, a tip: try sorting the field on Order Number. You might just find it saves a bit of head scratching later on.

Then you can set up an Order Form a bit like this one.

You need to get the Form Wizard to wave his magic wand on this one as you're taking stuff from three places: tblCustomer, tblOrder and qryOrderedProduct. Fun - slightly confusing fun the first time you do it, but in my experience most of the best things are.

You'll notice I've used some of the Cool Formy Things, like a Calendar control and a spinner, to increase the usability of the form. I've also taken a bit of time to get the layout as clear and usable as I can. I'm like that. Some people would say obsessive, others might just say odd.

So, Worried of Saxmundham, hopefully that might help. If it doesn't then I can recommend a nice crisp Chablis if that helps?

Best wishes,

Deidrie xxx

Cool formy things

So, here's a form.

You might want to open the image in a new window and have it handy. This might get a little complex (those easily scared might want to look away now...)

It's for a newspaper delivery database (perhaps more of which at some point). Specifically it lets the user update when a customer will be on holiday. It's only actually updating some of the data in the record by the way, which is entirely possible but might not have occurred to you.

First off, top right there's a combo box to skip straight to a record based on the surname - although the drop down will also show other key data. That's easy (it's just a combo box which you Find a record on the form... using).

Then there's a calendar control to enter the date the holiday starts. This is just a standard click the calendar thing - the juice covered textbook covers it on pages 228-229.

But, and here's where it gets clever, I added some code.

You'll remember (or be about to look it up...) that there's some code you need to add in the Calendar control VBA editing scary looking bit: something along the lines of Me.HolsBegin.Value = Calendar3.Value?

Well, what I did was add some code below that to simply make the HolsEnd box be equal to the calendar click + 7 in the first instance. It's kinda like using a default value. Honest.

Look, here's the screenshot...Then, to avoid having two confusing calendars on the same form I added a spinner next to the HolsEnd box to allow that to be adjusted manually but without having to do the annoying typing thing. The code for this is on pages 229-230 of the juice covered textbook.

Now, the clever bit.

I wanted a button to enable me to clear both dates easily and without any fuss.

Could I find a way to do this? Could I heck. Nothing from the standard stuff seemed to work.

So, I experimented. And, guess what, it worked. Like, first time dude! Must be my lucky day...

Anyway, here's what to do:

1. draw a button using the grey toolbar button thing
2. choose any of the options and go for the text - you can change it later
3. click finish to add it to the form (although you may want to give it a sensible name as well)
4. make sure the button is selected
5. hit the Properties button on the toolbar (or right click...)
6. find the On Click bit, click in it and then click the three dots next to it
7. choose Code Builder to go to the VB editor
8. now, delete all the code apart from the first and last lines in the section related to your button - it'll be called Private Sub Name_Click()
9. then add the code from the screenshot, using the names of your fields you want to clear

Heck, the code's easy isn't it? All it's doing is clearing both fields by making their value equal to nothing.

Easy. Lemon. Whatever.

And, yes, I do feel quite proud of that even though it's probably not that complex. Took 39 years and 350-odd days to get to that...

Which just goes to prove the foolishness of trying to teach new tricks to old dogs