dlookup

[Simon Harris]

Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch) ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--

 

[Andrew Tapp]

Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

 

[Andrew Tapp]

OK, try using a reference to the subform instead;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!frmSubFormName.Form!idAllocation)

Where frmFormName is the name of the form and frmSubFormName is the name of
the subform.

Hope this helps.

Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!

 

[Andrew Tapp]

See my previous post.

Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!