Slowing code down?

[ARW17]

I'm writing vba to .addnew then .update a table.

Once this is done, I want to clear the fields on the form that was used to update the table.

SOMETIMES the longtext field is cleared before it's written to the table.

If I take out the code that clears the fields, this is fixed.

Why is it that the code isn't completing before the clearing code runs?

I've looked into DoEvents, but can't get this to work.

Help please!

 

[Special-K99]

How on earth do we know what it does without seeing it?

Post it!

 

[ARW17]

Here's the code. The note_evaluation is what is causing the issue. The other fields are writing just fine.

Dim MsgSave, StyleSave, TitleSave, ResponseSave
MsgSave = "Ready to Save?"
StyleSave = vbYesNo + vbQuestion + vbDefaultButton2
TitleSave = "Evaluation Completed"
ResponseSave = MsgBox(MsgSave, StyleSave, TitleSave)
If ResponseSave = vbNo Then
Exit Sub
ElseIf ResponseSave = vbYes Then


Dim WrkRev As Recordset
Set WrkRev = CurrentDb.OpenRecordset("WorkRevHist")


WrkRev.AddNew


WrkRev!Name_Actor = [WorkReviewSub].[Form]![Name_Actor]
WrkRev!ID_DocID = WorkReviewSub.Form!ID_DocID
WrkRev!ID_Register = WorkReviewSub.Form!ID_Register
WrkRev!Date_Tx = WorkReviewSub.Form!Date_Tx
WrkRev!WorkType = cboWorkType
WrkRev!BILLTON = WorkReviewSub.Form!BILLTON
WrkRev!Duration = WorkReviewSub.Form!Duration
WrkRev!SupeGroup = WorkReviewSub.Form!SupeGroup
WrkRev!PrimaryRole = WorkReviewSub.Form!PrimaryRole
WrkRev!SecondaryRole = WorkReviewSub.Form!SecondaryRole
WrkRev!TertiaryRole = WorkReviewSub.Form!TertiaryRole
WrkRev!Emp = cboName
WrkRev!Share = cboShare
WrkRev!Date_Rev = date_reviewed
WrkRev!Evaluator = name_reviewer
WrkRev!Evaluation = note_evaluation
WrkRev!Understand = CboUnderstand
WrkRev!und_rub = CboUndRub
WrkRev!Decide = CboDecide
WrkRev!dec_rub = CboDecRub
WrkRev!communicate = CboCommunicate
WrkRev!com_rub = CboComRub
WrkRev!Overall_Grade = CboOverall
WrkRev!Counter = 1
WrkRev!WLID = WorkReviewSub.Form!WLID
WrkRev!CredVal = WorkReviewSub.Form!CredVal
WrkRev!Include_report = Include_report




WrkRev.Update


End If


Me.WorkReviewSub.Requery
Forms![Home]![Tally_form].Form.Requery

CboUnderstand = ""
CboUndRub = ""
CboDecide = ""
CboDecRub = ""
CboCommunicate = ""
CboComRub = ""
cboShare = ""
CboOverall = ""
Include_report = ""
note_evaluation = ""

 

[xenou]

If I take out the code that clears the fields, this is fixed.

very confusing. Can you describe this in detail?

Since this involves long text my main concern would be the datatype which is always very tricky.

 

[ARW17]

I have a form that someone fills out evaluating work. When they're done, they should save this to a table. The evaluation note is where they write in their observations. I didn't want that to get cut off.

I'm mapping all of my fields to the place on the table that I want to write to. This works successfully for all of the fields and OCCASIONALLY for the evaluation (longtext) field. However, even if I'm just writing a short note (a word or 2) to test, more than half the time it isn't written to the table.

If I take out the code to clear the fields on the form, which I want to do so they're ready to review the next item, the evaluation always writes to the table without an issue.

It's like it's clearing the field before it's been written even though the code order shouldn't allow that to happen...

 

[xenou]

Are fields on either of these forms bound?

Me.WorkReviewSub
Forms![Home]![Tally_form].Form

 

[ARW17]

Yes, both

 

[xenou]

mixing bound fields with code updates could be the problem. You shouldn't need to do both. THe requery command is also suspicious. WHy is that there? And why do you need ado commands if the fields are bound?

 

[ARW17]

I'm not sure. I've never been formally taught anything about building databases, just trial and error...

If I don't include the requeries, then the subforms don't update. I need them to. One tallies how many items have been reviewed and the other removes the piece of work that's already been reviewed so you don't do it a second time.

 

[xenou]

Hard to say really. Generally I avoid memo or long text fields. If you don't really need to store essays and short novels in your field, I'd reduce it to 255 characters. If you really need that kind of long text then I guess you will have to continue to use trial and error on this. How much data are you trying to save? If the fields are bound, have you tried not using recordset code at all?