Auto values in other fields

[Sgwapt]

Working on a system that reports customer complaints.
In a Excel version there is a report number automatically generated.
This number is unique so a primary key can be set in access for this value.

How it get the value is simple;
User enters the date in Date Rec'd field
This sets a Year and Month value in their respected fields.
Then it sums the number of entries based on year and month values in data
table then add 1 to create the index number.

The result equals six digit date "yymmdd - Indx No"

If I have a table in access that has year and month fields.
What is the best method to use to auto input the year and month value in
these fields after the user enters the date rec'd' value. The users form does
not show these fields but are in the table.

I have tried "Default Value" in the table field but my syntax is wrong
Tried Marco and also VBA but my knowledge is lacking.

The best i have so far is a query that counts the number of entries in the
DateRecd field but the result is by day not month, (i.e. Count(Year[DateRecd])

Your help is very appreicated.

thanks

 

[Pat Hartman\(MVP\)]

Is there some reason you don't want to use an autonumber? Creating complex,
multi-value attributes to serve as primary keys just leads to problems in
the long run.

 

[Sgwapt]

Pat,

I propabaly could use auto number if the system can find values based upon
the report number created. Say I used autonumber but added a field to contain
the report number. Could I find the recordset if the user selected the report
number?
If yes, then autonumber is fine but then still would need to know how to get
access to make this report number based on user input.

In Excel I used MATCH(value,range,0) ti return the row then INDEX to return
any other field value from that row. This worked very well but now I am
trying to move things over to access.

thanks for your assistance.
--
George G

Is there some reason you don't want to use an autonumber? Creating complex,
multi-value attributes to serve as primary keys just leads to problems in
the long run.

Working on a system that reports customer complaints.
In a Excel version there is a report number automatically generated.
This number is unique so a primary key can be set in access for this
value.

How it get the value is simple;
User enters the date in Date Rec'd field
This sets a Year and Month value in their respected fields.
Then it sums the number of entries based on year and month values in data
table then add 1 to create the index number.

The result equals six digit date "yymmdd - Indx No"

If I have a table in access that has year and month fields.
What is the best method to use to auto input the year and month value in
these fields after the user enters the date rec'd' value. The users form
does
not show these fields but are in the table.

I have tried "Default Value" in the table field but my syntax is wrong
Tried Marco and also VBA but my knowledge is lacking.

The best i have so far is a query that counts the number of entries in the
DateRecd field but the result is by day not month, (i.e.
Count(Year[DateRecd])

Your help is very appreicated.

thanks

 

[Tim Ferguson]

How it get the value is simple;
User enters the date in Date Rec'd field
This sets a Year and Month value in their respected fields.
Then it sums the number of entries based on year and month values in
data table then add 1 to create the index number.

It's a simple enough use of the DMax() function:

' find what was entered last time; nb using a Variant
varNewNumber = DMax( _
"SerialNumber", _
"Reports", _
"YearNum = " & txtYearNum & " AND MonthNum = " & txtMonthNum)

' See if this is the first one for this month
If IsNull(varNewNumber) Then varNewNumber = 0

' Okay, we need the next one, not the last one
txtSerialNumber = varNewNumber + 1


This can all be compressed into a single line if you prefer. The trick,
of course, is to remember your first normal form and keep the three
separate data items in three separate fields. It's easy enough to create
the three-field primary key, and for display purposes you can just
concatenate them into a single text box.

Hope that helps


Tim F

 

[Sgwapt]

Tim,
Thanks, this will help very much