Formula ?

[Tom]

Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Steve Schapel]

Tom,

Ok, thanks. Well, that wasn't what I was looking for! I hope you
understand what I was driving at here... You have showed us some code
which is a user-defined function. You want a certain value entered to
the Run Number field in a new record. But the function does not do this
by itself, and the simple existence of the function does not achieve
anything at all. Somewhere along the line, something is being "done" in
order to use the function in the process of assigning the Run Number
value. How this is being done, I still have no idea about, and I just
wondered whether the actual method or timing of this procedure may have
been contributing to the problem.

But anyway, as discussed elsewhere in the thread, it looks like the
absence of the leading 0 on the Run Number is a source of at least part
of the problem, so needs to be fixed. It has occurred to me that the
Run Number is a number data type, in which case a leading 0 will not be
possible. You could change it to a Text data type (which is probably
more normal for such a field anyway). Or you could change the code in
the GetNextNumber() function, where it defines
strCurrDate = Format(Date, "yymmdd")
I think this would do it...
strCurrDate = Format(Val(Format(Date,"yymmdd")))

 

[Tom]

Steve,

I thought this might be a better way to handle the problem.
Navigating these discussion logs is time consuming.

I'd like to e-mail you an attachment of the file in question.
That way you can look at everything.

Would that be OK with you?

Thanks,

Tom Pickett
ABLE Medical Transport
614-670-1151 (cell)

 

[Steve Schapel]

Tom,

At this stage I think it would be good to continue liaising via the
newsgroup. Especially since we have reached the point of identifying
specific actions you could take to help the situation.

Is the Run Number field in your table a number or text data type? If
number, any reason it shouldn't be changed to text? If text, any reason
for not entering the leading 0?

 

[Tom]

Steve,
Here is the code in the module.

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = Format(Val(Format(Date, "yymmdd")))
varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6)
='" & strCurrDate & "'")
If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")
End If
End Function

I've decided to make this easy, and it will problably be more
benificial when it comes time for relationships, and create a
field next to the date, that will be just for the last 3 digits.
The finished product should be in the format:
yymmdd 001
So if you could tell me what part of the code to keep for just
the date automatically changing at midnight and in the format
yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001
just need the yymmdd.

And could you tell me a counting code that will start at 001 at
midnight and increase by 1 for every record recorded, that
will make this alot easier. It should revert to 001 at midnight
when the date changes.

Unless we can make the yymmdd001 change with each new record,
that might be an easier way to go.

You've been a big help so far, thanks for your patience.
Tom

 

[Steve Schapel]

Tom,

Regarding "unless we can make the yymmdd001 change with each new
record", it is actually very easy to do this, and I can show you how if
you're still interested. But now we have started down another track,
which is probably a better one anyway.

My understanding is that the new records in the ABLE_Table1 table are
entered via a form. Is this correct?

There is also apparently an assumption that there will never be more
than 999 new records in any given day. Is this correct?

If it was mine, I would never have had a function like your
GetNextNumber() function in the first place. I recommend you should
delete it completely.

Now that you have decided to make two separate fields, one should be a
Date/Time data type and the other a Number data type. Let's say these
fields are called RunDate and RunNumber.

Ok, here's how I would do it...

1. Set the Default Value property of the RunDate control on the form to...
Date()

2. Set the Format property of the RunDate control on the form to...
yymmdd

3. Set the Format property of the RunNumber control on the form to...
000

4. Alter the code on your Command85 button, like this...
Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub

By way of explanation, the actual data stored in the RunDate and
RunNumber fields in the table will be a date and a number, which will
look like a date and a number if for some obscure reason you were poking
around in the table. In my opinion, this is as it should be. For your
purposes on form and report, you want the date to be shown without /s
and so forth, and you want the number to be shown as 3 digits with
leading zeros. This does not mean that the date and the number should
be stored in the table as text. They should be stored as a date and a
number, and then getting them to appear the way you want is Formatting.
Formatting affects the appearance of the data, but does not affect the
value of the data.

--
Steve Schapel, Microsoft Access MVP

Steve,
Here is the code in the module.

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = Format(Val(Format(Date, "yymmdd")))
varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6)
='" & strCurrDate & "'")
If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")
End If
End Function

I've decided to make this easy, and it will problably be more
benificial when it comes time for relationships, and create a
field next to the date, that will be just for the last 3 digits.
The finished product should be in the format:
yymmdd 001
So if you could tell me what part of the code to keep for just
the date automatically changing at midnight and in the format
yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001
just need the yymmdd.

