Need Help Simplifying

[SeaRox]

All:

What I'm trying to do:
I want to be able to use a switchboard button (or a button on a form) to open a unique form. OK, true, that's easy. Here is where I am having trouble. I want that one click to set the title of the form and the criteria for the query of a subform. Basically I want one form/subform/query to service multiple employees.

What I currently have:
I have a main form and two subforms, and multiple queries for each employee. They all do the same thing, display the same basic information but it all depends on the employee. If I make a simple change it takes hours to change all the forms for all the employees.

What I've done to find an answer:
I bought a Learn Access in 24hrs type book which got me to where I am now. I then purchased an Access VBA book and am currently about 1/3 of the way through that but still have no idea where to go.
I've searched through example databases to find one that does something similar but haven't found one.
I've done web and forum searches. I think my lack of success there is possibly due to not searching for the right things.
I've been experimenting on my own since November of 07.
I don't have a skilled friend I can ask.

There is probably a simple solution I just don't know where to find it.

Any help?

SeaRox

 

[KenHigg]

You may be able to do this by using the openargs property of the form. This is where you send the form information when you open it.

 

[georgedwilkinson]

I cannot begin to grasp what the problem is from the description. Please post what you have and I'll be happy to take a look at it.

 

[KenHigg]

I think he wants to limit what the user sees based on the user. Like a user can only see 'his' personal info... ?

 

[Mike375]

I want that one click to set the title of the form and the criteria for the query of a subform.

The title if the form is the Caption property and can be done with either code/macro.

Basically I want one form/subform/query to service multiple employees.

What I currently have:
I have a main form and two subforms, and multiple queries for each employee. They all do the same thing, display the same basic information but it all depends on the employee. If I make a simple change it takes hours to change all the forms for all the employees.

It sounds like what you need is a parameter query that is based on form data.

Are you familiar with setting conditions. If not, they basically determine what action lines in code or macros will run and based on "conditions", which can be entries in a field, the time the date etc and etc.

For example, lets say you have six employees and they are Bill, John, Tom, Mary, Elizabeth and Dolly. You might have an opening form in your data base whereby they enter their name in a textbox and then "click". Code or macro would now run based on the name that was entered.

So in a nutshell you can do about whatever you want to do BUT condition setting is the key. Let's take your multiple query for the subforms. A macro or code can change the recordsource of a form. Which query was used as the recordsource would be controlled by the name the employee entered.

Think of it this way. You have 6 employees and so you have 6 different sets of action. When the employee enters Tom and clicks the conditions will sort of look like:

If Tom is the entry in the textbox then do this and do that
If Mary is the entry in the texbod then do this and do that

 

[SeaRox]

Mike375,

Thanks for the help.

It sounds like you have an idea that will work for me. I'm not familiar with setting conditions. Can you point me to some example code I can modify to get these results or maybe a wiki on setting conditions?

 

[Mike375]

Mike375,

Thanks for the help.

It sounds like you have an idea that will work for me. I'm not familiar with setting conditions. Can you point me to some example code I can modify to get these results or maybe a wiki on setting conditions?

Here is an example. I just pulled this from myown db and this is action line from a macro but I converted it to code.

If (Eval("[Forms]![2ProspectT]![Text2180] Is Not Null")) Then
Forms!AllCalls1!F5 = Forms![2ProspectT]![SumInsured]
End If

The bold section (my bold) is the condition. The next part is the action

What it is saying is that if the Text2180 on form 2ProspectT is not null then place the value that is in the SumInsured field on 2ProspectT into the field F5 that is on form AllCalls1

The following is setting the record source of a form

Forms![2ProspectT].RecordSource = "011Select"

011Select is the name of the query and this is without conditions. But if we had conditions then it would like the above but with the action being changing the record source as opposed to setting the value of one field on a form with the value of another field on another form.

So for the change of record source your condition might look like

[forms]![YourFormName]![EmployeeNameEnterField] Like "Dolly"

You might have six of them and only one will run.

Usually if you do this sort of thing rather than having Dolly entered you reference a field name so you can change empoyees names without having to change the code or macro.

This one, with condition, is setting the caption for the form.

If (Forms!SelectExpACL!ComparisonType Like "AMP") Then
Forms!SelectExpACL.Caption = "Zurich Vs AMP"
End If

With queries you can base the criteria on an entry (or entries) in a field on a form.

So in the query criteria would go [forms]![FormName]![FieldName]

If the entry in the FieldName was Andrews then the query would select all the records with Andrews.

The above actions that set the Caption and the RecordSource of the form are only 2 of heaps of them. Basically, whatever you can do to a form is Design view you can do with code or a macro. For example, if Dolly enters here name you might set the value of the back colour of the form to be pink but if Tom enters his name then the back colour of the form is red.

The form set value actions all look similar

Forms!SelectExpACL.Caption
Forms![2ProspectT].RecordSource

You can have things like

[form]![FormName]![TextBoxName].visible =No and it is invisible and Yes it is visible.

That should keep you going for a while

 

[SeaRox]

Mike375,

Thank you. Now I only have to update one file and it works for everyone! What a time saver!

SeaRox