MS Access database support - MS Access Form with Dynamically generated buttons

Author

MS Access Form with Dynamically generated buttons

ghadley_00@yahoo.com

2006-04-08, 7:27 am

Hi,

I have a database with a main table and a lookup table of customers,
and reports for each of the customers listing each transaction they
have listed.

I have a form that has buttons for each of the reports that is written
by hand.

At present the number of customers can be altered manually, but that
requires a button to be added or removed from the form.

Can anyone recommend a way to make a form that will dynamically add or
remove buttons (or make a drop down list) based on the entries
currently in my table of customers such that the appropriate report
gets accessed?

In other words, if there are 3 customers in the lookup table, the form
should have only 3 buttons, each of which is labeled using a Dlookup
command for the proper text label (I already have this working
statically), and pressing on each of these buttons will open a report
with the appropriate parameter passed to it. And if the number of
customers increases, then additional buttons appear on their own.

Best wishes,

George Hadley
ghadley_00@yahoo.com

Ed Robichaud

2006-04-08, 11:28 am

Your method will quickly become unusable. What happens when there are 200
customers? Why not have ONE button (labeled "Selected Customer" or whatever
you like) and use the linkcriteria property to run the appropriate report?
-Ed

<ghadley_00@yahoo.com> wrote in message
news:1144498376.358690.150060@e56g2000cwe.googlegroups.com...
> Hi,
>
> I have a database with a main table and a lookup table of customers,
> and reports for each of the customers listing each transaction they
> have listed.
>
> I have a form that has buttons for each of the reports that is written
> by hand.
>
> At present the number of customers can be altered manually, but that
> requires a button to be added or removed from the form.
>
> Can anyone recommend a way to make a form that will dynamically add or
> remove buttons (or make a drop down list) based on the entries
> currently in my table of customers such that the appropriate report
> gets accessed?
>
> In other words, if there are 3 customers in the lookup table, the form
> should have only 3 buttons, each of which is labeled using a Dlookup
> command for the proper text label (I already have this working
> statically), and pressing on each of these buttons will open a report
> with the appropriate parameter passed to it. And if the number of
> customers increases, then additional buttons appear on their own.
>
> Best wishes,
>
> George Hadley
> ghadley_00@yahoo.com
>

Tim Marshall

2006-04-08, 1:28 pm

ghadley_00@yahoo.com wrote:

> In other words, if there are 3 customers in the lookup table, the form
> should have only 3 buttons, each of which is labeled using a Dlookup
> command for the proper text label (I already have this working
> statically), and pressing on each of these buttons will open a report
> with the appropriate parameter passed to it. And if the number of
> customers increases, then additional buttons appear on their own.

The only way I can think of doing this is using a continuous form or
subform that is bound to your customer table. You'd have to use form
view (not datasheet view) and on each detail you'd have a button. You
would *not* be able to have the proper label text on the button. This
is because buttons cannot be bound to data. Rather, what you'd do is
have a text box and then your button with a cutesy image or just plain.
Perhaps with controltip text like "click me for a report on this
customer".

Assuming you customer table has a structure with fields like like
Cust_Pk, Cust_Name, the Cust_Name being the full customer name would be
the controlsource for the text box. The button would have code
something like the following air code:

Sub btnCustReport()

'Opens a customer details report with the report
'based on the current Cust_Pk

on error goto err_Proc

docmd.openreport "rptWhatever"

Exit_Proc:

Exit sub

Err_Proc:

select case err.number

case 2501 'report is cancelled

resume exit_Proc

case else

msgbox "Error " & err.number & " " & err.description, _
vbcritical,"Error on Opening Report", err.helpfile, err.helpcontext

resume exit_Proc

end select

end sub

Your report, instead of perhaps being "hand written" should have a
recordsource SQL statement or saved query such as:

Select <blah, blah>
From <whatever>
Where Cust_Id = forms!frmMainform.CustId
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Sponsored Links