And could you tell me a counting code that will start at 001 at
midnight and increase by 1 for every record recorded, that
will make this alot easier. It should revert to 001 at midnight
when the date changes.

Unless we can make the yymmdd001 change with each new record,
that might be an easier way to go.

You've been a big help so far, thanks for your patience.
Tom

 

[Tom]

Steve,
Thank you! I'm learning, which I love to do.
Now for tweaking.
After entering several records, closing the program, and then reopening
the program, I notice that it will always start at 000. When I compare the
"RunNumber" with the actual count on the Record Navigation bar, it is way off.
When I look at the table, there are several duplicate RunNumbers with "0".

Several Questions:
1. Do I set the Indexed to "no duplicates" on the table?
2. How do I set the starting number to 001 and not 000
3. How do I set the RunNumber to always reset at midnight to 001
4. If I close the program, then reopen, is there a way to have it
always open with a new record for data entry?

Again, Thanks!
Tom

Tom,

Regarding "unless we can make the yymmdd001 change with each new
record", it is actually very easy to do this, and I can show you how if
you're still interested. But now we have started down another track,
which is probably a better one anyway.

My understanding is that the new records in the ABLE_Table1 table are
entered via a form. Is this correct?

There is also apparently an assumption that there will never be more
than 999 new records in any given day. Is this correct?

If it was mine, I would never have had a function like your
GetNextNumber() function in the first place. I recommend you should
delete it completely.

Now that you have decided to make two separate fields, one should be a
Date/Time data type and the other a Number data type. Let's say these
fields are called RunDate and RunNumber.

Ok, here's how I would do it...

1. Set the Default Value property of the RunDate control on the form to...
Date()

2. Set the Format property of the RunDate control on the form to...
yymmdd

3. Set the Format property of the RunNumber control on the form to...
000

4. Alter the code on your Command85 button, like this...
Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub

By way of explanation, the actual data stored in the RunDate and
RunNumber fields in the table will be a date and a number, which will
look like a date and a number if for some obscure reason you were poking
around in the table. In my opinion, this is as it should be. For your
purposes on form and report, you want the date to be shown without /s
and so forth, and you want the number to be shown as 3 digits with
leading zeros. This does not mean that the date and the number should
be stored in the table as text. They should be stored as a date and a
number, and then getting them to appear the way you want is Formatting.
Formatting affects the appearance of the data, but does not affect the
value of the data.

--
Steve Schapel, Microsoft Access MVP

Steve,
Here is the code in the module.

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = Format(Val(Format(Date, "yymmdd")))
varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6)
='" & strCurrDate & "'")
If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")
End If
End Function

I've decided to make this easy, and it will problably be more
benificial when it comes time for relationships, and create a
field next to the date, that will be just for the last 3 digits.
The finished product should be in the format:
yymmdd 001
So if you could tell me what part of the code to keep for just
the date automatically changing at midnight and in the format
yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001
just need the yymmdd.

And could you tell me a counting code that will start at 001 at
midnight and increase by 1 for every record recorded, that
will make this alot easier. It should revert to 001 at midnight
when the date changes.

Unless we can make the yymmdd001 change with each new record,
that might be an easier way to go.

You've been a big help so far, thanks for your patience.
Tom

 

[Steve Schapel]

Tom,

After entering several records, closing the program, and then reopening
the program, I notice that it will always start at 000.

Hmmm. I think the method I outlined will rely on all new records being
started via clicking the Command85 button. I suspect that the result
you are seeing is because of opening the form directly, and entering a
new record straight away... does that sound right?

When I compare the
"RunNumber" with the actual count on the Record Navigation bar, it is way off.
When I look at the table, there are several duplicate RunNumbers with "0".

Wanting the record navigator to correspond with anything related to your
data is a recipe for a headache.

Several Questions:
1. Do I set the Indexed to "no duplicates" on the table?

You will need a Primary Key inthe table. It can't be either the RunDate
or RunNumber fields, as they will both contain duplicates. You can set
*both* fields together as a composite primary key. Or you can add an
AutoNumber field to the table as the primary key. But you can't set the
Index just on the RunNumber to 'no duplicates', because there will be a
001 every day.

2. How do I set the starting number to 001 and not 000

Unless I've missed something, this should automatically be the case if
you always start a new record via clicking the Command85 button. If you
want to be able to start a new record by another route as well, you
would need to add some code somewhere (depending on what you want to
do), to ensure that the next RunNumber is allocated.

3. How do I set the RunNumber to always reset at midnight to 001

Unless I've missed something, this should automatically be the case with
the procedure I suggested.

