Go to new record on a new day

[Dale Fye]

Guys,

Thanks for this discussion. I has been very enlightening. I'll do some
tests on my own as time permits.

Dale

 

[Opal]

Did you test using perfmon.exe to watch the network traffic?
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

Arvin,
Any particular reason that you are opening a recordset rather than
just using DCOUNT?
Yes, the Domain functions are much slower than pulling recordsets
because they don't take advantage of indexes. [snip]

Cite? Testing on a large sample table before and after adding an index
clearly shows that domain functions do use the index.

- Show quoted text -

Arvin & Rick,

I'm sorry, but:

" Did you test using perfmon.exe to watch the network traffic?"

is something I do not understand and do not see how this helps me with
my problem?

Am I experimenting with quotes and hash marks even though my form is
bound.....?

Should I make my form unbound and try??? Help!!! Please?

 

[Opal]

Opal,

Post your code (copy and paste) so we can see exactly what you are working
with.

Dale









- Show quoted text -

This was Arvin's recommendation as I interpreted it...

Private Sub Form_Load()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From NorthWeldDailyTable Where
txtDate = Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = " & Date
Else
DoCmd.OpenForm "NorthWeldDailyfrm", , , , acFormAdd
End If

Exit_Here:
DoCmd.SetWarnings True
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[Opal]

This was Arvin's recommendation as I interpreted it...

Private Sub Form_Load()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From NorthWeldDailyTable Where
txtDate = Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = " & Date
Else
DoCmd.OpenForm "NorthWeldDailyfrm", , , , acFormAdd
End If

Exit_Here:
DoCmd.SetWarnings True
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub- Hide quoted text -

- Show quoted text -

Am I looking at too many variables for VBA to handle?

The scenario is that a user will open the form for the first time on
Sept 21st, for example,
and the form will be blank. Any subsequent times the user opens the
form on Sept
21st it will default to the same record in the table for that day so
that more information
can be added to that record. On September 22nd, the form will be
blank once again.

Someone else I conferred with outside this Usenet group suggested I
create another
"holding" table for the record before the data is written to the
"history" table. I wondered
if this was necessary - i.e. make the database larger than it needs to
be. Your assistance
is appreciated, if you can help me with this issue.

 

[Arvin Meyer [MVP]]

Interesting. I haven't tested this in 10 years, but that was the
conventional wisdom then, and it was easily provable. I'm not using a server
here right now, but when I get a chance, I want to check the network traffic
and see if it's about the same.

 

[Arvin Meyer [MVP]]

Arvin & Rick,

I'm sorry, but:

" Did you test using perfmon.exe to watch the network traffic?"

is something I do not understand and do not see how this helps me with
my problem?

Am I experimenting with quotes and hash marks even though my form is
bound.....?

Should I make my form unbound and try??? Help!!! Please?

We were talking about whether using a recordset or DCount would be faster.
According to Rick it is easier to use DCount. It makes little difference to
you since all you need to do is check to see whether there is a record with
the current date, and you can do it either way.

Access makes excellent use of bound forms and it has almost nothing to do
with your situation which is using an Access (Jet) data source. Unbound
forms may be slightly faster when many people are querying large recordsets
on a server based system like SQL-Server.

 

[Arvin Meyer [MVP]]

Does that work for you? I.e. does it open the form the way you want it to?
If not, let's find the error. Click in the margin at the line:

Set db = CurrentDb

and a red (burgundy) dot should appear. That's called a breakpoint. Now step
slowly through the code using the F8 key. Each line will appear as yellow.
As soon as the yellow goes to the first line in the Error handler, you can
read the error number, and go back and start over, holding your cursor over
each value. The IDE will tell you the value of each variable, if the
variable is wrong, we know our problem, other-wise, the Error code and
description from the message box should be sufficient.

 

[Marshall Barton]

This was Arvin's recommendation as I interpreted it...

Private Sub Form_Load()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From NorthWeldDailyTable Where
txtDate = Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = " & Date
Else
DoCmd.OpenForm "NorthWeldDailyfrm", , , , acFormAdd
End If

[ . . . ]


There are missing # signs around the date value.

Combining that with Rick's speed test, I think you could
could be simplified to:

Private Sub Form_Load()
On Error GoTo Error_Handler

If DCount("*", "NorthWeldDailyTable", "txtDate=Date()") _

0 Then

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate=Date()"
Else
DoCmd.OpenForm "NorthWeldDailyfrm", , , , acFormAdd
End If

Exit_Here:
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

Actually, I don't see why you need to check to see if a
record already exists. The first OpenForm will open to a
new record if one doesn't exist. If your only concern is
preventing users from creating two records on the same day,
then you can use code in the daily form's Load event to set
the AllowAdditions property.

 

[David W. Fenton]

An article in Access Advisor magazine (years ago) discussed
testing and comparison of all of the lookup methods and DLookup()
(when used with an index) came in second place losing out only to
Seek. It found only a negligible difference between DLookup() and
a Recordset with the Recordset actually being the slower of the
two.

Until Access 2000, I belive, all the domain aggregate functions were
slow on linked tables. That was why Trevor Best wrote his wonderful
t-functions to replace the built-in domain aggregate functions
(tCount(), tLookup(), etc.). After Access fixed the problem with
linked tables being slow (it may have been a service pack of A97
that fixed it, now that I think back on it), there was no longer any
need for it, but I'd become addicted to my own alterations of
Trevor's functions (where I used an optional argument supplying a
database variable so I could run them on something other than
CurrentDB()).

Seek, of course, is useless for lookups on more than one table.

And I just don't see it as useful in much of any circumstances.

Recordset.FindFirst can be optimized if your recordset is ordered by
the field being searched on. I did a whole bunch of testing on this
a couple of years ago and found that FindFirst is faster with an
index, but seems to do something of a table scan, as the time to
find a value is linearly proportional to the distance from the top
of the table. Because of that, you can optimize the FindFirst by
checking where you are in the recordset and doing a FindPrevious or
FindNext based on whether the next-sought value is greater than or
less than the current value.

To illustrate, say you have a table with FK DonorID, and you open a
recordset on that table, with a primary sort by DonorID and a
secondary sort by some other value. If you FindFirst
"[DonorID]=500000" you'd start from the top. For you next FindFirst,
if you're looking for 100000, then do a FindPrevious, and if you're
looking for 800000 do a FindNext. This improved performance by a
noticeable amount.

My recent work has reminded me of that testing, because I'm upsizing
the app that I did that testing for, and a stored procedure makes it
possible for me to abandon the persistent recordset that I was using
for lookups, so all that code that I wrote to implement the process
described above has been ripped out in the last two weeks!

But it was a valuable lesson that perhaps others might benefit from
hearing about.

 

[David W. Fenton]

Private Sub Form_Load()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From NorthWeldDailyTable
Where txtDate = Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = " &
Date
Else
DoCmd.OpenForm "NorthWeldDailyfrm", , , , acFormAdd
End If

Exit_Here:
DoCmd.SetWarnings True
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

You see, what I'd do is put all the code for this in the OnOpen of
the form NorthWeldDailyFrm.

And you'd open that form like this:

DoCmd.OpenForm "NorthWeldDailyfrm", , , _
"[txtDate=#" & Date() & "#, , acHidden

In the OnOpen, you'd do this:

If (Me.RecordsetClone.RecordCount=0) Then
Me.AllowAdditions = True
Me.DataEntry = True
Me.Requery
End If
Me.Visible = True

That's it -- no additional recordset need be opened. You'll use the
form's own recordcount to figure out whether or not you need to go
into DataEntry mode or not.

 

[Arvin Meyer [MVP]]

Thanks David,

That's exactly true. When you've been doing Access as long as I have (next
month will be 15 years) you tend to build methods of work that work well for
you and you don't change them until something better comes along. Rick has
shown that using DLookup is now probably the equal of building a recordset.
Since there is less code to write, I'll probably use it more often.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

An article in Access Advisor magazine (years ago) discussed
testing and comparison of all of the lookup methods and DLookup()
(when used with an index) came in second place losing out only to
Seek. It found only a negligible difference between DLookup() and
a Recordset with the Recordset actually being the slower of the
two.

Until Access 2000, I belive, all the domain aggregate functions were
slow on linked tables. That was why Trevor Best wrote his wonderful
t-functions to replace the built-in domain aggregate functions
(tCount(), tLookup(), etc.). After Access fixed the problem with
linked tables being slow (it may have been a service pack of A97
that fixed it, now that I think back on it), there was no longer any
need for it, but I'd become addicted to my own alterations of
Trevor's functions (where I used an optional argument supplying a
database variable so I could run them on something other than
CurrentDB()).

Seek, of course, is useless for lookups on more than one table.

And I just don't see it as useful in much of any circumstances.

Recordset.FindFirst can be optimized if your recordset is ordered by
the field being searched on. I did a whole bunch of testing on this
a couple of years ago and found that FindFirst is faster with an
index, but seems to do something of a table scan, as the time to
find a value is linearly proportional to the distance from the top
of the table. Because of that, you can optimize the FindFirst by
checking where you are in the recordset and doing a FindPrevious or
FindNext based on whether the next-sought value is greater than or
less than the current value.

To illustrate, say you have a table with FK DonorID, and you open a
recordset on that table, with a primary sort by DonorID and a
secondary sort by some other value. If you FindFirst
"[DonorID]=500000" you'd start from the top. For you next FindFirst,
if you're looking for 100000, then do a FindPrevious, and if you're
looking for 800000 do a FindNext. This improved performance by a
noticeable amount.

My recent work has reminded me of that testing, because I'm upsizing
the app that I did that testing for, and a stored procedure makes it
possible for me to abandon the persistent recordset that I was using
for lookups, so all that code that I wrote to implement the process
described above has been ripped out in the last two weeks!

But it was a valuable lesson that perhaps others might benefit from
hearing about.

 

[Arvin Meyer [MVP]]

I see that my code forgot the hash marks around the date:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & Date() & "#

