Setting Default Values Based on Last Record 3

[Carenne]

I have a form that has four fields for which I want to set default values. Due to the nature of the data entry process, the values will be the same in these fields for dozens and sometimes hundreds of sequential records, and then it will need to change for the next dozens or hundreds, and so on.

What I've done is to set the fields to a "no tab" option, and what I would like to have happen is *only* when the value needs to be changed, the operator will click in that field, type in a value, and upon exit, that (current/last record) will automatically become the new default value until it is changed again.

I have tried to use the dlookup and/or dlast functions, but I'm somewhat confused about the syntax and have not had success. I've also resorted to putting some unbound text fields on the form footer to serve as default values, but that seems rather klugy, plus the behavior is erratic, as sometimes (but mysteriously, not always) what will happen is the default value will appear when the new record is first created, but after I tab completely out of the first active tab field, the "default value" fields (all 4 of them) are suddenly empty.

So anyway, I would be very grateful if someone could provide an example of how I can accomplish my goal, and even more grateful if you could use the names in my form/table in the example, which are as follows:

Form name: frmVoucherRegDataEntry
Based on: qryDataEntryForm
Table: tblVoucherRegister
(there is also another talbe in the query - tblVendorName - but it is not needed for this function)
Field name: vrVRBatch
Control Name: txtvrBatch
Current "dummy value" control name: BatchDateDefault

THANKS!!!!

Karen Schouest

 

[RickSpr]

Good news: You're making this way, way too hard!

All you really need to do is, in each control's AfterUpdate event, change its DefaultValue property to the new value. For txtvrBatch, that would be this statement:
Me!txtvrBatch.DefaultValue = Me!txtvrBatch.Value

Of course, this default value won't persist if they close the form and open it again. That's probably fine, it just means you have to enter a value for the first record. When you have hundreds of records to add, that's a small price to pay for getting a "sticky" default. Rick Sprague

 

[Carenne]

Rick,

>>Good news: You're making this way, way too hard!

It does not surprise me to hear that. <g>

>>Me!txtvrBatch.DefaultValue = Me!txtvrBatch.Value

I am REALLY hitting myself in the head over this solution! Coincidentally, the very first approach I tried was a similar version of this, and I see that I was &quot;THIS CLOSE&quot; to success but missed it by a hair. My original statement looked like:

Me!txtvrBatch.DefaultValue = Me!txtvrBatch

I couldn't find any examples or references in the online help or my books, so that was my &quot;best guess approach,&quot; and when that didn't work, I moved on to the more tedious solutions. Did you hear my OH DUHHHHH all the way through cyberspace when I read your reply? <g>

>>Of course, this default value won't persist if they close the form and open it again. That's probably fine, it just means you have to enter a value for the first record. When you have hundreds of records to add, that's a small price to pay for getting a &quot;sticky&quot; default.

I figured that any solution would probably only be good for the current active session and might require resetting each time the form is open, and I'm perfectly happy with that solution, as I think the users will be as well. As you said, it's a *very* small price to pay for the convenience.

I can't thank you enough for your speedy solution, and also for making it seem &quot;Fisher Price easy&quot;!

Have a great weekend!

Karen Schouest

 

[Carenne]

Well, now I'm having another problem which also occurred in some of my earlier experimentations and I'm clueless as to what's causing it. I changed the AfterUpdate Event as described in your previous post. I also eliminated the &quot;dummy (unbound text) fields&quot; and emptied the Default Value setting in the txtVRBatch control.

At this point, all 192 records in the database only have one date (11/30/97) and it's set to the &quot;Short Date&quot; format (both on the form control property and in the table property). So after I open the form and type &quot;11/30/97&quot; in the field and then tab through the whole record, the field value for the next (new) record is 12/30/99. I have even tried entering different dates to see what would happen and it's always the same. Any suggestions?

Thanks again.

Karen

 

[Carenne]

