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

On Sep 24, 11:56 am, "Jeff Boyce" <nonse...@xxxxxxxxxxxx> wrote:
Opal

From your description of what folks are doing (different folks adding
different "types" of comments throughout the day), I'm going to guess that
your table structure looks a lot like a ... spread***!

It sounds like you are embedding data in the column/fieldnames (i.e.,
"quality comment", "productivity comment", ...). While this approach is
pretty much what you'd need to do if you were using Excel, MS Access is a
relational database. What happens when you need to add a new "comment
type"? You have to change the table design, your queries, your forms, your
reports, your code, ... this is a maintenance nightmare!

In a well-normalized relational database design, since there can be multiple
"types" of comment, you'd create a new table, related one-to-many. Your
original table would not hold comments, but would provide the necessary
identification/information. Your new table would hold one comment per row.
A rough idea of the structure of this new table would be something like:

trelComment
CommentID
PersonID (a foreign key, pointing back to the Primary Key in
tblPerson for who is adding this comment)
CommentDateTime (a date/time field ... a "timestamp" for when the
comment was made)
CommentTypeID (a foreign key, pointing back to the Primary Key in a
tlkpCommentType for which type of comment)
Comment

With a design like this (including a lookup table for CommentType), if you
add a new comment type, you simply add a new row in that lookup table, and
your trelComments table can now accommodate a new type.

Or have I misinterpreted what information you need to keep track of?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

news:1190431132.090483.51350@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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?- Hide quoted text -

- Show quoted text -

It was just a simple database to track comments on a daily basis for 4
different shops and bring them all together on one report. It would
be too difficult to maintain this data in an excel spread***, we
tried. Although what you wrote is just a little above my knowledge of
databases, it is beyond the scope of what we needed. We have trialed
the database as I have created it and it suits our current needs. As
my knowledge of databases increases with time (I am constantly
learning about them) I will be able to adapt this knowledge to new
projects. Our company has been very focused on excel use and I have
been trying to move them more and more over to databases as the scope
of what is needed begins to go beyond what excel spreadsheets can
handle.

.