MS Access Form with Dynamically generated buttons

[ghadley_00]

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
gh********@yaho o.com

[Ed Robichaud]

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

<gh********@yah oo.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.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
gh********@yaho o.com

[Tim Marshall]

gh********@yaho o.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.openrepor t "rptWhateve r"

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,"Err or 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!frmMainfo rm.CustId
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto