DLookUp Problem

[mikeTTI]

I have a subform. I need to use a DLookUp function to set the default value of [Grower] on the subform, based on the value of [Farm] in the same record (but earlier in the datasheet). Both farm and grower value are found in Table.

The idea is that the suer types in a farm number and this sets the default value of the [Grower] combo box the the grower for that farm.

I am taking this approach because the default value will be correct 90% of the time, but not always.

There is a problem with the DLookUp expression I am using: -

DLookUp("[Grower]","tblLevyReceiptsDetail","[Farm] =
" & " Forms![Levy Receipts]![sbfLevyReceiptsDetail Label]![Farm]")

(I inserted the line break here just for readability, it's not in the expression)

I don't get an error message, but the default value is not coming through to the [Grower] combo.

My questions are:

1) Is the expression correct?
2) Can I use this method to set the default value for another field in the same record or should I do it with code?

Any advice much appreciated.

Cheers


Mike

 

[MStef]

Helo Mike!
1) I don't know why do you need DLookup in combo box ???
2) DLookup try this, if Farm is a number.
..........."[Farm]=" & [Forms]![FormName]![SubFormname]![FieldName])
But this if Farm is a text field.
..........."[Farm]=""" & [Forms]![FormName]![SubFormname]![FieldName] & """")

 

[Galaxiom]

Refer to the full name of the farm in the condition:
[tblLevyReceiptsDetail].[Farm]

Always best to spell it out fully.

 

[mikeTTI]

Thank you everyone for your help, but I am still stuck.

I have a subform which contains two fields [Grower] and [Farm].

The subform is based on a table [...Details] that contains harvest records.

[Grower] and [Farm] are both lookup field based on the tables [...Contacts] and [...Farms].

The business situation I am trying to capture is that farms are owned by one person, but are sometimes partly sub-leased. Therefore a harvest from a particular is normally attributable to the [Farmer] listed for that farm in the [...Farms] table. But, in 7% of cases this won't be the case.

In the subform when a user enters a value in [Farm], I want that value to be used to set the Defaultvalue for [Grower] on the subform, based on the value of [Farmer] in the record in the [...Farms] table in which the field [Farm] has the same value as the control [farm] on the subform.

E.g. In the [...Farms] table the [Farmer] for [Farm] '123' is 'Smith'.

When a user enters '123' in [Farm] on the subform I want to set the default value property for [Grower] on the subform to 'Smith'.

I thoguh I could do this using a DLookUp expression in the default value property for [Grower], but this is not working at all.

I have also tried it in code in the lost focus event of the [Farm] control:

Private Sub Grower_LostFocus()
'Me!Grower.DefaultValue = DLookup("[Farmer]", "ListFarms", "[ListFarms].[Farm] = " & " Forms![Levy Receipts]![sbfLevyReceiptsDetail Label]![Farm]")
End Sub

But I get an error message saying:

Runtime Error 2001
You cancelled the previous operation

I am stumped. I think I have the syntax correct, and I think this is a suitable way of going about it. But no go.

 

[MStef]

The error 2001 means that you made a mistake somwhere in the name, (field name, tbl name, form name). Check yor VBA.

 

[Galaxiom]

The concatenation in the DLookUp is incorrect. Leave it out.

Me!Grower.DefaultValue = DLookup("[Farmer]", "ListFarms", "[ListFarms].[Farm] = Forms![Levy Receipts]![sbfLevyReceiptsDetail Label]![Farm]")

 

[mikeTTI]

Thank you Galaxiom for pointing out the error in my expression. I have corrected this, but it still isn't working.

I am still getting the 2001 error code.

 

[Galaxiom]

[sbfLevyReceiptsDetail Label]

I didn't read closely before.
Could you have accidentally referred to the form's label instead of the form?

 

[mikeTTI]

I saw that and fixed it. In the revised expession I am refering to the control/frame that contains the sub form on the main form.

So still something wrong ... ...

 

[Galaxiom]

If you are referring to a field in the Record Source of a subform

Forms!MainFormName!SubformControlName.Form!FieldName