Default field content based on different table

[TomJamieson]

Sorry if this seems like an obvious question, I have a pretty straightforward idea of what I need to do, I just can't figure out how to do it! I think it may need the dlookup function but I never can get that to work properly!

I have a form based on a table. One field, Max Passes and another, Min Passes, each have a text box that the user can edit the number in. What I want is the text box to default to a certain number when the form is opened. But the number is not static, it is dependent on the Consignment Size for that record, which is held in a different table.

So it's a long formula like, "If Consignment Size >20, Max Passes is 4, if Consignment Size >50, Max Passes is 8", and so forth, for quite a lot of numbers.

I assume I can't base the report on a query looking at both tables, because the fields need to be edited, that's why I was thinking maybe I need to use dlookup.

I hope I have explained myself clearly. Can anybody shed any light on this issue? I'd be very grateful.

 

[edtab]

DLookup is the right function to use to get values from any table into a textbox. It has 3 parts:

- the field you are interested in
- the domain (table or query)
- the condition (without the where clause)

Which part are you having problem with?

 

[phatnq2002]

I think, to do this, you can make some codes in the Consignment_AfterUpdate event procedure:

If [Consignment Size] > 20 Then
MaxPasses = 4
ElseIf ...
...
End If

And, the Form_Current:

If [Consignment Size] > 20 Then
MaxPasses.DefaultValue = 4
ElseIf ...
...
End If