set default value in a field to the value in another table (1 Viewer)

Jarichardusa

New member
Local time
Today, 06:28
Joined
May 23, 2020
Messages
19
I have a table, its just tblInfo its not in any relationships or anything its just used to store information that might be needed in other areas. one of the fields in this table is "wage" In another table, tblWages, there is a field "Wage" which tracks the wages earned in a particular job. For the most part there is a set per job rate, which is where the db user can enter this amount in the tblinfo. It can change, however on some jobs, but to make it easier for the user, once the wage from tblInfo is entered, then it would be nice if the default value that populates on the job form is equal to whatever that value is set at. That value could change if the default rate changes, that's why I did it this way. Maybe there is an easier way, but it seemed to make sense to me. I'm guessing there may be code to put behind the textbox on the order entry form that could do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:28
Joined
Oct 29, 2018
Messages
21,358
Hi. You should be able to use DLookup() in the Default Value property of the Textbox on your Form.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:28
Joined
Sep 21, 2011
Messages
14,050
Use DLookup() ?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:28
Joined
Feb 28, 2001
Messages
27,001
OK, you have to be careful about default values on a form. They can lead to problems with forms that are bound to some table, because having a default can lead to creating incomplete records if you are not careful.

You mentioned code and in another post you claimed to be new to Access. Do some reading on ACCESS EVENTS before trying to do what I am about to describe. Basically, you can do this in many places in the code "behind" the form, and part of the design process involves picking the right place to do this. I am going to suggest using the "LostFocus" event of the control that you want to be set to a particular value.

The "LostFocus" event occurs when you are "in" a control (i.e. it has "focus") and you hit the TAB key. The control "in focus" changes according to the TAB order on the form, which you don't have to set because there is a default order anyway. But the point is that when a control has focus, that is where you enter data via keyboard action, and when you hit TAB that control loses focus.

OK, there is an Access EVENT that helps here. The code you want might look like this, but you will have to provide your own variable and field names because I don't know the details of your database.

Code:
Public Sub Wage_LostFocus()
    If NZ( [Wage], "") = "" Then
        [Wage] = DLookup( "[field-name]", "tblInfo", "criteria" )
    End If
End Sub

This says in English "If the Wage control loses focus and was blank or null, look up some field from the Info table based on whatever is needed to select a particular record from that table. I had to be vague because you didn't tell us much about tblInfo, like how many fields it has or how many records it has. I should point out that you can do more here than just this, but I am showing a simple example of event code.

If you are not familiar with creating event routines, it is something you can look up online. It really is easy. Try this in your favorite search engine annd you can pick & choose from several tutorials: "how to create event procedure in access"
 

Users who are viewing this thread

Top Bottom