How do I retain the value that is entered into a text box from record to record?

[stateemk]

I have a form that will have about 50 - 100 records entered at a time all needing the same batch number. All the other data will need to change, but I would like to be able to retain that batch number from record to record until I am ready to enter a new batch. Right now, I have no code behind this form, everything is set up through macros. From what I've seen, this can't be done in a macro. I'm assuming I'll need some code. Any help would be greatly appreciated. Thanks in advance!!

[ajalwaysus]

First off:
1. How are you entering this data. (i.e. are you importing it from a XLS file?)
1a. if you are importing, how? (Method being used)
2. Is the batch number supposed to be a default value perhaps?

You need to be a lot more specific, on what you are doing and how you are doing it.

-AJ

[stateemk]

That data is being entered two ways. There are about 15 forms or so in this database for multiple agencies. The personal information is already prepopulated by tables within access, but the rest of the info will be manually entered. The way this works is we have a charitable campaign and donors fill out their form with the amont they want to give. There is a bar code on the form that is scanned in and upon scanning, the program locates the person based on the bar code scan. From there the batch number is entered along with the amounts donated. A couple of the forms are not prepopulated so the personal info, batch number and amounts donated are all entered manually. The batch number is determined by a preprinted batch report card that already had a number on it and the agency only enters the total amounts for that batch. So, the batch is then entered back into these forms to tie the individual amounts to a batch number total amount. Hopefully this clears it up a little bit. If not, feel free to ask me to explain more.

[NeoPa]

The easiest way to handle consistency of data across a batch while entering data into a form is to set the .DefaultValue property of the control (TextBox) to the value required using code.

[missinglinq]

As NeoPa said, in your form, you can use the AfterUpdate event of the control holding your data to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

Private Sub BatchNumber_AfterUpdate()
   Me.BatchNumber.DefaultValue = """" & Me.BatchNumber.Value & """"
End Sub

Linq ;0)>

[NeoPa]

An added benefit of the .DefaultValue property of course, is that you never actually have to make changes to the record buffer. That means Access doesn't get confused into thinking there's something changed that needs saving and prompt the operator to save changes when they've made none.

[stateemk]

Okay, the default command works to an extent. It works on agencies that I have to enter all the information manually on which is only three of the 15 or so. On the others, the form is already prepopulated with the information of the donor. The donor fills out their donation card which has a bar code on it. That bar code is scanned and the database is then searched for that employee id hidden within the barcode so the information is populate randomly based on the employee id. When I tried using the default code, it wouldn't retain the batch number info. Any other suggestions at what I could try or might be doing wrong?

[missinglinq]

When populating a control,such as a textbox, using code, as you're doing, many events associated with that control do not fire. AfterUpdate is one of these. So in addition to having the code given in the control's AfterUpdate event, you need to explicitly Call the event.

In your code, after the target fields are populated, you need to call the AfterUpdate event for each of these fields. The syntax is

Call YourFieldName_AfterUpdate

Linq ;0)>

[NeoPa]

You may also consider the alternative of creating a separate procedure where your code would reside, which could be called by your AfterUpdate event procedure as well as wherever else you may need the same functionality. This avoids event procedures needing to be called when an event is fired.

[stateemk]

Okay, I'm giving away my lack of VBA knowledge. I've got my two after update procedures. How would I do the call??

Private Sub batchno_AfterUpdate()
    Me.Batchno.DefaultValue = """" & Me.Batchno.Value & """"
 
End Sub
 
Private Sub inputdate_AfterUpdate()
    Me.inputdate.DefaultValue = """" & Me.inputdate.Value & """"
 
End Sub

[NeoPa]

@stateemk
What is "the call" exactly? Do you have a routine that needs to be called from multiple places?

[stateemk]

Let me back up to see if I can explain this better. In the database I have, there are multiple agencies. The personal data for most of the agencies is already poplulated with all the employees. The employees are given donation cards. Those cards have a barcode on them which has their ssn hidden. Once the employee fills out their card, they return it. The donation cards are entered in batches. Once an agency gets around 50 - 100 cards, they will enter a batch. The agency fills out a batch card that has a preprinted batch number on it. Right now, the agency will enter the information from the donation cards by scanning the ssn barcode. The program searches the table of employees until it finds that specific employee and then brings up a form that is identical to the donation card. The agency then manually enters the batch number, the date, the charities and the donation amount. To elminiate some time consuming repetition, since the batch number and date stay the same no matter who the employee, I would like to have it stay in the text boxes once it's entered for the first employee in that batch until it needs to be changed for a new batch. The after update worked for the agencies that did not have the information prepopulated, but since the majority of the agencies are already populated and are randomly selected no matter where they fall within the table, it doesn't retain the batch number and date with the after update. I'm sorry this is so long. I'm just at a loss of how to do this and I want you to fully understand what I'm trying to do.

[NeoPa]

It's good to see you understand that too much info can tend to swamp the reader, however the information is actually very helpful for me to understand what's going on.

For now though (late evening), it's a bit much for my brain to tie this and all the other information together to form a consistent whole. Let me see if I can do a better job when I'm fresher (tomorrow hopefully).

[NeoPa]

I'm afraid this is no clearer in the light of day than it was at night. Let's see if we can't prompt you with some specific questions. Please try to reply precisely and accurately. If you have a table holding Agency data, and the 'Agency' may also be considered to be entering the data, don't refer to them both simply as agencies. If the operator of the database is an employee for an agnency then share this information with us, then refer to them as the operator.

1. SSN Barcode. Is this a unique identifier for an agency employee?
2. Are the employees referred to in the project all employees of agencies?
3. Do you have a Batch table? Or are they simply numbers that group donations together and don't have any related data?
4. The employee info you want prepopulated, is this to be saved elsewhere (other than the Employee table), or are these unbound controls?
5. If the answer to #4 is Bound controls to be saved elsewhere then Why (This indicates non-normalised data and is strongly recommended against in almost all circumstances)?
6. What are the names of all the objects you're dealing with? Forms; Controls; Tables; Fields; etc.
7. Please identify, from the controls listed above (answer to #6) which store the SSN and Batch No values and which are effected by these two values (which need to be changed when the values of either of these two controls is changed).