How to update a form after selecting a record with a combo box (1 Viewer)

[dazzler]

After selecting a record (for a vending machine), I want the form to display the highest meter reading for that machine. I then want to be able to add a new meter reading, a date, and money collected. The form then calculates and displays the number of drinks sold (the difference between the meter readings, and calculates the expected amount of money collected.

 

[MajP]

Use an unbound combo box in the header with a row source containing the Vending machine ID and vending machine name. A subform of Readings lnked to the combo box by Vending machine ID. The Reading subform has readingdate, moneycollected, and readingValue. The other fields are calculated fields for amount sold since previous, and money collected. Pretty basic.

 

[MajP]

See discussion here on how to calculate amount sold since last reading.

Finding the difference in two odometer readings

I have a database that I have created for my job to track daily fuel and mileage for 60+ pieces of equipment. On my main table I have RecordID, Quarter, Date, Branch, Truck #, Begining Odometer, Ending Odometer and a calculated field for Daily Total Miles. I then linked that database to a new...

www.access-programmers.co.uk

Denormalized database: update adjacent records

Hi gentlemens, I suppose you will kill me for question regarding denormalized DB, but only you can help me. I have table where I store Car log book: id Date car_id Odometer (absolute value) 1 12.10.2022 1 10 000 etc Now, as is probably evident, I store odometer values as absolute...

www.access-programmers.co.uk

 

[dazzler]

After selecting a record (for a vending machine), I want the form to display the highest meter reading for that machine. I then want to be able to add a new meter reading, a date, and money collected. The form then calculates and displays the number of drinks sold (the difference between the meter readings, and calculates the expected amount of money collected.

Many thanks for your reply. Do you know of any tutorials I could look at to help get me started with this form?

 

[mike60smart]

Many thanks for your reply. Do you know of any tutorials I could look at to help get me started with this form?

Have you not looked at the example that MajP supplied?

 

[dazzler]

Have you not looked at the example that MajP supplied?

I'm afraid this didn't make much sense to me. I think I need some more basic guidance.

 

[mike60smart]

In #2 MajP gave you specific information :-

"Vending machine ID and vending machine name. A subform of Readings lnked to the combo box by Vending machine ID. The Reading subform has readingdate, moneycollected, and readingValue. The other fields are calculated fields for amount sold since previous, and money collected. Pretty basic."

You need a table which has the following fields:- Vending machine ID and vending machine name
With another table to record the readings.

If you create these two tables and then post again we would be able to help.

 

[MajP]

I can probably demo this closer. However let me ask a few questions.
Lets assume a vending machine holds 100 cans. I loaded up.
I come back and there are 75 Cans left.
Do I always load if back to 100 or do you take a reading without loading?
What happens if I only have enough stock to partially fill it?

Unlike an odometer I think I have to store the amount of cans when I open up the machine (Amount Present) to take the reading and the amount of cans after I fill back up (TotalLoaded) before putting the machine back in service.
Then the calculation is not Previous Amount Present - Current Amount Present, but Previous TotalLoaded - Current Amount Present.

To do the cost estimate I assume this is a simple machine that only has one price and type of item. Since I do not think such old school machines exist anymore, I am guessing this is not a real world problem, but some sort of school assignment. if this is some kind of homework we can still help, but please be straight forward about it.

 

[MajP]

This is my thoughts.
You need a VM table with information about VMs
tblVendingMachines

VendingMachineID	VendingMachineName	VendingMachineSerial
1​	VM 1
2​	VM 2
3​	VM 3

In the reading table you need something like
tblVendingMachineReadings tblVendingMachineReadings

ReadingID	ReadingDate	ItemsCount	LoadedCount	MoneyCollected	VendingMachineID_FK	itemCost
1​	10/6/2022​	0​	75​	​	1​	$1.00​
2​	11/1/2022​	30​	90​	$45.00​	1​	$1.00​
3​	11/17/2022​	40​	70​	$50.00​	1​	$2.00​
4​	11/30/2022​	35​	80​	$80.00​	1​	$2.00​
5​	10/6/2022​	0​	75​	​	2​	$1.00​
6​	11/1/2022​	27​	90​	$50.00​	2​	$1.00​
7​	11/17/2022​	39​	70​	$69.00​	2​	$2.00​
8​	11/30/2022​	35​	75​	$73.00​	2​	$2.00​
9​	12/6/2022​	40​	80​	$75.00​	1​	$1.00​

ItemsCount is the number of cans when the machine is opened
LoadedCount is the number of cans after filling the machine
ItemCost is how much every item sells for. Since this is not fixed it is associated to the date.

To figure out the items sold it is the Previous LoadedCount - current ItemsCount
Expected Money is Items Sold * Previous Item Cost.

To calculate the previous anything usually involves a subquery which makes the query not editable. So you need a way to enter new readings separate from showing all your data.

The previous data query would be something like

SELECT A.ReadingID,
       A.ReadingDate,
       A.ItemsCount,
       A.LoadedCount,
       A.MoneyCollected,

  (SELECT top 1 B.LoadedCount
   FROM tblVendingMachineReadings AS B
   WHERE (A.ReadingID > B.ReadingID
          AND A.VendingMachineID_FK = B.VendingMachineID_FK)
   ORDER BY B.ReadingID DESC) AS PreviousLoad,
       [PreviousLoad]-[ItemsCount] AS ItemsSold,

  (SELECT top 1 B.itemCost
   FROM tblVendingMachineReadings AS B
   WHERE (A.ReadingID > B.ReadingID
          AND A.VendingMachineID_FK = B.VendingMachineID_FK)
   ORDER BY B.ReadingID DESC) AS PreviousItemCost,
       [previousItemCost]*[ItemsSold] AS ExpectedMoney,
       A.VendingMachineID_FK
FROM tblVendingMachineReadings AS A
ORDER BY A.ReadingID;

qryPreviousReadings qryPreviousReadings

ReadingID	ReadingDate	ItemsCount	LoadedCount	MoneyCollected	PreviousLoad	ItemsSold	PreviousItemCost	ExpectedMoney	VendingMachineID_FK
1​	10/6/2022​	0​	75​	​	​	​	​	​	1​
2​	11/1/2022​	30​	90​	$45.00​	75​	45​	$1.00​	$45.00​	1​
3​	11/17/2022​	40​	70​	$50.00​	90​	50​	$1.00​	$50.00​	1​
4​	11/30/2022​	35​	80​	$80.00​	70​	35​	$2.00​	$70.00​	1​
5​	10/6/2022​	0​	75​	​	​	​	​	​	2​
6​	11/1/2022​	27​	90​	$50.00​	75​	48​	$1.00​	$48.00​	2​
7​	11/17/2022​	39​	70​	$69.00​	90​	51​	$1.00​	$51.00​	2​
8​	11/30/2022​	35​	75​	$73.00​	70​	35​	$2.00​	$70.00​	2​
9​	12/6/2022​	40​	80​	$75.00​	80​	40​	$2.00​	$80.00​	1​

 

[MajP]

My design would be a Combo to select the VM, a subform to enter information, and a subform to display. In this case the two subforms are needed because the display subform is not editable.