Changing Text Field values based on changes to another field

[des0906]

Greetings.

I have an inventory data entry form with five fields.

txtPhone, txtSerialNo, comboType, comboModel, txtBC

This form inserts records into the tblInventory table. However, when entering the phone extension into the txtPhone field, I need to populate the txtBC field with a 'default' value pulled from the BudgetCenter table (which has the extension number as the key field).

I tried a couple things that didn't work and I am still researching this. Does anyone have a suggestion on how I might make this work?

Many thanks.

 

[PHV]

Take a look at the DLookUp function you can call in the AfterUpdate event procedure of the txtPhone control.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[des0906]

Cool. Thanks for the idea PHV. I gave it a try and didn't get a syntax error...but I didn't get a value, either. I confirmed that the txtAssign field entry does indeed have a corresponding extension value in the BudgetCenter table, but no luck.

Here's what I went with:

Private Sub txtAssign_AfterUpdate()
Me.txtBC.Value = DLookup("[BC]", "BudgetCenter", "[Extension] = " & Me.txtAssign & "")
End Sub

I'm sure it's a big fat user error on my part, so any idea on where I went wrong?

Thanks again.

 

[PHV]

If Extension is not defined as numeric in the BudgetCenter table, you may try this:
Me.txtBC.Value = DLookup("[BC]", "BudgetCenter", "[Extension] = [highlight]'[/highlight]" & Me.txtAssign & "[highlight]'[/highlight]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[des0906]

Sorry, PHV. Good thought but it didn't take. Extension is numeric. Do I need to do some form of conversion here during the lookup process?

Thanks.

 

[popalu]

You are getting the value of BC from BudgetCenter based on Extension being exactly the same as Me.txtAssign. If one is text and the other numeric this wont work. You need to be looking at the same format and type in order for the correct parameter to be passed.

tt = DLookup("[eprooflink]", "RequestLog", "[LOGID] = " & forms![form1]![SEL])
(Where SEL=2 and their is a LOGID=2)

or

AMatCost = DLookup("[Cost]", "Auxilary Materials", "[Material]='Polyester Plate'")
(Where Material contains a value of 'Polyester Plate')

 

[des0906]

Many thanks for the replies. As a final note, I simply did a conversion on the Me.txtAssign value in the Dlookup function:

Me.txtBC.Value = DLookup("[BC]", "BudgetCenter", "[Extension] = '" & Int(Trim(Me.txtAssign)) & "'")