PDA

View Full Version : Help with MS Access



Aurealis_
04-03-2010, 03:21 PM
Hello again,

I'm trying to design a neat little application for work and I've come across one thing that I'm having trouble with. I have reports from 11 different teams in the database and I have created a form that uses command buttons to select a team and generate a report which shows that teams progress. However, at the moment I have 1 query and 1 report for each query - I have been trying to find a solution through filters and macros but I'm not any closer to it after 2 days of trying.


If anyone could offer any solutions it would be very much appreciated.

Regards,

Steven
04-03-2010, 08:09 PM
Have you created 11 different reports that you want to open using command buttons or are you using a form with combo boxes/list boxes etc in order to pass user input to a query from which a report will be generated?

Paul.Cov
05-03-2010, 08:34 AM
For this to work neatly, you need all your data in a single table, where there is a field which identifies the team(s).

On the form, add another Text Box with the Visible property set to hidden.
In your macro you will include a Setvalue statement to assign this hidden control the name (or ID) of the Team.

Then you only need a single report. With the OpenReport command, under the Where clause, include something along the lines of:

[Team ID]=forms![Pick Team]![Hidden Team Control]

[Team ID] has to be the name of the field in the Report that identifies the Team
[Hidden Team Control] is whatever name you give to the hidden control.

--------------
There's an even better way to do this - with a List Box or a Combo Box (visible) on the form, instead of the 11 Team Command Buttons.

The List/Combo box is used to pick the team to be printed, and the Where clause in the OpenReport function then goes something like:
[Team ID]=forms![Pick Team]![Team Combo]

The whole lot can then be kicked off with a macro attached to a single Command button labelled "Print" or "Preview".

One table, one report, one form, one button. Can't get much easier.

Aurealis_
05-03-2010, 11:07 AM
For this to work neatly, you need all your data in a single table, where there is a field which identifies the team(s).

On the form, add another Text Box with the Visible property set to hidden.
In your macro you will include a Setvalue statement to assign this hidden control the name (or ID) of the Team.

Then you only need a single report. With the OpenReport command, under the Where clause, include something along the lines of:

[Team ID]=forms![Pick Team]![Hidden Team Control]

[Team ID] has to be the name of the field in the Report that identifies the Team
[Hidden Team Control] is whatever name you give to the hidden control.

--------------
There's an even better way to do this - with a List Box or a Combo Box (visible) on the form, instead of the 11 Team Command Buttons.

The List/Combo box is used to pick the team to be printed, and the Where clause in the OpenReport function then goes something like:
[Team ID]=forms![Pick Team]![Team Combo]

The whole lot can then be kicked off with a macro attached to a single Command button labelled "Print" or "Preview".

One table, one report, one form, one button. Can't get much easier.


Hey Paul,

Thanks very much for your help. I would like to stick with the 11 command buttons, because that would suit the person using the application most. I tried doing what you suggested but I wasn't able to get it working. Here is what I have added:




Action: OpenReport
Report: rpt_view_by_filter
Filter Name: query_filter_incomplete
Where: [Team]=[Forms]![Pick Team]![hidden_team]



Text Box: hidden_team

So, it's everything you said except the SetValue macro - I've never used this before and I'm unsure where it has to be put in and what it needs to say. Do I have to create one macro for each team; e.g. click on the 'Marketing' button, run a Macro to set hidden_team to marketing and then another button to execute the macro to print/view the report?

Cheers,
Logan.

Paul.Cov
05-03-2010, 06:38 PM
Ok, so you can get rid of the filter in the OpenReport command.

I'm surprised you've got this far without knowing about SetValue, coz it's probably the most used of all the commands.

However, if you're sticking with 11 buttons, then you don't need the hidden TextBox control - just delete it off your form.

We need to know what the name of the form is that has these 11 buttons.

I've called it Pick Team, but you may be calling it Main Menu or Print Teams

Make yourself a Macro to take care of responding to the 11 buttons and opening the report.

Ideally, a single macro, with the names of other macros nested within it.
If you start a NEW macro, and play around with the buttons near the top of the screen, you'll find a button to Display or Hide the Macro Names column. There's another Button that displays or hides the Conditions column as well.

Ideally you'll see 4 columns in the macro design area, being (left to right):
Macro Name / Condition / Action / Comment

Lets assume you save this Macro under the name Print Teams

If you design the macro lines as follows:
Macro Name / Condition / Action / Comment
Button1 /(leave blank)/ OpenReport /
Button2 /(leave blank)/ OpenReport /
Button3 /(leave blank)/ OpenReport /
Button4 /(leave blank)/ OpenReport /
Button5 /(leave blank)/ OpenReport /
Button6 /(leave blank)/ OpenReport / ...etcetera

(Do not include the slashes or (leave blank) bits)

Now, back in the line for Button1, go to the Report Properties (Action Arguements) area in the bottom left of the screen.
Select the name of your report.
Select either Print or Print Preview
Leave the Filter area blank
In the Where Condition you'll need a line something like this:

[Team]=[Team1 Identifier]

Now, where I've put [Team] you need to put the name of the control used on your report which refers to the field used for the Team. Chances are, it's probably the same name as the Field from your table that refers to your Team Names. Include the square brackets around the name of the control.

Now, where I've put [Team1 Indentifier], take a look at your table data, and see how you've indentified each team. It may be by numbering them 1,2,3,4...
or you may have given the teams text names, like "Blue Team", "Red Team", "Yellow Team". Whichever the case, enter the value that is used to identify the team you wish to display when button 1 is pressed. In this case, with the bit to the right of the = sign you will NOT include the square brackets. If your teams are identified by number, you simply enter the number. If the teams are identified with text, then you must enter the exact text, and eclose the text within double quotes "

