Pre-populate fields on a Form from last record based on entry of field

[staton101]

Hi, I'm fairly new to Access 2016 and VBA and hope that I can get some help please.


I have a financial transaction Form and when entering a Vendor/Payee from a Combobox I want to fetch the latest Financial transaction entered for that Vendor/Payee and return 2 fields to populate onto the displayed Form. I think using 'On change' event on the Vendor/Payee field would be the way to go but I'm not sure about the coding required.
Can you help?

[Dave14867]

staton101,

Assuming that the fields you want are in the same table as the combo box is referencing, ensure that you include those fields in the combo box. I would use the AfterUpdate event. Then in the fields you want populated on the form you want populated use VBA in the AterUpdate event to populate those referring to the column of the combo box.

If you could attache a copy of the database it would be much easier to assist.

Dave

[Minty]

I would use the after update event for a combo, and ensure that you bring in the 2 fields required as hidden columns in the combo row source query.
You can the set the values on the form using (assume the Vendor is the first bound column)

Me.YourControl1 = Me.comboVendor.Column(1)
Me.YourControl2 = Me.comboVendor.Column(2)

Where comboVendor is your combo control name. Column numbering starts at (0)
YourControl1 and 2 are the controls you want to set the value in.

[staton101]

staton101,

Assuming that the fields you want are in the same table as the combo box is referencing, ensure that you include those fields in the combo box. I would use the AfterUpdate event. Then in the fields you want populated on the form you want populated use VBA in the AterUpdate event to populate those referring to the column of the combo box.

If you could attache a copy of the database it would be much easier to assist.

Dave

I want to retrieve 2 fields from the last transaction created for that Vendor/Payee (as they would probably be the same info) to save me from typing it in.
So I need to trigger a read of TransEntries table for the last entry for that Vendor and bring back Category and TransNote into the Form displayed.
Any assistance would be appreciated.

[Minty]

You don't need a trigger, you need a query or possibly two, joined to your vendor table.

This is the simple route;
Write a query that gets you the last transaction for all vendors.
Save that as qryLastTrans

In your combobox row source, add that query to your vendors table, and use an left join to link the vendor id's from the query and the table.
Add the fields you need , e.g. the vendor ID , Vendor Name , LastCategory, transnote.

Now your combo has the data required and can be populated on your form using the code above.
Try it, if you get stuck then post back where you got stuck and what you have tried.

Side note: I notice you are storing the vendor as a text field, you may be better storing an ID number if that's what is in your vendor table.

[staton101]

You don't need a trigger, you need a query or possibly two, joined to your vendor table.

This is the simple route;
Write a query that gets you the last transaction for all vendors.
Save that as qryLastTrans

In your combobox row source, add that query to your vendors table, and use an left join to link the vendor id's from the query and the table.
Add the fields you need , e.g. the vendor ID , Vendor Name , LastCategory, transnote.

Now your combo has the data required and can be populated on your form using the code above.
Try it, if you get stuck then post back where you got stuck and what you have tried.

Side note: I notice you are storing the vendor as a text field, you may be better storing an ID number if that's what is in your vendor table.

I have created a simple query that retrieves TransEntries SQL
SELECT DISTINCT TransEntries.[ID], TransEntries.[TransDate], TransEntries.[Vendor], TransEntries.[Category], TransEntries.[TransNote]FROM TransEntries
ORDER BY TransEntries.[TransDate] DESC;
But on running still returns all the records in TransEntries and not unique Vendor entries.
Also, the Vendor table doesn't have ID in it, the key is Vendor which will be unique.
thanks..

[Minty]

Okay - you are getting closer.

SELECT Max(TransEntries.[TransDate]), TransEntries.[Vendor], TransEntries.[Category], TransEntries.[TransNote]FROM TransEntries
GROUP BY TransEntries.[Vendor], TransEntries.[Category], TransEntries.[TransNote]

Try this as a query and you will see that it nearly gets you what you want.
Hint: As you have multiple categories you will need to actually need another sub query to just return the last TransactionID grouped by vendor and link that back to get the categories and notes.

[staton101]

OK, I can see how the query retrieves the entries grouped by Vendor, will the left join be part of the query?
Also, if I add this query to the combobox row source in my Form won't this cause an issue with 'On not in list' event (which I forgot to mention before), which then inserts a Vendor in the Vendors table if it doesn't exist.
Not sure I need to another sub query as I can use the Category from TransEntries.