Dlookup formula in subform referencing control on Form not working

[Tophan]

Hello,

I have a form name frmInvoice and sub form named frmInvoiceDtls. In frmInvoiceDtls I put the below formula

=DLookUp("[DlyRate]","tblSubs","[SubName]='" & [Forms]![frmInvoice]![cboSub] & "'")

I want it to lookup up the subcontractor name displayed in frmInvoice in the table tblSubs and return the pay rate for that sub. However, I'm not even getting an Error on this formula - it's just blank. All other attempts returned Error.

Any ideas where I'm going wrong?

 

[plog]

What value does [Forms]![frmInvoice]![cboSub] actually contain?

Create a text box and set it to that. Most likely its a value that you didn't expect and isn't in tblSubs.

 

[CJ_London]

are you sure your combo is returning a name and not a primary key number? ie. is your rowsource something like

SELECT SubID, subname from tblSubs

if so try

=DLookUp("[DlyRate]","tblSubs","[SubID]=" & [Forms]![frmInvoice]![cboSub])

also, you can refer to the parent form as parent so instead of

[Forms]![frmInvoice]![cboSub]

you have

[Parent].[cboSub]

 

[Tophan]

Sorry for the delay in responding. cboSub looks up the sub-contractor name in tblSubs.

tblSubs only has 3 fields - ID, SubName, DlyRate. The combo is showing the name only not the ID field. I tried changing the formula to reference the ID field but now I'm getting an error.

I found a roundabout way of bringing in the DlyRate but was hoping to get the Dlookup formula to work.

 

[CJ_London]

OK, slowly we get a bit more information. However saying

The combo is showing the name only not the ID field

is not answering the question

is your rowsource something like

SELECT SubID, subname from tblSubs

 

[gemma-the-husky]

what CJ said

If the combo box is bound to a different column to the one displayed, you can't get this to work.

generally speaking, you may well have a numeric field in your lookup table, not a text field.

 

[Tophan]

The row source for the combo box cboSub is

SELECT [tblSubs].[ID], [tblSubs].[SubName] FROM tblSubs ORDER BY [SubName];

Thanks for your help

 

[CJ_London]

so your dlookup should be

DLookUp("[DlyRate]","tblSubs","[ID]=" & cboSub)

 

[Tophan]

That worked! Thank you. I amended it slightly as the combo box is in the parent form so I used the below formula

=DLookUp("[DlyRate]","tblSubs","[ID]=" & [Forms]![frmInvoice]![cboSub])

Thanks again.

 

[CJ_London]

re parent form - reread post #3 - using parent is faster than going via the forms collection - and the solution is identical to what I proposed with the exception of SubID rather than ID

 

[Tophan]

Ok...changed the formula and that works too! The Dlookup formula always gives me trouble (I always the quotation marks are in the wrong place) I've been trying to use it more often to try and master it. I use it all the time in Excel with multiple criteria but in Access it is still a challenge but I'm slowly getting there.

Thanks for the tip and your help today.

 

[CJ_London]

dlookups should only be used sparingly, they (and the other domain functions) are painfully slow. Not too apparent on a single form, but noticeable on continuous forms and datasheets.

In your case you would probably be better to have the dlookup on the main form (so it only runs once) and then just reference it from your subform.

However the most efficient way would be that since you are selecting a contractor on your main form using the cboSub with tblSubs as the rowsource then bring it through there

1.modify your rowsource to

SELECT [tblSubs].[ID], [tblSubs].[SubName],tblSubs].[DlyRate] FROM tblSubs ORDER BY [SubName];

2. change the number of columns property from 2 to 3
3. change column widths property to 0;3;0

then instead of dlookup you would use

=parent.cboSub.column(2)

 

[Tophan]

I'll give it a try