DataBase - Subform updating in error

Asked By KC_Cheer_Coach

13-Nov-09 03:25 PM

I have this written this in ACCESS 2007:

Private Sub USAGE_PERIOD_AfterUpdate()
If DCount("*", "tblImportTracker", "[FileName] = '" & Me![FILENAME] & "' And
[RunNo] = " & Me![RunNo] & " And [UsagePeriod] = " & Me![UsagePeriod] & "") >
0 Then
Cancel = True
Me![txtDuplicatesWarning] = "DUPLICATE"
Else
Me![txtDuplicatesWarning] = "UNIQUE"
[Forms]![frmMain].[Form]![ frmImportTracker subform].Requery
End If
End Sub

FileName is a text field
RunNo is a Long Integer
UsagePeriod is a Long Integer
txtDuplicatesWarning is an unbound field on the parent form that is only
there to let the user know if the entered information is a duplicate or not.

My problem is when duplicate information is entered, tblImportTracker is
updated and the subform reflects this after the db is closed and reopened.
So, now my table has duplicate info in it and it should not. I also get a
run-time error 2448 stating I cannot assign a value to txtDuplicatesWarning
and I do not know why.

Can anyone help?

14-Nov-09 07:16 PM

Hi KC,
The code you have used, was originally written to be used an the
BeforeUpdate event.
Note the line
Cancel = True

The after update event does not allow you to cancel it, so the cancel = true
will not do anything here.

Move your code to the before update event.

The best place to check for possible duplicates is in the before update
event of the form.
When the form gets to its before update event, you can run some code to
check that all required fields have been filled in before you run the check
for duplicates.

If you check for duplicates in the before update of a control, some of the
other controls needed to check for a duplicate may be still empty.

I recommend that you move the code to the before update event for the form.


--
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

19-Nov-09 10:21 AM

I did what you said and it functions, but not without a lot of headache and
frustration. This is what I mean...

When I enter values that are duplicates, the duplicateswarning field does
not populate until I click all over the form. The "click" is never in the
same spot for it to populate with "Duplicate". So, the user will never know
if the information is a duplicate right away.

When I enter values that are not duplicates, the duplicateswarning field
acts the same way as above. Then, even when it does finally tell me the
information is "Unique", the subform will not update even with a requery. The
only time it will update is if I close Access and reopen it.

Do you have any ideas on what I can do to solve this? I have tried several
different things since and still the same result.

I appreciate your quick response to my question.

KC

19-Nov-09 08:03 PM

There is more than problem in this.
Let's do the most important problem first.
1. Subform not updating until you close and reopen access.

The usual reason for this behavior is the relationships between the tables
that the form and subform are based on.
Would you post the names of the tables, their primary keys and foreign keys
and the relationships for the tables
involved in these 2 forms.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

20-Nov-09 03:07 PM

That could be the problem! I am looking at it right now and there are no
relationships build between these two. Let me mess with it over the weekend
and I will let you know if I still have an issue or not on Monday.

Thanks!

23-Nov-09 06:06 PM

- Ol??, lingua portugues brasil , copie o seu documento para o seu propont.

not??cia da mensagem:BE54DEEF-E7E8-45FE-AE5F-789DC50C375B@microsoft.com...