DLookup to put a Number on a Form (2 Viewers)

[Malcolm17]

Hello,

I have a line of code that I want to lookup a number and display it on to a textbox on a form. Once this has happened the next step is to do a calculation.

My line of code is

Forms!frmBackground!subfrmRestaurant!subfrmRestaurantBookingEdit!txtMinTableCovers = DLookup("MinCover", "tbl_Net_RestaurantTables", "Display = Forms!frmBackground!subfrmRestaurant!subfrmRestaurantBookingEdit!cboTableSelect")

(MinCover is a Number, Display is Text, cboTableSelect is Text)

This puts the right number in to the textbox, however seems to treat it as text as once done the calculation doesn't work - however, if i type in the same number in to the textbox the calculation works fine, this is why I think my DLookup line is wrong.

Where am I going wrong please?

Thank you,

Malcolm

 

[Minty]

How is the calculation achieved?

If it's in the after update event of that control, that won't fire when you change the data using code.
You would have to force the calculation to happen after updating the value via any code.

 

[Gasman]

Is this code actually in Forms!frmBackground!subfrmRestaurant!subfrmRestaurantBookingEdit

TBH I would have thought you would have to concatenate Forms!frmBackground!subfrmRestaurant!subfrmRestaurantBookingEdit!cboTableSelect to the criteria for the DlookUp?

 

[CJ_London]

however seems to treat it as text

perhaps the field you are looking up is defined as text? You can try using the val function (or cInt,cLng, eval functions) to force it to be a number

val(dlookup(field, table, criteria))

 

[Malcolm17]

Fabulous, thank you!!

I concatenated the DLookup and added Val.

It is working now, thank you

 

[June7]

Expression worked without concatenation because control reference uses a fully qualified path. Which form has this code?

Could have this expression in textbox ControlSource instead of VBA.

=DLookup("MinCover", "tbl_Net_RestaurantTables", "Display = '" & cboTableSelect & "'"

However, I suggest eliminating domain aggregate function. Options:
1. include MinCover field as a column of combobox then reference that column in ControlSource or directly in formula.
2. include tbl_Net_RestaurantTables in form RecordSource then bind textbox to MinCover field (lock it so can't edit).