Conditional setfocus

[Emilia Maxim]

"Mike Mueller" <mikemueller@ecinet.net> wrote:

>I have a simple form for entering monthly pressure tests.
>There are only 2 fields of concern to data entry for any
>given month, cylinder number and monthly pressure. The form
>has fields on it for all months of 2003 and 2004. I want
>the form to go to the current month's field automatically.
>I currently change the code monthly for the setfocus. The
>remaining months are not tab stops so as soon as the current
>field is done, TAB takes you to the next record.
>
>Control Field= cylinder
>monthly fields- named on a month_year format, eg June_2003,
>July_2003 etc

Mike,

do you actually have table fields for each month for 2 years in one
record? If so, you should ASAP change your data structure. You should
have a field for the year (containing 2003, 2004 etc., may be set per
default) and one for the month, containing preferably 1, 2, ...12.
This way you can add records even in 2005 and later without having to
add new fields (besides, the number of fields in a table si limited).
Not to mention if there is a change some time requiring more data per
month.

Leaving the table structure as it is will cause big problems when
you'll need sorted lists, groupings etc.

As for putting together the field names dynamically, here is some
code:

Dim strFldName As String

'Assign the current month' full name to the variable
strFldName = Format(Month(Date), "mmmm")

'Concatenate the current year
strFldName = strFldName & "_" & Year(Date)

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
[url]http://www.maxim-software-service.de[/url]

[Mike Mueller]

: "Mike Mueller" <mikemueller@ecinet.net> wrote:
:
:: I have a simple form for entering monthly pressure tests.
:: There are only 2 fields of concern to data entry for any
:: given month, cylinder number and monthly pressure. The
:: form
:: has fields on it for all months of 2003 and 2004. I want
:: the form to go to the current month's field
:: automatically.
:: I currently change the code monthly for the setfocus.
:: The remaining months are not tab stops so as soon as the
:: current
:: field is done, TAB takes you to the next record.
::
:: Control Field= cylinder
:: monthly fields- named on a month_year format, eg
:: June_2003, July_2003 etc
:
: Mike,
:
: do you actually have table fields for each month for 2
: years in one record? If so, you should ASAP change your
: data structure. You should have a field for the year
: (containing 2003, 2004 etc., may be set per default) and
: one for the month, containing preferably 1, 2, ...12.
: This way you can add records even in 2005 and later
: without having to add new fields (besides, the number of
: fields in a table si limited). Not to mention if there is
: a change some time requiring more data per month.
:
: Leaving the table structure as it is will cause big
: problems when you'll need sorted lists, groupings etc.
:
: As for putting together the field names dynamically, here
: is some code:
:
: Dim strFldName As String
:
: 'Assign the current month' full name to the variable
: strFldName = Format(Month(Date), "mmmm")
:
: 'Concatenate the current year
: strFldName = strFldName & "_" & Year(Date)
:
: Best regards
: Emilia
:
: Emilia Maxim
: PC-SoftwareService, Stuttgart
: [url]http://www.maxim-software-service.de[/url]

Thank You for the code. You kind of lost me on the first
part. I am reading it to say that I should only have 1
field per year and then 1 field per month. I don't
understand how I would be able to access the reading from
any particular month.

[Emilia Maxim]

"Mike Mueller" <mikemueller@ecinet.net> wrote:

>Thank You for the code. You kind of lost me on the first
>part. I am reading it to say that I should only have 1
>field per year and then 1 field per month. I don't
>understand how I would be able to access the reading from
>any particular month.

Mike,

it's worse: 1 field per year and month! :-)

With the info you gave until now, here is an example how the data
would be stored in the table:

MyYear MyMonth CylinderNo Pressure
---------------------------------------------
2003 1 01 123.45
2003 1 02 456.78
2003 2 01 345.345
2003 2 02 111.11
2004 1 01 222.22
2004 1 02 333.33

etc.

Now for ex if you would want to see the data for a given year, you can
use such a query:
SELECT * FROM MyTable WHERE MyYear = 2003

If you want to see the data for a given year and a given month, then:

SELECT * FROM MyTable WHERE MyYear = 2003 AND MyMonth = 12

If you would want to average the data for a given year and a given
cylinder, then (untested, so beware of correct syntax!):

SELECT Avg(Pressure) FROM MyTable GROUP BY MyYear WHERE Cylinder=01
AND MyYear = 2003

If you would want to see the data for a given month over the years,
then:

SELECT * FROM MyTable WHERE MyMonth = 4

In all these examples you can use a form control or a function instead
of explicit 2003 or month 12 or cylinder 01.

To enter the data you could use a continuous form where the user
enters a new record for each cylinder per month. You could fill the
current year by setting the DefaultValue property of the control to:
=Year(Date), same for the month:
=Month(Date) (this returns the current month).

And if you would want to display the real names for the months, you
could use a combo with a 2 columns value list: number and name, like
this:

1;January
2;February
3;March

etc., sorted as you like, first column bounded and hidden. You would
store the number in the table and the user sees the friendly name.
This would help you when you need sorting the months chronologically.

And the name of all this theory is: a database is not a spreadsheet
:-) Please don't feel offended, but this is the core of it, really.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
[url]http://www.maxim-software-service.de[/url]

[peter walker]

There are heaps of ways to do this.
A simple way is to name the month fields
mnth1
mnth2
mnth3
....
mnth12

the code below will do the job...

Private Sub Form_Open(Cancel As Integer)
Me.Controls("mnth" & DatePart("m", Date)).SetFocus
End Sub

you can alway play with
? format(date(),"mmm") returns "Jul"
? format(date(),"mmmm") returns July"
....thus controls with the month name as part of the name can be use as well.
--

peter walker MVP

Please post replies to the news group so everyone can benefit.
[url]www.papwalker.com[/url]

"Mike Mueller" <mikemueller@ecinet.net> wrote in message
news:ukMBF5AQDHA.1552@TK2MSFTNGP10.phx.gbl...

> I have a simple form for entering monthly pressure tests.
> There are only 2 fields of concern to data entry for any
> given month, cylinder number and monthly pressure. The form
> has fields on it for all months of 2003 and 2004. I want
> the form to go to the current month's field automatically.
> I currently change the code monthly for the setfocus. The
> remaining months are not tab stops so as soon as the current
> field is done, TAB takes you to the next record.
>
> Control Field= cylinder
> monthly fields- named on a month_year format, eg June_2003,
> July_2003 etc
>
> Mike
>
>