Add record Problems

[Craig M. Bobchin]

Hi All,

I've got a form/subform combo that I'm trying to create a button that
will let me add a recordto the table. Unfortunately it does not work. I
get an error message saying "you can't go to the specified record."

Below is the code used in the Add Button click event:


Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Me.cboResourceID.Enabled = True
Me.cboWeekID.Enabled = True
Me.cboResourceID.Locked = False
Me.cboResourceID.Value = Forms!frmMainMenu.ResourceID
Me.cboResourceID.BackStyle = 1
Me.cboWeekID.BackStyle = 1
Me.cboWeekID.Locked = False
Me.cboSelect.Visible = False
Me.fsubTimeCards.Enabled = True
Me.lblInfo.Visible = False
Me.cboWeekID.SetFocus
DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

The table I'm trying to add the record to is comprised of two fields
(both part of the PK) It is a bound form, as is the subform. I've tried
putting the DoCmd line both at the front and at the end of the block of
code and had no luck.

The resourceID and WeekID fields are both comboboxes on the form, I
populate the resourceID with the user's ID from the main menu, and then
want the user to pick a Week. This will then ensure both parts of the
key are there before the record is created. After that they should be
able to go to the subform and enter data.

I've even tried putting the DoCmd.GoToRecord , , acNewRec as part of the
exit code of the weekID combo box since it only after the users selects
a week that the key is complete. All to no avail.


Can anyone help me sort this out? It is Urgent.

Thanks

 

[Steve Schapel]

Craig,

It is not clear whether you are talking about a new record on the main
form or the subform. Or which form the cmdAdd command button is on.

Is the AllowAdditions property of the form set to Yes? Are you able to
go to a new record "manually", i.e. if you use the built-in Navigation
Buttons?

 

[Steve Schapel]

Craig,

In that case, the most common cause of not being able to go to a new
record is that you are *already* on a new record. Sorry to be basic
here, but here's a test... at the point where you are about to click the
cmdAdd button, is the Navigation Button's 'new record' button, the one
on the far right with the >* icon, enabled?

Here's another test to try... comment out all of the code except the
'DoCmd.GoToRecord , , acNewRec' line, just to see. At the moment, I
can't see how any of the code in your procedure could affect the ability
to move to a new record. Although I don't really understand the purpose of
Me.cboResourceID.Value = Forms!frmMainMenu.ResourceID
.... and I would have expected a ! rather than a . as in
Me.cboResourceID = Forms!frmMainMenu!ResourceID
But in the end, if this data manipulation results in the existing record
not being updateable (and the current record would have to be updated
before you can move to a new record), then I would expect a different
error message.

So, as you see, I don't have any clear ideas on this, but maybe these
comments will give you something useful to explore.

 

[Craig M. Bobchin]

Steve see my replies inline to your responses/questions.

Craig,

In that case, the most common cause of not being able to go to a new
record is that you are *already* on a new record. Sorry to be basic
here, but here's a test... at the point where you are about to click the
cmdAdd button, is the Navigation Button's 'new record' button, the one
on the far right with the >* icon, enabled?

Yes it is. And I can add records with that.

Here's another test to try... comment out all of the code except the
'DoCmd.GoToRecord , , acNewRec' line, just to see. At the moment, I
can't see how any of the code in your procedure could affect the ability
to move to a new record. Although I don't really understand the purpose of
Me.cboResourceID.Value = Forms!frmMainMenu.ResourceID
... and I would have expected a ! rather than a . as in
Me.cboResourceID = Forms!frmMainMenu!ResourceID

I tried that and that gave me the same error. I did change the . to ! in
the mainmenu reference not that that should make any difference.

But in the end, if this data manipulation results in the existing record
not being updateable (and the current record would have to be updated
before you can move to a new record), then I would expect a different
error message.

So, as you see, I don't have any clear ideas on this, but maybe these
comments will give you something useful to explore.

Steve, I'm putting all the code in the form below. Maybe I've got
something going on that I can't see that is affecting the add button.

Option Compare Database
Option Explicit

