Macro 1

[melisc]

Hi,

I have written the following expression to set a value in a field on a form based on the data in another filed:

DLookUp("[countyID]","locations","[location ID] ='" & [Forms]![Receipt Entry]![County ID])

I am trying to have a field self populate based on another fields data and I need it to go back to the table and look the data up.

I am entering data in the Location Field and I need it to go to the table and return the correct County based on the Location.

Any ideas or suggestions will be greatly appreciated!

Melissa

 

[mgolla]

On AfterUpdate Event of Location Field:

Dim stCounty As String

stCounty = Nz(DLookup("[countyID]","locations","location ID]='" & Form_ReceiptEntry.[County ID] & "'&quot,"&quot

CountyIDFld = stCounty

If no county ID found will return a blank string

 

[melisc]

Thank you for your help. I entered the expression from this point on and entered it as an Expression Builder.

Nz(DLookup("[countyID]","locations","location ID]='" & Form!ReceiptEntry.[County ID] & "'&quot,"&quot

Did I miss something because it is returning an error that reads the following:


The expression After Update you entered as the event property setting produced the following error: The object you referenced in the Visual Basic procedure as an OLE object isn't an OLE object.

* The expression may not result in the name of a macro, the name of a user-defined function, or {Event Procedure].

* There may have been an error evaluating the function, event, or macro.

I have read and read and read and there seems to be little explanation in my Access 2000 book that explains this process well. Thanks again for your assistance.

 

[Steve101]

I suspect from the last post that you're not using the AfterUpdate event as proposed by mgolia.

If you are using the DLookup directly in the ControlSource of a form's control, then you will need to prefix it with an equal sign; eg.:
[tt]
[red]=[/red]DLookup("[countyID]","locations","location ID]='" & Form_ReceiptEntry.[County ID] & "'&quot
[/tt]

Having said that, a better approach would be to use joined tables as the form's recordsource to allow the value to be retrieved as part of the form's recordsource; eg. create a query with the existing table which feeds the form, then join on the locations table via the [location id] field, and you will have the CountryId field available directly without the DLookup.

Be aware however that with this approach, you will need to have the tables appropriately indexed on the join fields, for the recordsource to be updateable.




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[melisc]

Hello,

Thanks for the tips.

I was placing the dlookup command in the after update field. I used the expression builder and entered it there.

Dim stCounty As String

stCounty = Nz(DLookup("[countyID]","locations","location ID]='" & Form_ReceiptEntry.[County ID] & "'&quot,"&quot

CountyIDFld = stCounty

The only part of the above string that I entered in the Expression Builder was

Nz(DLookup("[countyID]","locations","location ID]='" & Form_ReceiptEntry.[County ID] & "'&quot,"&quot

Was that correct? I did not want to use the control source because I want the value to be returned to the Receipt Entry Table.


Thanks again.