Using DLookUp Function as a Default

[Glacil]

Using the DLookUp function, I've had success looking up the default value for certain fields on my form, but I'm run into another problem.

The form tracks transfers of assets, and queries of the tables determine where the asset was most recently, and in doing so determine the default value of the "former" fields for the new records accordingly. However, this only seems to work properly for the first new record, and subsequent records transfering the same asset reference the same people/places as the first new record. Also, directing the form to the first new record transfering an asset, even with all the relevent data entered causes a delay, showing that the queries are being run again, and the default values are being determined a second time, even though it is not the most recent time the asset has been transfered.

1 - Is there a way to make the queries that determine the default values run once, that data set for the new record (Save Record?, Refresh?) and not run again?
or
2 - Should I enter the DLookUp function somewhere else for more appropriate results for what I'm looking for?

My entry looks like this, in the [default value] section, and is similarly referenced for the different fields on the form:
=DLookUp("[NewENum]","qryWhereNow")

PSV or anyone else, do you have any suggestions?
Thanks in advance!

 

[willir]

Glacil

Instead of including the DLookup on the field property, use an Event Procedure...

Several ways, here is one

OnCurrent record event

If Nz(Me.NumericFld, 0) = 0 Then
   Me.NumericFld = DLookup(...          )
End If

If Len(Nz(Me.TextFld, "")) = 0 Then
   Me.TextFld = Dlookup(...             )
End If

This way, you wont over-write existing data.

Richard

 

[Glacil]

Willir

I have been having trouble getting your code to work. The code itself, and what you're getting at makes sense to me, but for whatever reason I keep getting errors.

I'm going to focus on the Numericfield example you gave, since all of the fields I'm referencing only require a number.

If Nz(Me.NumericFld, 0) = 0 Then
Me.NumericFld = DLookup(... )
End If

What am I suppose to enter in place of the "Nz"?
Am I to enter it as "Private Sub MultiBox_OnCurrent() routine? Sorry, I'm just not too familiar with manipulating MVB directly.

 

[willir]

You have to use the name of your control field ni place of Me.NumericFld -- this was just a generic example. (A little hard for me to see what your field names are from afar ;-) )

With the form open in design mode, and the properties window open ("View" -> "Properties"), click on the field in question. Then look on the "Other" tab of the property window, specifically, the "Name" field to see the name of the field in question.

Then type...[tt]
If Nz(Me.TheNameOfYourField, 0) = 0 Then
Me.TheNameOfYourField = DLookup(... )
End If[/tt]

I trust you know the syntax of the DLookup statement.

Richard

 

[Glacil]

Its working!

Many thanks Willir, I owe you one ;-)

I do have another question for you though. If you can picture this, I'm trying to get the information on subforms, to update one field is entered on the main form. If I put a button on the sub form and press it, then the information updates accordingly. But, if I put the button on the main form, it cannot reference the field name (Combo8 in my case), because its not on the main form.

Ideally, I would like be be able to fill out one field on the main form and as soon as I hit enter (Lose Focus maybe?), and have the data in the four subforms update accordingly.

So my question is, how do I make the subforms data update from a button or other action on the main form? The actual working code for the subform is below (I assigned it to a button for now):

Private Sub Command18_Click()

If Nz(Me.Combo8, 0) = 0 Then
Combo8 = DLookup("[NewENum]", "qryWhereNow")
End If

End Sub

Thanks in advance!