Viewing Forms with command buttons

[CC801340]

I have one table called "Response" which has contains a date field. I have a form called "Response View" which is used to view the data in this table. I have another form called "Menu" which has 12 command buttons (JAN-DEC). I want to be able to click on the (eg) JAN command button and that bring up the "Response View" form but only display the records where the date equals 01/01/01-31/01/01 (only contains and will ever only contain 2001 data).

What is the easiest way to do this?

Many thanks.

 

[macgood31]

1. In this form, for each button(e.g button1- button12) add tag=1 ...to 12
2. add a module
function display(a_month as string) as string
a_sql="select * from Response where month = '" & a_month & "'"

form."Response View".recordset = a_sql
form.refresh
end funtion
3. this is idea, I have no time to check the syntex, But I used this before.

Good luck

 

[HoleInTheFoot]

Simplest way is

1) to base your Response View form on a select query which looks at the Response table i.e. "responsequery".

2) add two text box's to the Menu form (text1 and text2)

3) in each of the 12 buttons add the following in the On Click Event :

Dim stDocName As String
Dim stLinkCriteria As String
Me![Text1] = Min date i.e. for the Jan button "01/01/2001"
Me![Text2] = Max date i.e. for the Jan button "31/01/2001"
stDocName = "Response Query"
DoCmd.OpenForm stDocName, , , stLinkCriteria

4) in the "responsequery" add the following :
WHERE ((([Response].[Date]) Between [Forms]![Menu]![Text1] And [Forms]![Menu]![Text2]))

 

[HoleInTheFoot]

Simplest way is

1) to base your Response View form on a select query which looks at the Response table i.e. "responsequery".

2) add two text box's to the Menu form (text1 and text2)

3) in each of the 12 buttons add the following in the On Click Event :

Dim stDocName As String
Dim stLinkCriteria As String
Me![Text1] = Min date i.e. for the Jan button "01/01/2001"
Me![Text2] = Max date i.e. for the Jan button "31/01/2001"
stDocName = "Response View"
DoCmd.OpenForm stDocName, , , stLinkCriteria

4) in the "responsequery" add the following :
WHERE ((([Response].[Date]) Between [Forms]![Menu]![Text1] And [Forms]![Menu]![Text2]))

 

[CC801340]

I have tried the above and it works for some months. However, for some unknown reason half the command buttons return "Run-time error 2501 - The OpenForm action was cancelled" when i click on them. The code (apart from the dates!) is exactly the same.

Any ideas?!

 

[CC801340]

Found out why it wasnt working for all months - any months that DONT have 31 days in, need the Max date part changing to the 30th or 28th for feb.

However, when i now come to do a filter...i am prompted twice to enter figures for the two text boxes - anyone know a way around this??

 

[jfreak]

I`ll send this to you my friend.....

 

[jfreak]

Well my fren...it seems like the world is small afterall....

Good luck....