Private Sub cboSelect_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ResourceID] = " & str(Nz(Me![cboSelect], 0)) & " AND
[WeekID] = " & Me.cboSelect.Column(1)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboResourceID.Enabled = True
Me.cboWeekID.Enabled = True
Me.fsubTimeCards.Enabled = True
End Sub




Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


Me.cboResourceID.Enabled = True
Me.cboWeekID.Enabled = True
Me.cboResourceID.Locked = False
Me.cboResourceID.Value = Forms!frmMainMenu!ResourceID
Me.cboResourceID.BackStyle = 1
Me.cboWeekID.BackStyle = 1
Me.cboWeekID.Locked = False
Me.cboSelect.Visible = False
Me.fsubTimeCards.Enabled = True
Me.lblInfo.Visible = False
Me.cboWeekID.SetFocus
DoCmd.GoToRecord , , acNewRec
Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

Private Sub Form_Current()
cboSelect.Requery
End Sub


Private Sub Form_Load()
Dim strEmpName As String
Dim rs As Object

strEmpName = Forms!frmMainMenu.Label0.Caption
lblEmployeeName.Caption = strEmpName


Set rs = Me.Recordset.Clone
rs.FindFirst "[ResourceID] = " & Forms!frmMainMenu.ResourceID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.cboResourceID.Value = Forms!frmMainMenu!ResourceID
'Me.cboResourceID.Locked = True
Me.cboResourceID.BackStyle = 0
'Me.cboWeekID.Locked = True
Me.cboWeekID.BackStyle = 0
Me.cboWeekID.Value = ""
Me.cboSelect.SetFocus
'txtSunHours.Requery

End Sub

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

 

[Craig M. Bobchin]

NOw something strange has occured, Now when I first enter the form and
click the access new record button ( >* ) I get an error saying the
record can be deleted or changed because the sub table contains related
records. ARGHHHHHHH!!!!!!

I hate trying to fix what some else had coded before me.

 

[Steve Schapel]

Craig,

I haven't got my head around everything going on here - in fact I
probably coluldn't without knowing more about the data and the
functionality of the controls mentioned in the code etc. But here's one
thing I notice... the code in the Form_Load() procedure doesn't really
make a lot of sense. This is how I read it:
This bit...
Set rs = Me.Recordset.Clone
rs.FindFirst "[ResourceID] = " & Forms!frmMainMenu.ResourceID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
.... has the result of making the current record on the form as the
record with the same ResourceID as the value of the ResourceID control
on the frmMainMenu form. Right? And on your form, the value of the
cboResourceID control will be the ResourceID field, right? So then the
code goes...
Me.cboResourceID.Value = Forms!frmMainMenu!ResourceID
.... so it is setting the value of a control to the value that it's
already got, which achieves nothing but it will Dirty the form.
Then, we've got...
Me.cboWeekID.Value = ""
Huh? What is cboWeekID? If I understand your naming convention, this
control will be bound to a field called WeekID. A lot of fields called
somethingID are a Number data type, so it doesn't make sense to set it
to a String. Even if WeekID is a Text data type, what it the meaning of
setting its value to ""?? If the purpose is to remove the value from
the WeekID field for this record, the code should be...
Me.cboWeekID = Null
But is this really what it is supposed to do? Very unusual.

In any case, is this form opened via an event on the the frmMainMenu
form? If so, the above functionality, with rst and all that, will be
more simply obtained by changing the frmMainMenu code to like this...
DoCmd.OpenForm "YourForm", , , "[ResourceID]=" & Me.ResourceID

So, what am I not understanding?

 

[Steve Schapel]

Craig,

Ok, I think it might be to do with this WeekID that I mentioned in my
other reply. Do you have a relationship based on this WeekID field, to
another table which is on the "many" side of a one-to-many relationship?
And with Referential Integrity enforced? Well then, you can't delete
the WeekID value in the cboWeekID control, because this would "orphan"
the records in the related table for that WeekID.

 

[Craig M. Bobchin]

Steve,

