Filtering records to then add a new one

[Benny1973]

Hi

I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.

For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?

I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks

 

[mikevds]

Benny193

How do I filter records by a form
CD Group CD Name display only related records

 

[godofhell]

Benny1973, you can do 1 of 2 things.
1. Create a Table with your supplier codes and Supplier name, then give the users a combo box to select the Supplier Name and use that to run a query that selects the Top value for that supplier name. Doing this will allow you to select the highest code that exists in the database for that supplier. This is also known as a cascading combo box.

2. Create text boxes on the form showing the top value for each supplier. This will require quering the database and writing that top value on to the text boxes for each supplier.

I highly recommend option 1 since this way the user can simply select the name of the supplier and the highest code entered will show. You can even write code against that value and write in the valid code yourself without the user having to write it in.
Note: the SQL Select statement would be SELECT TOP 1 [tblSuppliers].[SupplierCode], [tblSuppliers].[SupplierName] FROM tblSuppliers WHERE ((([tblSuppliers].[SupplierName])=[Forms]![frmMaterialList]![cmbSuppliers])) ORDER BY [tblSuppliers].[SupplierCode] DESC;
See the results in the attached database.

 

[Benny1973]

Thank you very much - that's great

Thanks for your help, very much appreciated