Re: New record on a new day (re-post)

On Sep 21, 6:53 pm, "Jeff Boyce" <nonse...@xxxxxxxxxxxx> wrote:
Opal

?"... more information can be added to that record."?

You and I may use a different definition of record. In Access, a record is
a single row in a table.

I guess I'm not understanding a table design that keeps adding more data to
a single row.

If you'll provide a bit more specific description of the data/fields you
have in your table, the newsgroup volunteers may be able to offer more
specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Opal" <tmwel...@xxxxxxxxxx> wrote in message

news:1190414199.792554.290230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I seem to be having problems with this post, so I am going to try
again.

I need help in coding a form so that the following can happen:

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.

I was given the following code to try:

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 MyDailyTable Where
txtDate = Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
DoCmd.OpenForm "MyDailyfrm", , , "txtDate = " & Date
Else
DoCmd.OpenForm "MyDailyfrm", , , , 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

It does not produce the desired results. The form is bound to the
table,
by the way. Can anyone provide any assistance as to help get this
form to work?

It was also suggested that I try Dcount as in the following:

Private Sub Form_Load()
On Error GoTo Error_Handler
If DCOUNT("ID", "MyTable", "[txtDate] = " & Date) > 0 then
DoCmd.OpenForm "Myfrm", , , "txtDate = " & Date()
Else
DoCmd.OpenForm "Myfrm", , , , acFormAdd
End If

Exit_Here:

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

but this produced an error: 2001: You canceled the previous
operation.

Someone else outside this group suggested a holding table separate
from the history table....

Help! Which is the best route to go????- Hide quoted text -

- Show quoted text -

Yes, I understand that a record in Access is a single row of data in a
table.
I only want one record per day. However multiple users will be adding
data to that single record. There are several text boxes in which
they will place various comments. One user may comment on an
activity in the shop relating to quality, another to productivity. I
do
not want to have multiple forms as the shop Manager will want to view
all
inputs prior to adding his own and this is best achieved on one form.
Once
a complete day's record is in the table I will run a report for the
General
Manager to view. All I need to ensure is that on Sept 21, the form
opens
with only Sept 21st information, not the first record in the table.
And on
September 22nd the new form is blank at the start of the day and so on
and
so on....does this help clear it up?

.

Relevant Pages

  • Re: New record on a new day (re-post)
    ... a single row in a table. ... Private Sub Form_Load ... On Error GoTo Error_Handler ... Set rst = Nothing ...
    (microsoft.public.access.formscoding)
  • Re: New record on a new day (re-post)
    ... In a well-normalized relational database design, ... CommentTypeID (a foreign key, pointing back to the Primary Key in a ... a single row in a table. ... Set rst = Nothing ...
    (microsoft.public.access.formscoding)
Loading