Software

Office challenge: How to use input values as default values in an Access form

This week, test your Access skills with this fairly common efficiency request from data input personnel.

Let's suppose one of your users transfers data from paper forms, usually submitted in batches, into an Access database. That means a lot of the data - salesperson's name, territory id, and so on - repeats across the entire batch of forms. These aren't true default values, not in the traditional sense. However, the user would like to input them once, and have them remain the default value as he inputs a record for each form in the batch, as if they were true default values. Could you do this for your user?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
Snak
Snak

It's easy to bind a table to a drop down list. Run a macro (or, if you really must, some soon-to-be-needing-to-update VBA) to detect an new entry and add it to the table, or, if your skills aren't quite up to that, offer an option to add a new entry and open a sub-form.

jswed
jswed

I would create an unbound form, set the Tag property of each text box to the field name of the table to which the information is being added. Place a "Save" button on the form that when clicked would scrape the values - via VBA code - from the text boxes and save them to the corresponding fields in the target table. The user-entered information will remain in the form's text boxes until the user manually changes/clears them or until the form is closed.

sirtimtoo
sirtimtoo

This is close to LKJCPA recommentation. Do an afterupdate event on the field in question setting the default value in the properties for the current new default value entered in form. It would stay the same value if nothing changes. Quick and dirty code: Private Sub Field1_AfterUpdate() Dim txtDefault As String txtDefault = Me.Field1.Value Me.Field1.DefaultValue = Chr(34) & txtDefault & Chr(34) End Sub

dsomerv
dsomerv

It depends on how the data is organized. For the example, one would expect the data to go in a least two separate tables: one for Salesone ID information, another for order information. In which case the answer is trivial. The challenge implies that the intent is to repeat the Salesone ID in every record in a single table, hardly good relational practice.

cmelhem
cmelhem

You can use the following Code in the form After update event: Field1.DefaultValue = """" + Field1.Value + """" field2.DefaultValue = """" + field2.Value + """" field3.DefaultValue = """" + field2.Value + """" This will replace the default values by the latest values entered on the form at each record insert.

LKJCPA
LKJCPA

Actually, I can think of three different ways to do what you ask, but one way in particular probably answers your question most directly. FIRST, set up public variables for the "defaults" in question and initialize them (to blank, zero, etc.). SECOND, create After_Update event triggers such that the variables are populated as data is entered for each related field (this will also allow you to change the "default" by overtyping with new information). THIRD, create event triggers such that variable values are entered in the related fields when a new record is begun. Certain precautions could be taken in this last set of triggers to minimize the chance of partially completed records.

ssharkins
ssharkins

It's immaterial to the question really and I certainly didn't mean to imply that the data sources were different. It could be either way really and the solution I have in mind would be the same.