I need to find a record in a subform for edit

[andyswen]

I am pretty new to access, so sorry for the basic question. I have a form with 3 sub forms and need to be able to access a specific record in a subform for edit purposes. The main form holds a product (wine) and the sub form in question is a bin location. I want to add locations as wine is added to the cellar, or remove locations as wine is sold.

The main form has a button for locations which opens another form but I need to go to the correct record (bin location) in order to edit the correct record.

I would also like to better understand how to get the main form to go to a specific wine by name rather than scrolling through the database. I used the template from Microsoft's Web site as a basis and have edited it pretty heavily so far.

Any help is greatly appreciated.

Andy

 

[Trendsetter]

Hi Andy,
This is all about table structures, if you have a number of locations for wines then you need a related table which will be one to many related by WineID.
So when you select a WineID from the locations table there will be the number of records that correspond to the number of locations that wine is stored in.
As far as the main form going to a selected wine, there are lots of examples of the various methods used but generally a combo is used to list all the wines and the selected wine will use a query or a recordset to locate the selected record.
I have a sample of a wine transaction and stockholding application that you can have if you let me know.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[andyswen]

That would be great, Thanks! I have a separate table set up currently - main table is wine list with wines in it and the second table is locations with one record for each bin. I do have the subform working correcly where it will display a list of rows with each bin containing the wine from the main form.

My issue is with being able to open a form off the main form at the click of a button to allow the user to navigate to the correct record in the bin table (based on location code) and then have the program add the wine list ID (unique key for the main table) to the bin location record. Then when the form closes the requery will show the new bin location along with the others. Same deal for when a wine is consumed/sold. Have a button that opens a new record in the consumed table (allows for ratings, & other info) and then remove the winelistID from the bin table record as well.

Again, I'd love to look at your app and I am happy to zip mine up and send it along as well. It is based off the MS sample app so I don't have copyright but it is a shared app so I don't see any issues there.

Andy

 

[Trendsetter]

Andy - you need to send me your email address

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[andyswen]

Emailed to your signature line address.