So now the Where Condition bit is something like either:
[Field Name for Teams]=1
or
[Field Name for Teams]="Blue Team"

and the bits in itallics you will have changed to whatever is appropriate in your database.

Now go to the next line of the Macro, where the Button2 macro is defined, and do the same adjustments to the OpenReport arguements.
The bit for [Team] on the left should be the same every time, as it refers to the same control on the same report, so the only bit that differs is the name to the right of the equals sign used to identify the 2nd team, which you put in the place of [Team2 Indentifier] this time.

Command button3 will use the macro line for Button3, so you enter the text or number for the team that button3 is to print out... etcetera.

Now once you've saved this macro (saved as Print Teams) you can then refer to each individual macro within this macro as follows:

Print Teams.Button1
Print Teams.Button2
Print Teams.Button3

So now on your form design click on each button and change the OnClick property of the button to:
Print Teams.Button1 for the first button,
Print Teams.Button2 for the 2nd button,
Print Teams.Button3 for the 3rd button, etcetera.

Done!

Paul.Cov
05-03-2010, 06:55 PM
Probably as clear as mud. It can be really hard to get to grips with all the little arguements and properties in Access.

Provided it is in Access 2003 or EARLIER, and isn't anything too confidential, I am happy to have a look at your database (then I'll better understand what you've already got in terms of objects, fields and formats.

If you want to email it to me, you can send it via an address I use where there's a risk of being spammed (it's not polite). send it to buggeroff@vodafone.co.nz

:)

Aurealis_
07-03-2010, 09:44 PM
Thanks mate I'll try it out at work tomorrow

Aurealis_
08-03-2010, 03:13 PM
Wow that's amazing. It worked. Thanks mate, that was a real help.

Yeah, it certainly is hard getting to grips with all of the different bits and pieces. So far I've just been learning as I go - I have been reading quite a bit from the access section of techonthenet.com but mainly it's improvised learning ;)

Good times!

I'm going to try and figure out how I can make it respond with
"no valid records to display" for the times when there's nothing visible for a team (this is a job requests database and so there are times when one team has no jobs for a while). I'll tinker around with it tomorrow and let you know how I go :)

once again - thanks heaps mate, you've been a great help.

Cheers,
Logan.

Paul.Cov
09-03-2010, 06:49 AM
If you have a field that tracks jobs/contracts, then you might find the ImmediateIf function helpful.

You type in the IIF statement in the ControlSource line for the textbox that you want to use for the "There are no records to display".

A formula must be preceeded by an = sign when used as part of a Control Source.

Something like this...

=IIF(IsNull([Jobs]),"There are no records to display","")

Or if you want it to name the team, it becomes more like:

=IIF(IsNull([Jobs]),"There are no records to display for team "&[TeamName]&".","")

Its kinda like an If Then Else statement
IIF([condition],[do this if condition is true],[do this if condition is false])

If you're learning on Access 2003 then you might struggle. The help available from the F1 key and help menus really blows. It tells you about the structure of a command, but almost nothing about the function of commands.
I learnt my inroads with Access on version 2.0, when it actually came with a bunch of books to help you learn.
Still slowly learning!

Aurealis_
10-03-2010, 09:29 AM
If you have a field that tracks jobs/contracts, then you might find the ImmediateIf function helpful.

You type in the IIF statement in the ControlSource line for the textbox that you want to use for the "There are no records to display".

A formula must be preceeded by an = sign when used as part of a Control Source.

Something like this...

=IIF(IsNull([Jobs]),"There are no records to display","")

Or if you want it to name the team, it becomes more like:

=IIF(IsNull([Jobs]),"There are no records to display for team "&[TeamName]&".","")

Its kinda like an If Then Else statement
IIF([condition],[do this if condition is true],[do this if condition is false])

If you're learning on Access 2003 then you might struggle. The help available from the F1 key and help menus really blows. It tells you about the structure of a command, but almost nothing about the function of commands.
I learnt my inroads with Access on version 2.0, when it actually came with a bunch of books to help you learn.
Still slowly learning!

Thanks mate

I'm learning on 2007 mainly, but they use 2003 at work so I'm having to format everything for that version. I had a textbook once that I learnt a lot of the basics from but I left that in Auckland :|

I'm thinking that I may sit the Microsoft exam for Access/Office later this year - would you have any comment on that?

Cheers,

Aurealis_
10-03-2010, 01:14 PM
Sad Face xD...

Another problem. I'm trying to create a form to allow the user of this database to update jobs - but he needs a quick way to select which job to edit. I added the records as a subform, and added a combo box to the top of the detail section to select [Job ID], and then some code (which I read a tutorial for) to filter the subform by selection - I suppose this is pretty much what you were talking about above, as an alternative to teh 11 buttons! It isn't working.

Here is what we have:

Forms:
frmEditJobs
frmSubformEditJobs

Queries:
query_filter_incomplete

Now, the Subform loads the records from the query and then these results are displayed within the main form. The main form has the following code for the Open and OnUpdate events:

SetFilter

The SetFilter procedure is as follows:

Sub SetFilter()

Dim LSQL As String

LSQL = "select * from query_view_incomplete"
LSQL = LSQL & " where [Job ID] = '" & SelectJobID & "'"

Form_frmSubformEditJobs.RecordSource = LSQL

End Sub

However, when I run the form I get Error 424: Object Required. What have I missed? :(

Aurealis_
11-03-2010, 09:28 AM
Never mind, I solved it :)
Exciting times - I'm almost finished this project

Paul.Cov
11-03-2010, 02:59 PM
Re the Access / Office exam - I haven't a clue about it's content or difficulty or any merit attached to it. Sorry.