How to add listbox contents to a subform

[Aremo]

Hello,
I have five list boxes set to a table in my database. Here is what a few rows in my table look like...

ID IngredientName IngredientType Cost
1 Ham Meat $1.23
2 Beef Meat $3.45
...... ....
27 Lettuce Vegetable $0.22
28 Onion Vegetable $0.12
..... ....
38 Mayonaise Sauce $0.13

Hopefully you get the idea. The five listbox controls each show the list by the IngredientType, so one listbox shows vegetables, another Meat, and so on. Multi-select is turned on for each listbox. Here is what I want to do: In the form I want to have a subform that will show what the user clicks in listboxes. This running list, with an extra column next to it that will accept a number. Example: say the user selects 'Ham' from the meat listbox. The subform should then show 'Ham' and a space next to it where he can type an integer (allowing for more meat).

Subform:

Ham | 2
Lettuce | 1
Mayo | 1
Subroll | 1

If the user de-selects the item in the listbox I'd like the subform to delete the item from itself. Anyone have any knowledge on how best to accomplish this?

Thanks!!

 

[burrina]

Here is an Example: Of course modify to suit your needs. As to the extra field, simply add a field to the record source for the subform.

If Me.lstlist.ItemsSelected.Count = 0 Then
    MsgBox "You must select at least 1 Item"
    Exit Sub
  End If

Dim strEmail As String
Dim varItem As Variant
For Each varItem In lstlist.ItemsSelected
            
         strEmail = strEmail & lstlist.Column(2, varItem) & ";"
     
Next varItem
If strEmail > "" Then
  strEmail = Left(strEmail, Len(strEmail) - 1)
  End If


Me.EmailTo.Value = strEmail

HTH

 

[Aremo]

I'm afraid I owe you an apology - I should have stated that I am doing this inside of Access 2007 - an Access form.

 

[Aremo]

Also - this appears to be attempting to send an email - I want to update a subform inside of Access 2007 not format / send an email.

 

[burrina]

The process is the same. That was an example: I have no idea of your setup.

 

[Aremo]

Okay - I can see some cross over in that you're building a nice long string of data to place into the subform without actually putting it there which unfortunately is where I am stuck. I can build a string using a loop but I do not know how to target the subform for this data?

From your example:

'Me.EmailTo.Value = strEmail

What I want to do:
Me.mysubform!myfirstcolumninmysubform = strEmail (this doesn't work obviously)

I have not worked with subforms before and I am sadly new to much of this so I thank you for your patience.

 

[burrina]

Take a look at this link.

https://support.office.com/en-za/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b?ui=en-US&rs=en-ZA&ad=ZA

 

[Aremo]

Thank you - no where in that link is the question "How do I make a VBA call from my form to the subform" answered but it does provide a very simple picture of what a subform is and how to add it to a page. It is as Dilbert once stated a huge binder full of information that at first glance looks useful but later you'll realize it isn't. I guess posting that was easier than writing a line of code though so you for the win.

 

[burrina]

Look on YouTube for videos, download some samples from this forum. Google is your friend, use it. Your asking for something not that complicated but yet you don't have basic access skills.

Access has built in help as well. Do some homework and post back.

 

[Aremo]

I thought I was asking a quick question: how to reference a subform from a main form. I didn't expect that question to be controversial and require cheekiness and misdirection. Hopefully you are not indicative of the population on this forum. I asked here because I thought it would yield a simple reply like "Oh you do it this way" rather than a song and dance about making a loop to send an email, and posting a link on how to use the wizard (which was thoroughly useless akin to giving one a map of Russia in response to the question 'How do I get from Chicago to California')

I will try this once more. I *know* how to construct a string thank you, I know how to gather inputs from form controls and create strings that I store in variables. I have used this method multiple times to create SQL statements. I can do this already (which is what you've provided in the example you gave). What I want to know is how do I take that string data and populate a subform with it. Right now when I try to reference the subform I am having issues as the subform either wants a record set or some other list to start off with.

Must I populate the subform with the entire contents of the database, blank it at form load, then add each item as it selected back into the subform?

Is that a valid question or must I attend 3 years of Access University before I am given the right for a non-sarcastic and useful reply?

 

[Aremo]

It is a datasheet - a blank datasheet at present although I can certainly populate it with the contents of the table I described in my first post. There are about 80 items in the table however so ideally I would start with a blank datasheet, the user would select an item from one of the list boxes, and the item would then appear in the subform.

The use case would be as follows:
1. Restaurant owner decides to make a new menu item
2. Restaurant owner opens form page showing list boxes populated by ingredients that are currently in the database (this part is complete) - there is a blank subform on the page.
3. The restaurant owner selects an item from one of the list boxes - this item appears in the subform.
4. The owner goes to the subform and types in an integer next to the item indicating how many servings (this will be a number between 1-9 with default of 1).
5. The owner selects other ingredients repeating steps 3 & 4 until new menu item is complete.
6. The owner pushes an "add menu item" button on the main form to add the item to the database.

Currently I have an ingredients table and a sandwich table with an association table named 'sandwichingredient' in the middle containing as foreign keys the primary keys of both the ingredients table and sandwich tables.