Adding timeframes to database

[Dalocky]

I have a set of timeframes under a priority. How can I get the date to
default when a priority is selected, eg Priority is 8 weeks, due date from
today would be?????

 

[Dorian]

Look up the date functions in Access Help, specifically the DateAdd and
DateDiff functions.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dalocky]

Thanks Dorian

Have tried this and I kept getting caught out with the second date as it is
shown as 1 week (7 days) or 8 weeks (60 days). How can I do the due date
with the priority required (selected).

Ness

 

[kc-mass]

Let's assume you have a combo box, cboPriority where you
pick priorities. The number of days is in column1
(remember it's zero based). Priority1 is 7 days, Priority2 is 30
days, Priority3 is 60 days or whatever. Your math would then
be something like:
dteNeeded = dateadd("d",ME.cboPriority.Column(1),Date())

That would add the number of days related to your priority
designator to today's date and return a date.

Regards

Kevin

 

[BruceM via AccessMonster.com]

It's not clear what the two columns are in the Row Source. The approach
could work, but the details are unclear.

Let's assume you have a combo box, cboPriority where you
pick priorities. The number of days is in column1
(remember it's zero based). Priority1 is 7 days, Priority2 is 30
days, Priority3 is 60 days or whatever. Your math would then
be something like:
dteNeeded = dateadd("d",ME.cboPriority.Column(1),Date())

That would add the number of days related to your priority
designator to today's date and return a date.

Regards

Kevin

Thanks Dorian

[quoted text clipped - 16 lines]

 

[PieterLinden via AccessMonster.com]

I have a set of timeframes under a priority. How can I get the date to
default when a priority is selected, eg Priority is 8 weeks, due date from
today would be?????

set the value in the AfterUPdate event of the control Use DateAdd to
calculate the new date

 

[Dalocky]

Thanks Guys

I have written the code as

Private Sub_DueDate
Dim strPriority As String
Dim strDuedate As Date
dteNeeded = DateAdd("d", cboPriority.Column(1), Date)
dteNeeded = DateAdd("d", cboPriority.Column(2), Date)
dteNeeded = DateAdd("d", cboPriority.Column(3), Date)

I know I am missing something. It has been a couple of years since I played
in Access with VB.

 

[BruceM via AccessMonster.com]

The code you have written assumes a combo box Row Source of at least four
columns, with numbers in the second, third, and fourth columns. The first
column would be numbered Column(0), the second Column(1), etc. The combo box
properties would have to allow for the correct number of columns in Column
Count, where you would add the actual number of columns (i.e. it is not zero-
based numbering on the property sheet). Use the Column Widths to show or
hide columns.

BTW, you have declared two variables, strPriority and strDueDate, that you do
not use in the code; and you have what seems to be an undeclared variable:
dteNeeded. On another point, it will be clearer when reading the code if you
use different prefixes for different data types. For instance, strPriority
would be a string (text), and datDueDate would be a date.

You could have a two column Row Source query like this:

4 Weeks 4
8 Weeks 8

The Column Count would be 2, and the Column Widths something like 1.5";0"

The combo box (cboPriority) After Update event could be:

Me.TextBoxName = DateAdd("ww",Me.cboPriority.Column(1),Date)

If you want to use days or other time units (along with weeks) for DateAdd
you could have this:

5 Days 5
4 Weeks 28
8 Weeks 56

The After Update expression would be:

Me.TextBoxName = DateAdd("d",Me.cboPriority.Column(1),Date)

TextBoxName is an unbound text box in which you display the expression result.


You could adjust the expression depending on whether it is days, weeks, or
months, but that would involve some extra steps.

A futher thought is that you could have this as the Row Source:

5 5 Days
28 4 Weeks
56 8 Weeks

Column Widths 0";1.5:
Column Count 2
Bound Column 1

If the value you need is in the bound column, no need to specify the column:

Me.TextBoxName = DateAdd("d",Me.cboPriority,Date)

You could also add the number to an unbound text box, and select the unit of
time from a combo box. I won't get into details, but rather want to point
out there are a lot of options, depending on your specific needs.

 

[Dalocky]

Thanks Bruce, that helped heaps.

Stupid question, having a blonde moment what is Me???

Ness

 

[BruceM via AccessMonster.com]

Not a stupid question at all. It's not intuitive at all.

The Me prefix followed by a dot (Me.) in a form's code module (the collection
of code used by the form) means that what follows is a property of the form.
Controls (text boxes, combo boxes, lines, and pretty much anything you can
put on the form) are considered properties of the form, as are things such as
AllowEdits, Recordsource, and quite a few others. If you set the form's
Allow Edits property to False on the form's Property sheet, you can have code
in a command button to set it to True. The command button Click event would
have:
Me.AllowEdits = True

Or you could change the record source:

Me.Recordsource = "QueryName"

The command button code is in the form's code module, so the Me prefix tells
Access to allow edits for the form to which the code module belongs. It does
not affect AllowEdits in any other forms.

If you use the Me prefix followed by a text box name, what follows is a
property of the text box. If you type Me. you should see a list of
properties including RecordSource, but not ControlSource. After the text box
name and a dot you will see ControlSource, but not RecordSource. A form has
a Record Source but not a Control Source; vice versa for a control. The
following code (in a command button Click event, for instance) will set the
Control Source of the text box Text1 to the field Comments, which we will
assume is in the form's Record Source table or query:

Me.Text1.ControlSource = "Comments"

As another example, you can hide Text1:

Me.Text1.Visible = False

There is more, depending on the details of what you are doing, but I will
mention a few things. Access Help shows Me! rather than Me. in some examples.
Generally, you can use the dot rather than the bang (!). I won't try to get
into the subtleties of that just now, but can direct you to an article on the
topic if you are interested.

Using the dot produces a list of choices. I have heard this referred to as
the Intellisense menu. The bang does not produce the menu. The menu
contains properties of the form, but also user-defined functions and some
Methods such as Requery. I have to admit I don't know why some Methods are
listed and others are not.

In many cases you do not absolutely need to use the Me prefix, but if you do
not, Access has to sort out whether you are referring to a variable, property,
method, control, or whatever. The Me prefix narrows the list, and should
make the code run faster, although often the difference will be minor.

Thanks Bruce, that helped heaps.

Stupid question, having a blonde moment what is Me???

Ness

The code you have written assumes a combo box Row Source of at least four
columns, with numbers in the second, third, and fourth columns. The first

[quoted text clipped - 71 lines]