Form w Subform, inserting, updating,deleting subform results

[Dazzer]

LO 6.3, HSQLDB, Mac OS Mojave

Hi there

New user here, did some minor work with MS Access years ago, now creating a project for a work friend

Simple(ish) database - Guests Tbl (Guest_ID, Name , etc) RentalEquipment tbl (Equipment_ID, Description, Price)

I can show the Guests on mainform and their Rentals on a subform of mainform however no updates,deletions,additions are allowed to subform as it’s greyed out - I understand this to be because I am using a query to extract from the two information tables.

I have tried using just SQL commands and also tried using a junction table, the junction table method allows updates/changes (as the + is not greyed out and I can manually type changes although I need them to be from a drop down list of the Equipment tbl to prevent user error and misinformation being entered)) to the existing information. I want to add to the Rentals for a particular Guest just the items from a specific list of items on the Rental ItemsTable and not allow free text to be typed in and then once the added/changed/deleted information is entered for the subform to update itself

Initially if someone could give me a pointer in the right direction as to whether to use a junction table or some other method to achieve this I would be most grateful - a very brief example would be extremely helpful.

Guests information should be updateable to rent NONE, 1, or MULTIPLE items at the same time

Thanks in advanceDive_Guests.odb

[Ratslinger]

Hello,

There are many methods to do what you want but did this with mostly existed already.

It appears you, as many seem to do, are trying to do too much on a single form. It then becomes very busy. One form for Guests and another form for Equipment Rentals.

Created a new form → Guests_Equipment_Rentals. The form can be re-designed in many ways including a list box to select a Guest (search table/form filtering on this site for examples). Simple design using a table control for the Guests. For adding rentals used tbl_JCN_Guests_Equipment table on a subform linked back to the main form on Guest_ID. Then on another subform used your SQL to display all items rented for that Guest. Again linked back to the main form with Guest_ID. Then as an added feature add a sub sub form to that to display a total using “TotalQuery” query.

Now there are some way to further automate many things, especially when using macros (fairly big learning curve in Base) but after adding or deleting an item, the “All Equipment” section can be refreshed by pushing the button. It will also update the total amount. This button simply has the Action property set to Refresh form and essentially re-executes the SQL.

Sample ------ Rentals.odb

[Dazzer]

Hi there

Thank you very much for your assistance, that’s a great help. I was starting to wonder if I was trying to do too much with one form myself so thank you for that guidance as well.

Very much appreciated

Best Wishes

Darrin