Add record Problems

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

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

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, Microsoft Access MVP


Craig M. Bobchin wrote:
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,

Thanks for the reply. I am unable to add a new record to the main form.
The Add Button is in the main form's Form footer.
I do have AllowAdditions set to yes and I can use the built in buttons
to navigate and add records. So I know it is something in my forms code.

Craig

In article ,
says...
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?



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

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.

--
Steve Schapel, Microsoft Access MVP


Craig M. Bobchin wrote:
Steve,

Thanks for the reply. I am unable to add a new record to the main form.
The Add Button is in the main form's Form footer.
I do have AllowAdditions set to yes and I can use the built in buttons
to navigate and add records. So I know it is something in my forms code.

Steve see my replies inline to your responses/questions.

In article ,
says...
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


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

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.

In article ,
says...
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.



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

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, Microsoft Access MVP

Craig M. Bobchin wrote:
Steve see my replies inline to your responses/questions.

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.

--
Steve Schapel, Microsoft Access MVP


Craig M. Bobchin wrote:
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!!!!!!

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.

In article ,
says...
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.
--
You have no right to protection against being offended.

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?

--
Steve Schapel, Microsoft Access MVP

Craig M. Bobchin wrote:
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.