Add record Problems

Thanks Steve,

I tend to agree with you. What I finally wound up doing was this (and
you will probably laugh):

I recreated the form from scratch. Using just basic access functionality
and not worying about trying to make the form do double duty. I'll
create a separate admin form and menu button to handle that. It is much
cleaner and works like a charm.

Now if I could just figure out this damn running total isse I'm having.

I need to create a daily running total of hours from the subform on the
main form. So far so good. I use the Dsum and it updates the hours
correctly. But only after I close the form. I'm trying to put a requery
for the running totals fields someplace in the subform so it performs a
requery after the hours are entered. I can get that part too but the
problem is it then prevents me from adding records to the subform (which
is in Datasheet view).
The fields on the subform are in this order
ResourceID, WeekID (both Hidden)
Date, ProjectID, PhaseID, Hours and Comments.

I've tried the following events with no luck Hours_afterUpdate event,
Comments_exit, Form Current (logically where it should be but it
prevents me adding new lines/records to the subform), Date_beforeupdate,
Date_enter, Date_Exit and a few others.

Any ideas what event I should put the requery into?

Thanks again for your help.

In article ,
says...
Craig,

Thanks for the further clarification.

Here's the rub... If you open the form from the Main Menu form, you
either want it at an existing record, or you want it at a new record.
If you have ResourceID and WeekID as a composite primary key, then a
specified record involves defining both of these. At the moment your
code opens the form at a record specified by the ResourceID, of which
the database actually contains a number, so it opens at whatever happens
to be the first one in the recordset. And then it tries to delete the
WeekID entry for this record. Nope, this is not what you want.

So, here's how I would see it at the moment. If you want the form
opened at a new record for data entry, you would use this code from the
main menu...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry", , , , acFormAdd
End Sub

If you want the ResourceID for the new record to be defaulted to the
value of the ResourceID specified on the Main Menu, like this...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry", , , , acFormAdd
Forms!frmTimeEntry!cboResourceID = Me.ResourceID
End Sub

If you want the form to open at a new record, but still be able to
scroll back to see previous records, like this...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry"
DoCmd.GoToRecord , , acNewRec
Forms!frmTimeEntry!cboResourceID = Me.ResourceID
End Sub

If you want the above, but only to scroll back to existing records for
the specified ResourceID...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry", , , "[ResourceID]=" & Me.ResourceID
DoCmd.GoToRecord , , acNewRec
Forms!frmTimeEntry!cboResourceID = Me.ResourceID
End Sub

If you want to open the form to show existing records for the specified
ResourceID...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry", , , "[ResourceID]=" & Me.ResourceID
End Sub

If you want to open the form to show a single existing specified record...

--
You have no right to protection against being offended.

Craig,

I would imagine the After Update event of the subform would be the way
to go. And you need Recalc not Requery. Since the control you want to
update the display of is on the main form, I guess it would be...
Me.Parent.Recalc

By the way, as an aside, 'date' is a Reserved Word (i.e. has a special
meaning) in Acces, and as such should not be used as the name of a field
or control.

--
Steve Schapel, Microsoft Access MVP


Craig M. Bobchin wrote:
Thanks Steve,

I tend to agree with you. What I finally wound up doing was this (and
you will probably laugh):

I recreated the form from scratch. Using just basic access functionality
and not worying about trying to make the form do double duty. I'll
create a separate admin form and menu button to handle that. It is much
cleaner and works like a charm.

Now if I could just figure out this damn running total isse I'm having.

I need to create a daily running total of hours from the subform on the
main form. So far so good. I use the Dsum and it updates the hours
correctly. But only after I close the form. I'm trying to put a requery
for the running totals fields someplace in the subform so it performs a
requery after the hours are entered. I can get that part too but the
problem is it then prevents me from adding records to the subform (which
is in Datasheet view).
The fields on the subform are in this order
ResourceID, WeekID (both Hidden)
Date, ProjectID, PhaseID, Hours and Comments.

I've tried the following events with no luck Hours_afterUpdate event,
Comments_exit, Form Current (logically where it should be but it
prevents me adding new lines/records to the subform), Date_beforeupdate,
Date_enter, Date_Exit and a few others.

Any ideas what event I should put the requery into?

Steve,

Thanks, The recalc worked, but with one strange side effect.... When
leaving the last column on the subform, instead of adding a new
line/record it jumps back to the 1st line/record in the subform data
sheet. I had to add a DoCmd.GoToRecord , , acNewRec after the recalc to
get it to work, but all is fine now. Thanks again for all your help.

In article ,
says...
Craig,

I would imagine the After Update event of the subform would be the way
to go. And you need Recalc not Requery. Since the control you want to
update the display of is on the main form, I guess it would be...
Me.Parent.Recalc

By the way, as an aside, 'date' is a Reserved Word (i.e. has a special
meaning) in Acces, and as such should not be used as the name of a field
or control.



--
You have no right to protection against being offended.

Craig,

You haven't still got a Requery code in there, have you, on the
subform's After Update event, or the After Update or Exit or some such
event of the last control on the subform?

--
Steve Schapel, Microsoft Access MVP


Craig M. Bobchin wrote:
Steve,

Thanks, The recalc worked, but with one strange side effect.... When
leaving the last column on the subform, instead of adding a new
line/record it jumps back to the 1st line/record in the subform data
sheet. I had to add a DoCmd.GoToRecord , , acNewRec after the recalc to
get it to work, but all is fine now. Thanks again for all your help.

Nope, the only code on the subform is the following: And yes I do know I
have to change the Date field's name

Option Compare Database
Option Explicit

Private Sub Date_BeforeUpdate(Cancel As Integer)
'if the user enters a date that is more than 7 days less that the
weekending date
'display an error and set the focus back
'to the date field.

Dim intDays As Integer
Dim dtDateEntered As Date
Dim dtWeekEnding As Date

dtDateEntered = Me.Date
dtWeekEnding = Forms!tbltimetrack!txtWeekEnding

If dtDateEntered = dtWeekEnding Then
intDays = DateDiff("d", dtDateEntered, dtWeekEnding)
If intDays 6 Then
MsgBox "The date is not valid. Please re-enter a valid date for
the week-ending: " & dtWeekEnding
Cancel = True
End If
Else
MsgBox "Date is not Valid."
Cancel = True
End If

End Sub


Private Sub Form_AfterUpdate()
Me.Parent.Recalc
DoCmd.GoToRecord , , acNewRec
End Sub



In article ,
says...
Craig,

You haven't still got a Requery code in there, have you, on the
subform's After Update event, or the After Update or Exit or some such
event of the last control on the subform?



--
You have no right to protection against being offended.

Craig,

Ok, well I wouldn't have expected that behaviour with the subfrom, but
anyway, you've got it sorted so no point in sweating over that one! :-)

Best wishes for the rest of the project.

--
Steve Schapel, Microsoft Access MVP


Craig M. Bobchin wrote:
Nope, the only code on the subform is the following: And yes I do know I
have to change the Date field's name