Hi,

what I would like to know is: if I have a form that is used as a menu, and on this form I have to choose from multiple action buttons to go to the next step, but the action button to use is based on the last input of the underlying tables. Is it possible to have a label on the form under each action button that tells me on wich date the last input has been made?

There will probably be multiple inputs on the same date, but I would like a label on the form that shows me which date has been inputted last... The action button that the user will have to choose will be the one were the date that is placed under the button will be the date that is the most far in the past.

I don't know if this is clear or not.

Yep.

Create a module called "Public"

In the module, add the public variables you want to populate that you want to refer to from form to form.

Public dtMyDate As Date

Then on the on click event of the previous form, add this line:

dtMyDate = Me.WhateverDateFieldYouWant

No need to add Dim statements for the variable since that is already done in the module.

Then on the downstream forms, use a text box, remove the label from the text box, format the textbox to llok like a label, and then on the On Load event of the form, add this line:

Me.TextBoxLookingLikeALabel = dtMyDate

You might be able to make a real label show up with this code as well.

Did that help?

mmcdonal

the last part I understand, but could you give me a little bit more info on what to write in the module?

There will be 5 dates to lookup. They will come from the tables "tbl1" "tbl2" "tbl3" "tbl4" and "tbl5" and in each tables there will be a date field. So I need that on the form that will contain the 5 action buttons, that the dates will be put under these buttons with the text box you told me... Every time it has to look in the table to find the date that is closest to today's date (or equal to of course).

I don't know if it is clear for you this way...

I am not sure what you mean about the look ups and the buttons, but it sounds like you just want to make a parameter form so that people can open reports after selecting the parameters.

In the module, put this, for the Date variable you want to transfer around:

Public dtMyDate As Date

Or

Public dtMyDate1, dtMyDate2, dtMyDate3, dtMyDate4, dtMyDate5 As Date

if you want 5 date variables. Just put that line in below

Option Compare Explicit

When you do that, a blue line will appear below the statement. These become public variables that all of your events can read and write to without you having to delcare them on every form and report. Once you write a value to the variable, it stays there until you close the app, so you can read it anytime after. I think the data is that persistent, anyway. With a normal variable within a sub, they are usually only available within the sub, and then go out of scope as soon as the sub exits.

So if you write a value when the user clicks the button, then it will be there in the variable when you read it when the report of next form opens.

For the date look ups and buttons to open reports or forms, there are several ways to pass those values. Is that what you are after now?


mmcdonal

Let me explain to be sure u understand what I mean.

I have a from "frmInlogBA06". On this form I have multiple actions buttons that opens another form. The actions buttons are called "cmdBubble1", "cmdBubble2",... till 5. Every action button opens a different form. "cmdBubble1" opens the form "frmBubblesBA06Bubble1",...

The user will have to choose for each day in which 'bubble' he has to work. The action button to press will be the one were the input has been done the longest date in the past. So if for exemple the last input dates are

bubble1 01/21/07
bubble2 01/22/07
bubble3 01/18/07
bubble4 01/19/07
bubble5 01/20/07

Then the user will have to press the "cmdBubble3" action button.

What I want is that these days are shown under the action buttons, so the user can see which action button to press by looking at the dates...

If you find a way to disable the action buttons by referring to the dates it would be even nicer, so only the action button where the longest date in the part is located under is 'active', but this is optional.

Do you understand what I mean now or not? I guess I will just have to use a text box under each action button, but I don't know how to get these dates to show up...

You can do this. Don't lose faith here. WHERE are the dates coming from? Did you put them in Public Variables as we suggested? Let me know where the data is coming from and we can move forward from there. Everything you want to do is possible.

mmcdonal

I didn't input them, but I can do it if it is needed. Didn't want to start because I was not sure you understood what I meant...

There are 5 tables named "tblBubblesBA06Bubble1"... till 5. In each table there are fields, but the only one that I'm interested in for the search would be the date field...

So it has to take the most recent date in the date field of "tblBubblesBA06Bubble1" and return this date under the text box (I guess it will be a text box) of the "cmdBubble1" command button. And the same for the other 4.

Do you have everything needed?

Ooooooh, that is easy. Here is an easy and robust way to do that:

Create 5 queries, one on each table, and select the date column and the PK column from each table. Then sort the Date Desc (I think most recent first is descending). Then go to the Query properties, and select Top 1. You will get ties with this, so if there is more than one record with the same date, you will get more than one record in your results. If only one record is going to have the most recent date, then it is not a problem.

Then turn these into Append queries, and create a table that they can go into with these fields: PK, Date. Also create a delete query and run that first to empty this temporary table each time this runs.

Then just pull the top date from this table, not all 5. Why give your users an option to choose the wrong date.

Does that help? Then just pop open the downstream form, and no user intervention is needed to open the wrong form. Or did you want to give them the option to open the wrong form?





mmcdonal

You are kind of losing me here...

The only thing I want is the date to be shown under each action button. The action buttons have to stay unlocked, the user can use the action button he wants...

Also it seems I can't have a top 1 value... I have to choose between 5, 25, 100, 5%, 25% and all.

You kind of lost me at:

Then just pull the top date from this table, not all 5. Why give your users an option to choose the wrong date

You can type in "1" instead of using the selection for the Top value. Access allows items not in the list. Just type 1<space><enter>

You said you only wanted the user to click the button with the earliest date. Why not just go get that date? Or do you want them to be able to open each table to the earliest date?

mmcdonal