New record on a new day (re-post)

[Opal]

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????

 

[Jeff Boyce]

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

 

[Guest]

One method that comes to mind is to is to count the records for the day, then
if the count = 0, then run an append query that will add a record for todays
date, then open the form. This could be on the OnClick event of where the
button is clicked or on the OnLoad event of the actual form your opening.

Here's a bit of the OnClick event of the button.

'Any records for this date?"
Dim DailyRecordCount As Integer
DailyRecordCount = DCount("*", "YourTableName", "YourDateField = #" &
Date & "#")

If DailyRecordCount = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "YourAppendQueryName"
DoCmd.SetWarnings True
Else
'todays record already exists
End If

'open the next form
DoCmd.OpenForm "YourFormName"

I'm sure there are other ways, but I have used this before and it works
nicely.

SteveD

 

[Opal]

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

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?

 

[Opal]

One method that comes to mind is to is to count the records for the day, then
if the count = 0, then run an append query that will add a record for todays
date, then open the form. This could be on the OnClick event of where the
button is clicked or on the OnLoad event of the actual form your opening.

Here's a bit of the OnClick event of the button.

'Any records for this date?"
Dim DailyRecordCount As Integer
DailyRecordCount = DCount("*", "YourTableName", "YourDateField = #" &
Date & "#")

If DailyRecordCount = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "YourAppendQueryName"
DoCmd.SetWarnings True
Else
'todays record already exists
End If

'open the next form
DoCmd.OpenForm "YourFormName"

I'm sure there are other ways, but I have used this before and it works
nicely.

SteveD

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 -

Thank you Steve, I will try this and see if I can get the desired
results.

 

[Rick Brandt]

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?

You are making this way more complicated than necessary. All you need to do is
have a default value for your date field of "Date()" and then open the form with
a WHERE clause...

DoCmd.OpenForm "Myfrm", , , "txtDate = " & Date()

If there is a match it will open to the existing record for today and if there
is not a match it will open to a new record (which will default to the current
date). No counting before hand is necessary at all.

 

[Opal]

You are making this way more complicated than necessary. All you need to do is
have a default value for your date field of "Date()" and then open the form with
a WHERE clause...

DoCmd.OpenForm "Myfrm", , , "txtDate = " & Date()

If there is a match it will open to the existing record for today and if there
is not a match it will open to a new record (which will default to the current
date). No counting before hand is necessary at all.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Rick!

That is what I was hoping it would be.....
I already have the default value for the txtDate field as "=Date()"

I have been using the on load event for the code....however,
when I use the:

DoCmd.OpenForm "Myfrm", , , "txtDate = " & Date()

statement and then close and re-open the form, it goes to a new
record.
I want it to open on the current day's record....If I am reading you
correcting I need to put a "WHERE" clause in the on open event?

 

[Rick Brandt]

Rick!

That is what I was hoping it would be.....
I already have the default value for the txtDate field as "=Date()"

I have been using the on load event for the code....however,
when I use the:

DoCmd.OpenForm "Myfrm", , , "txtDate = " & Date()

statement and then close and re-open the form, it goes to a new
record.

That would indicate that you have no existing record where txtDate is equal to
today's date. That is an unusual name for a field by the way. It would be a
common name used for a control on a form, but not for a field in a table. Are
you quite sure that is the field's name? Is it a DateTime field or a Text
field? That would make a big difference in how the WHERE clause is constructed.

I want it to open on the current day's record....If I am reading you
correcting I need to put a "WHERE" clause in the on open event?

No the WHERE clause is in the OpenForm method...
(this bit here at the end) "txtDate = " & Date()

I would move Date() inside the quotes though like...

"txtDate = Date()"

....that should remove formatting as an issue (providing txtDate is an actual
DateTime field).

 

[Opal]

That would indicate that you have no existing record where txtDate is equal to
today's date. That is an unusual name for a field by the way. It would be a
common name used for a control on a form, but not for a field in a table. Are
you quite sure that is the field's name? Is it a DateTime field or a Text
field? That would make a big difference in how the WHERE clause is constructed.


No the WHERE clause is in the OpenForm method...
(this bit here at the end) "txtDate = " & Date()

I would move Date() inside the quotes though like...

"txtDate = Date()"

...that should remove formatting as an issue (providing txtDate is an actual
DateTime field).

I am relatively new to using VBA in Access. I read that "Date" was a
reserved
word in Access so whenever I have a Date field I have gotten in the
habit
of calling it txtDate as opposed to just "Date" It is a Date/Time
field in the table.
I have named the fields in the form the same as the fields in the
table for ease
of cross reference.

I created a "test" record for Sept 22nd and then when I opened the
form, it did not
bring up that existing record, but rather a new one.

Oh wow, I just tested it with "txtDate = Date()" and it works --
thank you so so much,
this is great and its one simple line....WOW.

I have been told so many convuluted ways to accomplish this, I was
beginning to
hate this assigned project. But this is perfect. Thank you again!

 

[Jeff Boyce]

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 ... spreadsheet!

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

?"... 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

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?

 

[Opal]

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 ... spreadsheet!

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
?"... 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

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

 

[tina]

though you may not want to take Jeff's advice for your current db, it is
right on target. the concepts he explained apply to relational databases
regardless of size, number of users, etc - so, no, it is NOT "beyond the
scope of what you need".

if you are working to move your company to the use of relational databases
where appropriate, i applaud you. i also recommend you read up on the
principles of relational design, so you'll be in a strong position to lead
your company in this new direction. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

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 ... spreadsheet!

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

?"... 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.

Jeff Boyce
Microsoft Office/Access MVP

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

 

[Opal]

though you may not want to take Jeff's advice for your current db, it is
right on target. the concepts he explained apply to relational databases
regardless of size, number of users, etc - so, no, it is NOT "beyond the
scope of what you need".

if you are working to move your company to the use of relational databases
where appropriate, i applaud you. i also recommend you read up on the
principles of relational design, so you'll be in a strong position to lead
your company in this new direction. for more information, seehttp://home.att.net/~california.db/tips.html#aTip1.

hth

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 ... spreadsheet!
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
?"... 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

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 spreadsheet, 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.- Hide quoted text -

- Show quoted text -

Thank you for you input and advice. I will continue to increase my
knowledge
of relational databases.