4. If I close the program, then reopen, is there a way to have it
always open with a new record for data entry?

Does the application open with a "menu/switchboard/whatever" type of
form? Or do you want it to open straight to your data form? You can
set the Data Entry property of the form to Yes, but this won't be a good
way to go if you want to be able to scroll back to previously entered
records. You could possibly use an AutoExec macro to open the form as
soon as the application is opened, and this could also include assigning
the applicable RunNumber to the first record entered for the session.

 

[Tom]

Steve,

Right now, I have a shortcut on my desktop to the form.
When I open that up, that's when "000" appears everytime.
You are right about the command button, it does change the number
in the control.

What I need is a way for that control to generate the next available
number, in sequence, if I were to start up the program from scratch. Have
it search the database upon opening and plop that next number right into
the control. The command button is working fine.

I also noticed that when I click on the command button, that
the flashing cursor does not go to the 1st tab stop, on the next record.
When I first open the database it does, but not after I record a record.

A switchboard/ menu sounds like a great idea. I'm assuming, as a newbee,
that this is like a "program" front end, "Welcome to ABLE Medical Dispatch"
"select where you want to go" type of thing. With buttons for "Dispatching"
"Reports" etc. It sounds like I need some books, and I have a list. I just
wanted to get
started as we are going to begin operations in January.

Tom

 

[Steve Schapel]

Tom,

Right now, I have a shortcut on my desktop to the form.
When I open that up, that's when "000" appears everytime.
You are right about the command button, it does change the number
in the control.

You can handle this by putting similar code into the On Load event
property of the form itself. Something like this...
Private Sub Form_Load()
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub

I also noticed that when I click on the command button, that
the flashing cursor does not go to the 1st tab stop, on the next record.
When I first open the database it does, but not after I record a record.

When you move from record to record on a form, the focus stays on the
same control on the next/new record as it was on the previous. If you
want the behaviour to be different, you have to control it. The way to
do this would be to add a line to the Command85 code, like this...
Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec
Me.NameOfFirstControl.SetFocus
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub
(of course, you substitute the actual name of your control for the
'NameOfFirstControl' in the code)

A switchboard/ menu sounds like a great idea. I'm assuming, as a newbee,
that this is like a "program" front end, "Welcome to ABLE Medical Dispatch"
"select where you want to go" type of thing. With buttons for "Dispatching"
"Reports" etc.

Yes, that's exactly what I mean. A desktop shortcut to an Access form
is not a suitable final solution, you need to provide a user interface
to your application, and navigation facilities.

It sounds like I need some books, and I have a list.

Great idea. Do you have "Building Microsoft Access Applications" by
John Viescas on your list?

I just wanted to get
started as we are going to begin operations in January.

Well, I won't hold you up any longer... you've got a busy couple of
months ahead of you.

 

[Tom]

Steve,

You've been a great help. Thanks for your
time and patience!!

Tom Pickett, EMT-Paramedic
ABLE Medical Transport
740-574-5555

Tom,

Right now, I have a shortcut on my desktop to the form.
When I open that up, that's when "000" appears everytime.
You are right about the command button, it does change the number
in the control.

You can handle this by putting similar code into the On Load event
property of the form itself. Something like this...
Private Sub Form_Load()
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub

I also noticed that when I click on the command button, that
the flashing cursor does not go to the 1st tab stop, on the next record.
When I first open the database it does, but not after I record a record.

When you move from record to record on a form, the focus stays on the
same control on the next/new record as it was on the previous. If you
want the behaviour to be different, you have to control it. The way to
do this would be to add a line to the Command85 code, like this...
Private Sub Command85_Click()
DoCmd.GoToRecord , , acNewRec
Me.NameOfFirstControl.SetFocus
Me.RunNumber =
Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
Me.Dirty = False
End Sub
(of course, you substitute the actual name of your control for the
'NameOfFirstControl' in the code)

A switchboard/ menu sounds like a great idea. I'm assuming, as a newbee,
that this is like a "program" front end, "Welcome to ABLE Medical Dispatch"
"select where you want to go" type of thing. With buttons for "Dispatching"
"Reports" etc.

Yes, that's exactly what I mean. A desktop shortcut to an Access form
is not a suitable final solution, you need to provide a user interface
to your application, and navigation facilities.

It sounds like I need some books, and I have a list.

Great idea. Do you have "Building Microsoft Access Applications" by
John Viescas on your list?

I just wanted to get
started as we are going to begin operations in January.

Well, I won't hold you up any longer... you've got a busy couple of
months ahead of you.