I'll answer both the posts here. First some background for you. The app
is a simple time keeping app that I inherited from another consultant
who really did not know Access and she got from another consultant to
use as a base to make the modifications to. the original intent of the
timesheet entry form we're dealing with (and I think this is where the
issue stems from), was to serve a dual purpose. Limit the users to
entering time only for themselves, and allow admins (who have a higher
security level) to enter time for everyone. Why they tried to do this
with one form and not two is beyond me.But this is why the ResourceID
was a combobox. and not just a text box.

The timesheet entry form/table has a two part key ResourceID and WeekID
these go to a subform/table in a 1-M relationship with ref. integrity
enforced. So yes you are correct in that.

I'll answer the rest inline below.

Craig,

I haven't got my head around everything going on here - in fact I
probably coluldn't without knowing more about the data and the
functionality of the controls mentioned in the code etc. But here's one
thing I notice... the code in the Form_Load() procedure doesn't really
make a lot of sense. This is how I read it:
This bit...
Set rs = Me.Recordset.Clone
rs.FindFirst "[ResourceID] = " & Forms!frmMainMenu.ResourceID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
... has the result of making the current record on the form as the
record with the same ResourceID as the value of the ResourceID control
on the frmMainMenu form. Right? And on your form, the value of the
cboResourceID control will be the ResourceID field, right? So then the
code goes...
Me.cboResourceID.Value = Forms!frmMainMenu!ResourceID
... so it is setting the value of a control to the value that it's
already got, which achieves nothing but it will Dirty the form.
Then, we've got...
Me.cboWeekID.Value = ""
Huh? What is cboWeekID? If I understand your naming convention, this
control will be bound to a field called WeekID. A lot of fields called
somethingID are a Number data type, so it doesn't make sense to set it
to a String. Even if WeekID is a Text data type, what it the meaning of
setting its value to ""?? If the purpose is to remove the value from
the WeekID field for this record, the code should be...
Me.cboWeekID = Null
But is this really what it is supposed to do? Very unusual.

I see where you are coming from on this, and yes both are numeric ID
fields read via combo box from another location and then stored in the
timetrack table. As for setting the weekID to "" I think the original
developer was trying to clear out the weekID so they could enter a new
week and create a new timesheet.

In any case, is this form opened via an event on the the frmMainMenu
form? If so, the above functionality, with rst and all that, will be
more simply obtained by changing the frmMainMenu code to like this...
DoCmd.OpenForm "YourForm", , , "[ResourceID]=" & Me.ResourceID

This form is opened from a menu form that gets loaded after login. The
call to open the timesheet form is:

Private Sub cmdTimeEntry_Click()
On Error GoTo Err_cmdTimeEntry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTimeEntry"
DoCmd.OpenForm stDocName

Exit_cmdTimeEntry_Click:
Exit Sub

Err_cmdTimeEntry_Click:
MsgBox Err.Description
Resume Exit_cmdTimeEntry_Click

End Sub

Would your solution work with a two part key where the 2nd part of the
key is not known at this point? After all we don't know if the user is
going to be editing an existing timesheet or creating a new one.

So, what am I not understanding?

Thanks for all your help and Time. It has been invaluable.

 

[Steve Schapel]

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...
Private Sub cmdTimeEntry_Click()
DoCmd.OpenForm "frmTimeEntry", , , "[ResourceID]=" & Me.ResourceID
& " And [WeekID]=" & Me.WeekID
End Sub

All of the above would involve removing all the code from the
FrmTimeEntry form which has anything to do with recordsets or setting
control values or whatnot, and this would be a very good thisng, because
this code is wrong. I have left out of consideration at the moment the
aspect of toggling the Enabled etc properties of controls according to
admin status etc, as this is really a separate question, but it
shouldn't be too hard to work that out.

Have we got any further ahead?

 

[Craig M. Bobchin]

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.

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

 

[Steve Schapel]

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.

 

[Craig M. Bobchin]

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.

 

[Steve Schapel]

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?

 

[Craig M. Bobchin]

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

 

[Steve Schapel]

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.