Subform Question

[Jaybird114]

I am working with a copy of the Home Inventory database that comes with Microsoft Access or you are able to download from Microsoft. For my parents Estate, we figure it would be a good way to organize their belongings, put pictures in place and use it for the estate sale we will be haing to track the goodies.



From the existing database model, I made a new form of the Asset Details and slimmed it down a little for our purposes. I changed the Item field to be a drop down, so when you select the item for sale from the inventory of items, it would show up. I then updated the form to capture a few more things.

One thing that I thought would be easy would be to add a subform to the bottom of the Asset Details form. The idea I have is to be able to pick the item from the drop down inventory list. When the item is selected, the item and more detail would show up on the mainform and the subform. The subform would be populated with item id, description, asking and selling price information. I have that working . . . However, when I picked the 2nd item, I was hoping it too would populate in the detail form and append to the list of items selected. I figure then to use the detail form to create a report / printout of their items and summarize a total for everything. This would make the sale complete and simpler for one of us at check out. It would also help me manage the estate sales and track everything.

I have the item dropdown combo box working and filling in the details of the mainform. I also have the subform being filled in with the fields I need for the item selected. The issue I have is when I click on a 2nd item, the subform doesn't retain the first items information. The first item is replaced in the subform with the next item selected from the mainforms combo box selection.

It has been a while since I played with Access and I am not sure what to do. I figure it is some coding to add, but not sure what or where to add it. I have done some searches on the internet and have not been able to find what I am looking for exactly.

I am hoping somone could give me some insight on how to handle this. I appreciate the help ahead of time and look forward in hearing from someone in the near future.

Jay

[Dal Jeanis]

Currently your subform is doing a query based on a link field (master/child). As long as that is the case, you can only show one item below.

You can fix this pretty easily, by breaking that connection and giving it something else to connect to.

1) First, back up your database and do this entire modification in a test copy. First rule is back up early and often.

2) Create a table called tblSelected with one field, SelID, which is a number field, not a key, not an autonumber. This table will hold the keys of any items that have been selected. Manually enter any two or three keys for items that are on your inventory, to use for testing.

3) Copy the query that you were using as the recordsource for the subform. (Save the old query.) On the very end, add the words WHERE [ID] = [tblSelected].[SelID]. This assumes there is no WHERE clause there yet, and that the key of the inventory table is called ID. If there is a WHERE clause, use AND instead of where. If the key of the inventory is different from ID, use the name of the key field.

4) From the main form, in design view, click the very top left corner of the subform. Two little squares should darken, showing that you have activated the subform control on the main form. Over in the properties pane, on the data tab, change the Source Object to the new query name, and clear the link master fields and link child fields.

5) Switch to form view. You should see the two or three items that you manually added. This is the time to debug that connection.

6) When it's working, back up the database again and proceed.

7) In the afterupdate event of the dropdown box, you will add code that inserts a record into tblSelected using the key of the item selected. In the properties pane, Events tab, click the After update event, click the three dots, select code builder, and add these lines in the empty subroutine that comes up. Replace ****lstboxname**** with the name of your dropdownbox:

Dim strSQL As String
strSQL = "INSERT INTO tblSelected ( [SelID]) VALUES ( " & ****lstboxname****.Column(0) & " );"
' msgbox strSQL
CurrentDB.Execute

8) Switch to form view and test the dropdownbox by picking a few items to add. Verify that they appear in the subform. Currently we haven't done anything to stop it from adding twice, but that's a nit at the moment. If it doesn't work, uncomment the msgbox line and make sure that the message box ends in something that looks like something that "VALUES ( 5 );" where 5 is teh key to the item you are selecting. If not, then I have the syntax wrong, so fix it. try removing the .Column(0) off the end and see if that fixes it. If not, google to find out the correct properties of a listbox/combobox, or post the exact symptoms here and someone will figure it out with you.

9) When it's working, back up the database again and proceed.

10) Add a button "Clear Selections" to the form that will delete everything from tblSelected. In the properties pane, Events tab, click the On Click event, click the three dots, select code builder, and add these three lines in the empty subroutine that comes up:

Dim strSQL As String
strSQl = "DELETE * FROM tblSelected;"
CurrentDB.Execute

That code should work straight off.

11) When it's working, back up the database again and proceed.

You now have the ability to select multiple items, to display them in the subreport, to clear the selections.

You can also use the tblSelected in order to run your other reports and calculations, and to mark items as sold when you finalize the sale.

If you want, you could add a line of code or two to the code in number 7 above to test whether the same item had already been added to tblSelected, but that's a nit.

[Jaybird114]

Currently your subform is doing a query based on a link field (master/child). As long as that is the case, you can only show one item below.

You can fix this pretty easily, by breaking that connection and giving it something else to connect to.

1) First, back up your database and do this entire modification in a test copy. First rule is back up early and often.

2) Create a table called tblSelected with one field, SelID, which is a number field, not a key, not an autonumber. This table will hold the keys of any items that have been selected. Manually enter any two or three keys for items that are on your inventory, to use for testing.

3) Copy the query that you were using as the recordsource for the subform. (Save the old query.) On the very end, add the words WHERE [ID] = [tblSelected].[SelID]. This assumes there is no WHERE clause there yet, and that the key of the inventory table is called ID. If there is a WHERE clause, use AND instead of where. If the key of the inventory is different from ID, use the name of the key field.

4) From the main form, in design view, click the very top left corner of the subform. Two little squares should darken, showing that you have activated the subform control on the main form. Over in the properties pane, on the data tab, change the Source Object to the new query name, and clear the link master fields and link child fields.

5) Switch to form view. You should see the two or three items that you manually added. This is the time to debug that connection.

6) When it's working, back up the database again and proceed.

7) In the afterupdate event of the dropdown box, you will add code that inserts a record into tblSelected using the key of the item selected. In the properties pane, Events tab, click the After update event, click the three dots, select code builder, and add these lines in the empty subroutine that comes up. Replace ****lstboxname**** with the name of your dropdownbox:

Dim strSQL As String
strSQL = "INSERT INTO tblSelected ( [SelID]) VALUES ( " & ****lstboxname****.Column(0) & " );"
' msgbox strSQL
CurrentDB.Execute

8) Switch to form view and test the dropdownbox by picking a few items to add. Verify that they appear in the subform. Currently we haven't done anything to stop it from adding twice, but that's a nit at the moment. If it doesn't work, uncomment the msgbox line and make sure that the message box ends in something that looks like something that "VALUES ( 5 );" where 5 is teh key to the item you are selecting. If not, then I have the syntax wrong, so fix it. try removing the .Column(0) off the end and see if that fixes it. If not, google to find out the correct properties of a listbox/combobox, or post the exact symptoms here and someone will figure it out with you.

9) When it's working, back up the database again and proceed.

10) Add a button "Clear Selections" to the form that will delete everything from tblSelected. In the properties pane, Events tab, click the On Click event, click the three dots, select code builder, and add these three lines in the empty subroutine that comes up:

Dim strSQL As String
strSQl = "DELETE * FROM tblSelected;"
CurrentDB.Execute

That code should work straight off.

11) When it's working, back up the database again and proceed.

You now have the ability to select multiple items, to display them in the subreport, to clear the selections.

You can also use the tblSelected in order to run your other reports and calculations, and to mark items as sold when you finalize the sale.

If you want, you could add a line of code or two to the code in number 7 above to test whether the same item had already been added to tblSelected, but that's a nit.

Let me read through this all and try it on Monday. I have a few things to get done before I leave for the weekend. Thanks and I'll keep you posted.
Have a nice weekend!