Last entry +1 help....Reset when desired.

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:
*Equipment*
EquipID, autonumber
Equipment, text
etc
Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
..
..
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

strive4peace wrote:
Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad he
http://home.bendbroadband.com/conrad...abaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work* :-)

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? g), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far?? :-)

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow". :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ps.com...
So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:
*Equipment*
EquipID, autonumber
Equipment, text
etc
Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

strive4peace wrote:
Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.
And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Fletcher.


Graham Mandeno wrote:
Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad he
http://home.bendbroadband.com/conrad...abaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work* :-)

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? g), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far?? :-)

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow". :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ps.com...
So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:
*Equipment*
EquipID, autonumber
Equipment, text
etc
Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

strive4peace wrote:
Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

I figured out how to do multiple primary keys. If that's what you
meant for me to do. If it's not, let me know.
Fletcher wrote:
Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.
And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Fletcher.


Graham Mandeno wrote:
Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad he
http://home.bendbroadband.com/conrad...abaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work* :-)

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? g), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far?? :-)

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow". :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ps.com...
So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:
*Equipment*
EquipID, autonumber
Equipment, text
etc
Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

strive4peace wrote:
Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Fletcher" wrote in message
ups.com...
I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

Hi Fletcher

I'll answer you questions and points inline...

"Fletcher" wrote in message
oups.com...
Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me! :-)

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell me
how many times this FSI has been recharged in the last year?", then I would
be the first to say "I told you so!" :-)

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on ToolsRelationships. Then you can add tables to the window
(Viewshow tables) and drag the mouse from the primary key of one table to
the related field (foreign key) of another.


One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay, this is probably the most productive discussion I've had about
this since I've begun. Thank you Graham. I'll answer your questions
and add some of my own to the mix. And I'll concede to the operators
table...I think it's a good idea now that you explained it more.

Firstly, I'll explain an FSI and Runs like you asked at the end of your
last post.
An FSI is a cleaning device that can clean up to 6 runs (aka lots to
some people, it just depends on your preference, but everyone who works
here understands either) which all have a unique run number associated
with it that is carried throughout the facility. A run is also a group
of 25 potential products.

So that can bring us to my last question: What would happen if a run
number went through an FSI more than once (e.g. it was cleaned more
than once, which happens very regularly)? I believe that it wouldn't
matter since the PassID and RunNumber will both be primary keys and
therefore I will never have a single run number going through the same
pass and never creating a duplicate entry in *Runs*. As well as the
chance that it will go through the same peice of machinery (FSI).

I hope that's enough. I'm not really allowed to disclose what it is
that we do because of government contracts. Sorry that I can't go more
in depth.

Now, to move back into the discusion:
You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?
I'll put a unique identifier, but I am very reluctant to use auto
number because I've found that it can become slightly messy if edits or
deletions are necessary. Do you have any suggestions on this? Also,
how would I calculate the number of passes since the last recharge and
display this value on a form (which was the ultimate goal of this
thread, and I am determined to figure this out)?

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time
Check, but how do I tell it (or have it retrieve) what the last
recharge date was?

And I'm just starting to run this through my head: how do I keep track
of the need for each FSI to be recharged. Since there are 3 machines
and the information for every pass on all 3 are stored in the same
table, how will I sort out which FSI has had 5 passes since recharge
and which has had 19 since recharge?

Here is my updated table structu
*Passes*
PassID, Number, Primary Key, link to *Runs*PassID
FSINumber, Number, link to *FSIs*FSINumber
DateTimeOut, Date/Time, default Now()
Operator, Text, Link to *Operators* (I'm not sure if I should link
this to peratorID or OperatorInitials)
Pre, Number
Post, Number
Comment, Memo

*Operators*
OperatorID, AutoNumber, (maybe) link to *Passes*Operator
OperatorName, text
OperatorInitials, text (maybe) link to *Passes*Operator

*Runs*
PassID, Number, link to *Passes*PassID
RunNumber, Number

*FSIs*
FSINumber, Number, link to *Passes*FSINumber
RCHGDate, date/time

If you see anything wrong, please point it out. And could you tell me
which link would be better on *operators*?

Thanks for your help, it's greatly apreciated.


Graham Mandeno wrote:
Hi Fletcher

I'll answer you questions and points inline...

"Fletcher" wrote in message
oups.com...
Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me! :-)

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell me
how many times this FSI has been recharged in the last year?", then I would
be the first to say "I told you so!" :-)

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on ToolsRelationships. Then you can add tables to the window
(Viewshow tables) and drag the mouse from the primary key of one table to
the related field (foreign key) of another.


One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand