Go to new record on a new day

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I know that when you open or load a form you can put the following:

DoCmd.GoToRecord , , acNewRec

to create a new record so that the bound form does not retrieve the
1st record in the table . But here is my dilemma. I have a bound
form where data is updated by several users at different times in the
day. I do not want the form to open on a blank record but rather
bring up the day's record. However, on the next day, I want a blank
record to start off the day. I have
a text box (txtDate) that has a default value of "=Date(). How can I
write a statement that tells
the form 'On Load' to only bring up a new record if the day has
changed? Or if the date has not changed to bring up that day's
record? I only want one record in the table per date, not multiple
records with only fragments of information. Can anyone help? Thank
you!
 
In the form's open event, use DLookup or build a recordset to check if a
record for that day exists. If it does, open the form to that record, if
not, open to a new record. Here's some similar code that I've tried to adapt
to your need, but I haven't tested the changes:

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 tblWhatever Where DateField =
Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "frmWhatever", , , "DateField = " & Date()
Else
DoCmd.OpenForm "frmWhatever", , , , 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
 
I know that when you open or load a form you can put the following:

DoCmd.GoToRecord , , acNewRec

to create a new record so that the bound form does not retrieve the
1st record in the table . But here is my dilemma. I have a bound
form where data is updated by several users at different times in the
day. I do not want the form to open on a blank record but rather
bring up the day's record. However, on the next day, I want a blank
record to start off the day. I have
a text box (txtDate) that has a default value of "=Date(). How can I
write a statement that tells
the form 'On Load' to only bring up a new record if the day has
changed? Or if the date has not changed to bring up that day's
record? I only want one record in the table per date, not multiple
records with only fragments of information. Can anyone help? Thank
you!
Click to expand...

Would an If...Then statement in the 'On Load' event work? But can you
write 'if... does not equal ...then...'?
 
Would an If...Then statement in the 'On Load' event work? But can you
write 'if... does not equal ...then...'?
Click to expand...

The statement would look like:

If Not Whatever Then

or you could use an Else statement to handle opposition.
 
Arvin,

Any particular reason that you are opening a recordset rather than just
using DCOUNT?

Dale
 
Arvin,

Any particular reason that you are opening a recordset rather than just
using DCOUNT?

Dale
--
Email address is not valid.
Please reply to newsgroup only.











- Show quoted text -
Click to expand...

How would DCOUNT help in this situation...?
 
The statement would look like:

If Not Whatever Then

or you could use an Else statement to handle opposition.
Click to expand...

So, something like:

If txtDate = Date() then
DoCmd.GoToRecord , , acLast
Else
DoCmd.GoToRecord , , acNewRec

?????
 
So, something like:

If txtDate = Date() then
DoCmd.GoToRecord , , acLast
Else
DoCmd.GoToRecord , , acNewRec

?????
Click to expand...

This what I have tried and it is not producing the desired results:

Private Sub Form_Load()
If txtDate = "Date()" Then
DoCmd.GoToRecord , , acPrevious
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub
 
You just use DCOUNT to determine the number of records in the table that
meet the date criteria.
Using Arvin's code as a start point, I will remark out all of the lines you
don't need and add the dcount line.

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 tblWhatever Where DateField =
Date();")

'If Not rst.EOF Then rst.MoveLast

'If rst.RecordCount > 0 Then
If DCOUNT("ID", "yourTable", "[DateField] = " & Date) > 0 then
DoCmd.OpenForm "frmWhatever", , , "DateField = " & Date()
Else
DoCmd.OpenForm "frmWhatever", , , , 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
 
This what I have tried and it is not producing the desired results:

Private Sub Form_Load()
If txtDate = "Date()" Then
DoCmd.GoToRecord , , acPrevious
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub
Click to expand...

Remove the quotes.

This will compare txtDate to the literal text string Date() - not the date
which would be returned if you called the date function!


John W. Vinson [MVP]
 
You just use DCOUNT to determine the number of records in the table that
meet the date criteria.
Using Arvin's code as a start point, I will remark out all of the lines you
don't need and add the dcount line.

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 tblWhatever Where DateField =
Date();")

'If Not rst.EOF Then rst.MoveLast

'If rst.RecordCount > 0 Then
If DCOUNT("ID", "yourTable", "[DateField] = " & Date) > 0 then
DoCmd.OpenForm "frmWhatever", , , "DateField = " & Date()
Else
DoCmd.OpenForm "frmWhatever", , , , 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




How would DCOUNT help in this situation...?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I did not see Arvin's code posted before.....

I will give this a try, thank you.
 
You just use DCOUNT to determine the number of records in the table that
meet the date criteria.
Using Arvin's code as a start point, I will remark out all of the lines you
don't need and add the dcount line.

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 tblWhatever Where DateField =
Date();")

'If Not rst.EOF Then rst.MoveLast

'If rst.RecordCount > 0 Then
If DCOUNT("ID", "yourTable", "[DateField] = " & Date) > 0 then
DoCmd.OpenForm "frmWhatever", , , "DateField = " & Date()
Else
DoCmd.OpenForm "frmWhatever", , , , 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" <[email protected]> wrote in message
Click to expand...

Dale,

When I load the form I get a pop-up message that states:
2001: You canceled the previous operation.

As well, it is bringing up the oldest record, not a new record or the
last record.
 
The statement would look like:

If Not Whatever Then

or you could use an Else statement to handle opposition.
Click to expand...

Arvin,

I tried your code without Dale's additions and I still do not get the
desired results.

The form still opens to the oldest record. I do not get the error
message I got with the DCount, but
I do not go the desired record either..... any suggestions?
 
Dale Fye said:
Arvin,

Any particular reason that you are opening a recordset rather than just
using DCOUNT?
Click to expand...

Yes, the Domain functions are much slower than pulling recordsets because
they don't take advantage of indexes. Data from a properly indexed table
called from a recordset is orders of magnitude faster. An index from the
table is brought to the local processor, and Access then retrieves only
those records that fit the index. With a domain function like DLookup or
DCount every record is brought to the local machine in its entirety.

I'm used to working with tables containing 100,000 records or more and the
difference is under a second to perhaps 3 or 4 seconds. On small tables with
powerful PCs, there probably isn't much difference. I just got in the habit
of doing it the fastest way, and try to do that for everything, just to be
consistent.
 
Arvin said:
Dale Fye said:
Arvin,

Any particular reason that you are opening a recordset rather than
just using DCOUNT?
Click to expand...

Yes, the Domain functions are much slower than pulling recordsets
because they don't take advantage of indexes. [snip]
Click to expand...

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

I tried your code without Dale's additions and I still do not get the
desired results.

The form still opens to the oldest record. I do not get the error
message I got with the DCount, but
I do not go the desired record either..... any suggestions?
Click to expand...

I see that you are using txtDate for the current date. Assumming is it an
unbound textbox in the form's header, you'd change this part of the code:

If rst.RecordCount > 0 Then
DoCmd.OpenForm "frmWhatever", , , "DateField = #" & Me.txtDate & "#"
Else
DoCmd.OpenForm "frmWhatever", , , , acFormAdd
End If

You may have to experiment slightly with the quotes and hash marks.
 
I see that you are using txtDate for the current date. Assumming is it an
unbound textbox in the form's header, you'd change this part of the code:

If rst.RecordCount > 0 Then
DoCmd.OpenForm "frmWhatever", , , "DateField = #" & Me.txtDate & "#"
Else
DoCmd.OpenForm "frmWhatever", , , , acFormAdd
End If

You may have to experiment slightly with the quotes and hash marks.
Click to expand...

No, it is not unbound. It is in the form's header, but as I stated in
my first post:

I have a bound form where data is updated by several users at
different times in the
day. I do not want the form to open on a blank record but rather
bring up the day's
record. However, on the next day, I want a blank record to start off
the day. I have
a text box (txtDate) that has a default value of "=Date().

Its Control Source is the txtDate field on the table.
 
Did you test using perfmon.exe to watch the network traffic?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Rick Brandt said:
Arvin said:
Dale Fye said:
Arvin,

Any particular reason that you are opening a recordset rather than
just using DCOUNT?
Click to expand...

Yes, the Domain functions are much slower than pulling recordsets
because they don't take advantage of indexes. [snip]
Click to expand...

Cite? Testing on a large sample table before and after adding an index
clearly shows that domain functions do use the index.
Click to expand...
 
Arvin said:
Did you test using perfmon.exe to watch the network traffic?
Click to expand...

No, but hasn't it been stated over and over in these groups that the same exact
i/o that would happen with a local disk is what happens with a networked back
end? Is that not still considered correct?

I ran a 100 iteration loop calling DCount() on a table with over 600,000 records
using criteria on a 20 character text field. Running that 5 times in a row with
no index took 57, 56, 56, 57, 55 seconds. After adding an index on the criteria
field the same 5 tests took 2, 1, 1, 1, 1 seconds each. Clearly the DCount was
utilizing the index.

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.
 
Opal,

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

Dale
 
Back
Top