I'm having another minor problem which is hopefully easily fixable. Two of the fields are behaving properly with regard to the default values - the page number and gl# fields, which are both set to number formats in the table. The division (txtvrDiv) field will always be one of three possible values - 01, 02, 03 - so I set it to a text value in the table property because that's the only way I know to get the leading zeroes to appear (and I won't need to do any calculations on this field anyway).

So anyway, if I enter the values manually (with no default value behavior activated), the &quot;01&quot; comes out just fine. But after turning on the automatic default value event, the value changes to &quot;1&quot; after I type it in. Is there any way to get it to accept the &quot;01&quot; value?

Thanks again!

Karen

 

[RickSpr]

First the problem with the date. Unfortunately, people get confused with the Format property all the time. Remember this: it has nothing to do with the way the date is stored in either the database or in a VBA variable. The Format property only affects what's displayed to the user. In the table properties, it controls how the date is displayed in the table datasheet, and in the Form or Report, it controls how it's displayed there. But the value of a date/time field is always a complete date, regardless of the setting of the Format property. I'd have to say, based on my experience at Tek-Tips, that this is an almost universal misconception.

What's happening with your date is that it's being interpreted as three integer literals involved in 2 divisions, that is, 11 divided by 30 divided by 97. The result, 0.00378, is then converted to a date/time value. Date/time values are stored as floating point numbers where the integer part is the number of whole days since December 30, 1899, and the fraction is the time of day expressed as a fraction of a day (e.g. 6 hours is 1/4 day, resulting in a fractional part of .25). 0.00378, then, is 0 days since 12/30/1899, and .00378 corresponds to 12:05:27 AM. If you modify the Format in this field, I think you'll see it's 1899, not 1999, and that the time is as I said.

This is happening because the code I gave you for making the default value persist wasn't suitable for date/time values. I think we need to specify that we want the text box Value property as a date, and we do that with:
Me!txtvrBatch.DefaultValue = CDate(Me!txtvrBatch.Value)

Your division number problem can be similarly solved, I think, by specifying the data type we intend it to have, in this case a string. Try this:
Me.txtvrDiv.DefaultValue = Format$(&quot;00&quot;, Me!txtvrDiv)
The Format$() function returns a string, and the &quot;00&quot; specification will ensure the leading zero is there.

I agree with defining your division in the table as a Text field. Opinions differ, but I prefer to define numeric fields as Text if they are non-quantitative, that is, they don't represent quantities. Telephone numbers, social security numbers, sequence numbers, and the like I usually define as Text fields.

BTW, the only difference between my suggestion for DefaultValue and your original attempt was that you didn't explicitly specify the Value property. That's the default property and should have been assumed anyway, so I don't know why your original experiments didn't work. Maybe you had something else wrong at the same time, which you subsequently corrected? Rick Sprague

 

[Carenne]

Rick,

You were exactly right in your guess that I was suffering from the universal misconception about the date properties. That also explains why I had such difficulty finding any solutions in my online or text reference guides. Everything I searched for led me to either a gazillion ways to generate the current system date/time and/or how to manipulate the format properties in the control box, which, of course, would be of no help in this case. Thanks again for the detailed and very educational explanation!

I agree with your theory that my original approach might have failed for some other reason than the command syntax. Working into the wee hours of the morning, all things are possible! It's not unusual to go back after a night's rest and find silly blunders, such as the event statement being placed in the wrong event section, or a typo in the command, or other such nonsense that caused me grief in the previous session.

But now, back to the formatting problem. Of course, what you said for both the date and the leading zero problem made perfect sense, and I thought I was home free. But after I made those changes, and meticulously checked to make sure there were no other obstacles (i.e., old erroneous code, old default settings, etc.) plus no typos or anything, can you believe it STILL won't work? I'm getting the SAME behavior in the date (the 12/30/99 result) and the SAME behavior with the missing zero when I want the 01, 02, or 03 to appear as the default. Armed with more information this time, I scoured through the online help as well as every Access 97 and Visual Basic reference book I have on hand, and though I did find references to those commands, unfortunately, none of them included concrete examples of command syntax, which unfortunately I depend on to get me over the hump of understanding and being able to apply the information.

So now, O Wise One, can you see if there are any more rabbits in your hat? It doesn't make any sense that your last solution wouldn't work, because it is totally in sync with what the books and online help says. I'm stumped!

I'm now going to put this frustration aside and go onto the other suggestion you offered in the other post regarding temporary default values.

Thanks again for all your help. I SO appreciate it!

Karen

 

[RickSpr]

(Things that make you go &quot;Hmmm.&quot

Karen, I'm sorry for leading you on a wild goose chase. I should have tested out what I was telling you. Then I would have seen immediately where I was mistaken.

I guess you could say that this time I was making things way too hard. The problem is that when we assign a new value to the DefaultValue property, we need to give it the proper delimiters, as if it were a constant we were coding in VBA. Change the AfterUpdate events to set the defaults for txtvrBatch and txtvrDiv to:
Me!txtvrBatch.DefaultValue = &quot;#&quot; & Me!txtvrBatch & &quot;#&quot;
Me.txtvrDiv.DefaultValue = &quot;&quot;&quot;&quot; & Me!txtvrDiv & &quot;&quot;&quot;&quot;
Rest assured that this will work; I have tested it this time.

I'm not exactly sure why, but it appears that whatever string you assign to DefaultValue gets interpreted as an expression, and the expression result becomes the new default value.
Rick Sprague