I also see that, you need more than today's date, so you'll need an unbound
text box on the search form, I'll call this one txtDate. David added,
correctly, that you can just open the form without checking. So now, try:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & txtDate & "#

which is sufficient to do what you want. Now what happens here is that if
there is an existing date, it will open to it, if there is no existing
record, it will open a blank record. Others here have shown you code on how
to prevent additions.

Under no conditions do you need a "holding" table. You never ever store a
record twice. Even audit history records are there because someone changed
the original. I'd stay well clear of the person who gave you that idea,
since the no nothing about relational databases. That kind of advice
typically comes from programmers who think code solves everything and will
get you in more database trouble than any real database programmer wants to
deal with. When I see that kind of activity, I generally rewrite everything
from scratch.

 

[Douglas J. Steele]

While I realize you're in the US, just in case you ever have users should
have a different Short Date format, you're safer using

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate=" & _
Format(Date(), "\#yyyy\-mm\-dd\#")

 

[Opal]

While I realize you're in the US, just in case you ever have users should
have a different Short Date format, you're safer using

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate=" & _
Format(Date(), "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I see that my code forgot the hash marks around the date:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & Date() & "#[/QUOTE]

I also see that, you need more than today's date, so you'll need an
unbound text box on the search form, I'll call this one txtDate. David
added, correctly, that you can just open the form without checking. So
now, try:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & txtDate & "#[/QUOTE]

which is sufficient to do what you want. Now what happens here is that if
there is an existing date, it will open to it, if there is no existing
record, it will open a blank record. Others here have shown you code on
how to prevent additions.

Under no conditions do you need a "holding" table. You never ever store a
record twice. Even audit history records are there because someone changed
the original. I'd stay well clear of the person who gave you that idea,
since the no nothing about relational databases. That kind of advice
typically comes from programmers who think code solves everything and will
get you in more database trouble than any real database programmer wants
to deal with. When I see that kind of activity, I generally rewrite
everything from scratch.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com- Hide quoted text -

- Show quoted text -

Sorry,.,,, I'm still missing something here.....

I am copying and pasting your suggestions and I am getting compile
errors
and syntax errors... VBA is still pretty new to me...where would the
other
] bracket go?

 

[Opal]

While I realize you're in the US, just in case you ever have users should
have a different Short Date format, you're safer using

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate=" & _
Format(Date(), "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I see that my code forgot the hash marks around the date:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & Date() & "#[/QUOTE]

I also see that, you need more than today's date, so you'll need an
unbound text box on the search form, I'll call this one txtDate. David
added, correctly, that you can just open the form without checking. So
now, try:

DoCmd.OpenForm "NorthWeldDailyfrm", , , [txtDate=#" & txtDate & "#[/QUOTE]

which is sufficient to do what you want. Now what happens here is that if
there is an existing date, it will open to it, if there is no existing
record, it will open a blank record. Others here have shown you code on
how to prevent additions.

Under no conditions do you need a "holding" table. You never ever store a
record twice. Even audit history records are there because someone changed
the original. I'd stay well clear of the person who gave you that idea,
since the no nothing about relational databases. That kind of advice
typically comes from programmers who think code solves everything and will
get you in more database trouble than any real database programmer wants
to deal with. When I see that kind of activity, I generally rewrite
everything from scratch.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com- Hide quoted text -

- Show quoted text -

I am using the following and it works just great:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = Date()"
End Sub

One simple line and it accomplishes the desired results. Thank you
for all your help.

 

[David W. Fenton]

While I realize you're in the US, just in case you ever have users
should have a different Short Date format, you're safer using

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate=" & _
Format(Date(), "\#yyyy\-mm\-dd\#")

Or using DateSerial().

 

[David W. Fenton]

I am using the following and it works just great:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = Date()"
End Sub

I would change that to:

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate =#" & Date() & "#"

 

[Rick Brandt]

I would change that to:

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate =#" & Date() & "#"

Why? When inside the quotes the value will be evaluated by the Expression
Service as an actual DateTime, not as a string that looks like a date.
Delimiters should not be necessary at all. Just as they are not necessary when
you use Date() or Now() in the criteria of a query.

 

[Douglas J. Steele]

Or using DateSerial().

I don't see how that makes the problem any easier.

As was pointed out else-thread, "txtDate = Date()" will work. If the date is
specified outside of the quotes, then you must delimit using # characters,
and you must put it in a format Access will recognize correctly.

 

[David W. Fenton]

I don't see how that makes the problem any easier.

It means you don't have to worry about formats, nor about implicit
coercion of a string type (which is what Format() returns) into a
date type.

As was pointed out else-thread, "txtDate = Date()" will work. If
the date is specified outside of the quotes, then you must delimit
using # characters, and you must put it in a format Access will
recognize correctly.

I always resolve a reference to a function once, and try to pass
string values only as filters, i.e., no functions. One of the
reasons for that is that when opening a recordset, not all functions
can be used, so it's important to resolve them before passing the
SQL to the OpenRecordset. I just see this as good practice overall.