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

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" <tmwelton@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????



.

Relevant Pages

  • RE: New record on a new day (re-post)
    ... times the user opens the form on Sept 21st it will default to the ... Private Sub Form_Load ... On Error GoTo Error_Handler ... Set rst = Nothing ...
    (microsoft.public.access.formscoding)
  • RE: "Delete Record" command button woes
    ... Private Sub Command96_Click ... On Error GoTo Err_Command96_Click ... Error 13 in procedure DelCurrentRec of Module ... Set rst = Nothing ...
    (microsoft.public.access.formscoding)
  • RE: listbox link to seperate form help
    ... the item or can use the text box portion to type in the item, ... Private Sub cboActivity_AfterUpdate ... On Error GoTo cboActivity_AfterUpdate_Error ... Set rst = Me.RecordsetClone ...
    (microsoft.public.access.forms)
  • RE: Beginner needs Combo Box help
    ... Private Sub cboActivity_AfterUpdate ... On Error GoTo cboActivity_AfterUpdate_Error ... Set rst = Me.RecordsetClone ... Part of this issue IS SQL server: ...
    (microsoft.public.access.forms)
  • RE: If, then
    ... Private Sub Combo38_NotInList(NewData As String, Response As Integer) ... Set rst = Me.RecordsetClone ... Then check the Control Source property of each control that should show a ...
    (microsoft.public.access.forms)
Loading