Editing data in a subform

  • Thread starter Thread starter Craig M. Bobchin
  • Start date Start date
C

Craig M. Bobchin

Hi all,

I've run into a bit of a problem that I need your help to figure out.

I have a form/subform that I've built for entering timesheet data
(controlsource for the form is a table tblTimeTracker).
On the main form I have an unbound combobox that displays the data from
two key fields for this table (resourceID and weekending date). I'm
using this combo box as a search/selection to allow the users to select
a timesheet to edit.

Once a timesheet is picked, I feed each of these column values to
another set of combo boxes and pull up the record. So far so good.

The problem comes when I try to edit the subform data. I click on a
field in the subform and I get a message box saying that The changes you
requested were not successful because they would create duplicate
valules (you know the rest).

Below is the code from the combobox's AfterUpdate Event: (Beware of
Wrapping)

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.cboResourceID.Value = str(Nz(Me![cboSelect], 0))
Me.cboWeekID.Enabled = True
Me.cboWeekID.Value = Me.cboSelect.Column(1)
Me.tblTimeTrackData_Subform.Requery

End Sub

What am I doing wrong in the code and how can I fix it?

Thanks

Craig
 
There are at least 3 issues that could cause the problem you are
experiencing.

Does the main form have something in its RecordSource property, or is it
unbound? If it is bound to the same table as the subform, you may be
experiencing this problem:
http://allenbrowne.com/ser-40.html#OtherCauses
where Access actually corrupts the Seed of the AutoNumber, with the result
that you cannot add any more new records at all (not even directly to the
table.)

If the problem occurs immediately you begin adding a new record to the
subform (rather than after you have entered all the fields and would expect
Access to try to save the record) and the subform is bound to a query that
contains more than one table, try removing the Default Value from all fields
in the tables in the query. With this bug, Access wrongly applies the
Default Value to the fields in the lookup table, and naturally enough fails
with the message you describe.

If it is neither of those Access bugs, you will need to track down which
index is being violated. Make sure all fields in the subform have a text box
(even the foreign key fields that relate to the combo and date in the main
form) so you can see the values that are there and figure out which is the
duplicate. You will also need to know which field(s) in the subform's
table(s) have a Unique index on them. To discover that, open the table in
design view, and click the Indexes button on the toolbar.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Craig M. Bobchin said:
I've run into a bit of a problem that I need your help to figure out.

I have a form/subform that I've built for entering timesheet data
(controlsource for the form is a table tblTimeTracker).
On the main form I have an unbound combobox that displays the data from
two key fields for this table (resourceID and weekending date). I'm
using this combo box as a search/selection to allow the users to select
a timesheet to edit.

Once a timesheet is picked, I feed each of these column values to
another set of combo boxes and pull up the record. So far so good.

The problem comes when I try to edit the subform data. I click on a
field in the subform and I get a message box saying that The changes you
requested were not successful because they would create duplicate
valules (you know the rest).

Below is the code from the combobox's AfterUpdate Event: (Beware of
Wrapping)

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.cboResourceID.Value = str(Nz(Me![cboSelect], 0))
Me.cboWeekID.Enabled = True
Me.cboWeekID.Value = Me.cboSelect.Column(1)
Me.tblTimeTrackData_Subform.Requery

End Sub

What am I doing wrong in the code and how can I fix it?
Click to expand...
 
Thanks for the reply. Here is what I found:

The mainform is bound to a table that has two fields (both making up the
PK)
the Subform is bound to a query that references a single separate table
from the main form. There is no Autonumber in either table.

The Error occurs as soon as the user click on a field in the subform.
The only default value is in the subform for date (current date), it is
not on the table.

The only unique indexes on the subform's table is the PK (composite of 4
fields).

One more thing I noticed, when I click a field in the subform, the
status line says Calculating... for a long time before it comes back and
gives me the error message.

Any other ideas as to what I can check?
 
Okay, so the main form has 2-field key, and the subform a 4-field key.

If you open the main form in design view, right-click the edge of the
subform control and choose Properties, the Link Master Fields should contain
the names of the 2 key fields in the main form's table, and the Link Child
Fields should contain the names of the matching fields in the subform's
table. Assuming the main form has a record when you try to run this, Access
will automatically supply 2 of the 4 key fields to the subform, and the
other 2 still have to be supplied (e.g. entered manually.)

We are now looking at simplifying the subform until the problem ceases. Once
we have identified the culprit, you can put the other things back in.

Is there any code in the events of the subform control (on the main form),
the form that is the subform, or the control in the subform? Particularly:
- the Enter, Got Focus, or Click events of the subform control?
- the Current event of the subform?
- event procedures of the control you clicked on in the subform?
If so, try commenting out these event procedures.

Is there any conditional formatting in any controls in the subform? If so,
try removing that. (This can cause the endless "Calculating..." message.)

Try setting the Record Source of the subform directly to the table, rather
than the query. If this avoids the problem, we need to look further at the
query.

You can try removing the default value for the date field, but I doubt that
will do anything if the subform's source is a single-table query.

As always, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Explanation:
http://allenbrowne.com/bug-03.html
Then compact the database.

The basic strategy is to remove stuff until the problem goes away, at which
point you have identified what is causing the cause.
 
Back
Top