ARW17

Board Regular
Joined
Oct 31, 2016
Messages
109
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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 = ""
 
Last edited:
Upvote 0
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.
 
Upvote 0
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...
 
Last edited:
Upvote 0
Are fields on either of these forms bound?

Me.WorkReviewSub
Forms![Home]![Tally_form].Form
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top