How to carry forward a values on a record that is Not New 2

[TSimonick]

Hi,

Can anyone suggest the code to carry forward values on existing records? DefaultValue works only on new records, my problem is opening existing records to add more information, then carrying the values of some controls to the next record while that form is open.

Thanks!

-Tom

 

[SBendBuckeye]

One way would be to use the control's tag property. If you are only interested if something changes, use the AfterUpdate property. Can you give a little bit more background regarding what you are trying to do? Carry a value forward if the next one is empty, only on change, etc?

Put code like the following as appropriate to save it:

Me.Tag = Me.Tag + "SavedValue=" & ControlName.Value & "~"

The tilde ~ is just a delimiter and you save a complete expression plus the current value to allow for any future stuff you may want to do.

Put code like the following to retrieve it:

Dim intPos As Integer
Dim intTemp As Integer
Dim strTemp As String

intPos = Instr(UCase$(Me.Tag), "SAVEDVALUE&quot 'Be safe
If intPos > 0 Then
strTemp = Mid$(Me.Tag, intPos)
intTemp = Instr(strTemp, "~&quot

'Rebuild Me.Tag without this piece
Me.Tag = Left$(Me.Tag, intPos - 1) & _
Mid$(strTemp, intTemp + 1)

strTemp = Left$(strTemp, intTemp - 1)
intTemp = Instr(strTemp, "=&quot
strTemp = Mid$(strTemp, intTemp + 1)

ControlName.Value = strTemp
End If






Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

Thanks for your interest. Where I want to use this is let's say I receive a shipment of parts and I want to update the records for each part with the date it arrived. So the record already exists and I enter the for the first part and advance to the next part. I just want to carry that date from record 1 to record 2.

I'll try your code, but please let me know if your code covers what I wanted to accomplish.

Thanks,

Tom

 

[Rolliee]

Use your own new record button. The wizard can take you through this. Then before triggering the new rec with a DoCmd or equivalent. Dim an array of values.

with code like this before the new rec trigger:


ar(0) = me.field1
ar(1) = me.field2


and after the fire

me.field1 = ar(1) and etc

piece of cake!!
rollie@bwsys.net

 

[TSimonick]

Rolliee,

Thanks for your suggestion, but could you expand a little on your idea? I don't understand what the array is doing or how values get into it.

-Tom

 

[SBendBuckeye]

Tom,

If all you are doing is pulling one field, you could do that with a form level variable. In the Declaractions section do something like the following:

OPTION blah, blah, etc

Private datSaveDate As Date

In the after update event of your date control, put something like the following:

datSaveDate = YourDateControlName.Value

Then, in the form's On Current processing, do something like the following:

YourDateControlName = datSaveDate

Depending on what you are doing, you may want to check for a null or 0 value before you make the assignment.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

SBendBuckeye,

Thanks for your time. Now I'm confused. I have several forms where the ability to carry forward values for date, name, etc. would be great. On a group of records, based on a query, I would set the values on record #1 and have them carry forward to the last record. So initially all of the controls are empty.

Could you larify your first post, because I couldn't get the Tags to work, or I'm putting the code in the wrong events. Or, should I be using your second suggestion?

This would really help me out in a number of situations, so I would really appreciate a little more of your time.

Thanks,

Tom

 

[SBendBuckeye]

Tom,

Sorry to confuse the issue. There are several different ways to go here, based on what your end goal is. When I answered your post the first time, I thought there were multiple fields you wanted to update going forward.

The second response was just another way to do the same thing. Do you really need to cycle through every record with the form? Or, once you have the first record, could you update all of the rest of them at once.

If you could post some examples of query output, what you are doing with the fields from the form, etc. maybe we can come up with a good workable system for you.

Also, where are you currently doing the tag stuff?

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

I had tried the following before posting my original question on the AfterUpdate event of each control that I wanted to carry forward:

Me![cboAnalyst].Tag = Me![cboAnalyst]

Then on the form's BeforeInsert:

Me![cboAnalyst] = Me![cboAnalyst].Tag

I tried this on the combo, a couple of text fields and a date field with no luck. This seems like it should have a lot of use in many databases since not all information is added to a record at the time a new record is created.

After you posted your first solution, I put the code:

Me.Tag = Me.Tag + "SavedValue=" & ControlName.Value & "~"

into the AfterUpdate and I wasn't sure where to put the other procedure. It didn't work in the BeforeUpdate event.

In my database, I have a form based on a temp table. The object is to look at 30 or 40 records and verify some test results that I imported from an instrument. The analyst starts with record# 1 and enters his name and the date, looks at the test results, checks a box if he approves them, then moves onto record#2, etc. When he has looked at all of the test results he clicks a button and the approved results are sent into the permanent table using an Update query. So, I don't want the analyst to have to constantly have to re-enter his name and the date, just verify and move onto the next record. On the other hand, I do want to make the analyst look at each record, not just apply a batch approval, name and date.

Hope this clarifys what I'm trying to accomplish. I certainly appreciate your time on this problem.

Tom

 

[TSimonick]

I seemed to have found an answer. I used my original code, but put the:

Me![cboAnalyst] = Me![cboAnalyst].Tag

on the form's Current event. Seems to be working OK.
Now, I wanted the "default" review date to be Now()when the form is opened. But when I put:

Me![txtAnalReviewDate] = FormatDateTime(Date, vbShortDate)

in the form's Activate or the controls GotFocus or anywhere else so far, the date is not placed in record#1. In fact, it can interfer with my Tag solution. I don't have a clue why that would be the case, but I don't see any Help info on events interferring with each other. Any ideas?

Tom

 

[SBendBuckeye]

Tom,

Nice clarification. Now I understand a lot better what you are trying to accomplish. With regards to the first post I made and in general, what tripped you up was that you were not putting the update code in the right spot.

You want to put your update code for the new record in the On Current code for the form. Your own solution was setting up correctly, but the before insert code was for the record you had just processed and then there was no after update after the first record. Changing a control's value programatically does not fire the control's after update code (sigh - I wish it was at least an optional thing).

You want to use the on current event because that will get you the next record in the recordset. So for either of my solutions and your original one, put the update code for the next record in the form's On Current event code.

Based on what you are doing, the module level variable is probably cleaner and involves less code. Sorry I didn't think of that first. The tag code will work just fine also.

A quick recap:

A. Setup your data in the AfterUpdate code for the controls holding the name and date
(eg Me![cboAnalyst].Tag = Me![cboAnalyst]

B. Modify the remaining records by modifying them in the form's On Current event code
(eg Me![cboAnalyst] = Me![cboAnalyst].Tag


If you are a little bit unsure of the order of events for a form, create a simple form and then put a message box in all or several of the form events (eg msgbox "Form_Load", msgbox "Form_Current", etc) and run the form and see what happens.

Several things you can do in VBA code will trigger an On Current event for the form, so don't be surprized in debugging to see Form_Current called more than once sometimes. A couple things that will trigger that are changing the OrderBy (but not changing OrderByOn), changing the form's record source, changing the primary key of the record source, etc. As you go along, I'm sure you will encounter others.

Good Luck! You ask good questions!
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[SBendBuckeye]

Tom,

Try putting the following in the Form_Load event code (or Form_Open if that doesn't work).

Me![txtAnalReviewDate].DefaultVale = FormatDateTime
(Date, vbShortDate)

Changes made at run time do not persist after the application is shut down so you aren't hurting anything using this approach.

Good Luck!

PS. Don't you ever sleep?


Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

Well I tried Form Open, Load, and Activate with:

Me![txtAnalReviewDate].DefaultValue = FormatDateTime
(Date, vbShortDate)

But remember these are existing records, so I wouldn't expect the DefaultValue to work. I also tried:

Me![txtAnalReviewDate] = FormatDateTime
(Date, vbShortDate)

and it didn't work. However, it didn't occur to me to mention that I'm using a single form, with the cboAnalyst and txtAnalReviewDate in the header. When I change the form to continuous, then my date code works as a Load event.

I have no idea what the difference is between these variations of forms (in regard to events). I have noticed that I can't set the tab order from the header to the details section, but I haven't seen an explanation.

I want to thank you again for your help and your excellent "teachers patience". I definitely will try your "MsgBox" exercise, because reading about it is one thing, but trying it out and seeing the results are much better.

I'm off to sleep since I'm a little short on that, as you so perceptively noticed. It nevers fails to amaze me how such a small problem can take so much time. However, there is a lot of satisfaction in these small victories for us amateurs.


Best Regards,

Tom Simonick
Gilbert, AZ

 

[Abinadi8]

I do this in the following manner:
I put a hidden field on my form Me.intRecsH (property: Visible: No) where the bound Name is Me.intRecs (property: Visible: Yes) then I do two things in the vba code as follows:

Private Sub Form_Current()
On Error GoTo Trap
If IsNull(Me.intRecs) Or IsEmpty(Me.intRecs) Or Not Me.intRecs = 0 Then
If IsNull(Me.intRecsH) Or IsEmpty(Me.intRecsH) Then
Else
Me.intRecs = Me.intRecsH
End If
Else
Me.intRecsH = Me.intRecs
End If
.
.
.
Private Sub intRecs_AfterUpdate()
On Error GoTo Trap
If Not IsNull(Me.intRecs) And Not IsEmpty(Me.intRecs) Then
Me.intRecsH = Me.intRecs
End If
.
.
.
(Or Not Me.intRecs = 0) is optional.

 

[TSimonick]

That is another nice way to handle this problem. Now I know what to do with invisible fields.

Tom

 

[newfrontiers]

Thank you for these postings. It really helped me out.