Combo box with subform

[Jerseynjphillypa]

I am trying to get the combo box to show only Distinct item names from my table and then show the item information in my subform. I tried going to the data-row source and typed in Distinct but that didn't work

Also how can i add another combo box to select the field "From" based on the first combo box item i choice. But if nothing is selected in the "from", I want all of the item to show in the subform.

[alansidman]

In your row source, open the query that is the basis for the combo box. Once open, select the Sigma (Group By) Icon. See this video.

http://www.datapigtechnologies.com/f...combobox1.html

Can't read your file as only have 2003 at work. So I am guessing this is the issue.

[Jerseynjphillypa]

It says its Access 2007 when I load it on my computer

[TG_W]

I think alansidman was meaning he can only has 2003 at work and can't view the db you uploaded since it is saved as an .accdb file format rather than .mdb.

Question - do you need to show the information in a subform in datasheet view? If not, there is a much easier solution.

Also, there does not appear to be a field named "From" in the sample you uploaded.

Finally, the field "Date" - "Date" is a reserved word in Access and could cause future issues. You might consider changing to "IDate" or something like that.

[Jerseynjphillypa]

Thanks for catching that error. I meant to say from the field called "for".

Also it doesn't have to be in datasheet view. As long at the data is being showed. What method of viewing did you have in mind

I change Date to iDate. Thanks for the tip.

[TG_W]

Putting all of the information in one form. If you move the combo box to the Form Header area, you can pull up the table information easily. Try doing the following:

1. Make the form a "Continuous Forms" in the Default View under the Format tab
2. Drag the Combo box to the Form Header area.
3. Change the column count to 1 for the combo box in the Format tab; Under the Data tab, click the "..." in row source and delete the "ID" field; at the top of this screen, press the Sigma sign as alansidman suggested; save and close.
4. With the box still selected, go to the Event tab in the Property Sheet and click the "..." by After Update. Select "Code Builder". Enter the code below. Save and close after.
Me.FilterOn = False
Me.Filter = "[Item] Like '*' &[Forms]![Table]![Combo16] & '*'"
Me.FilterOn = True
Me.Requery
5. In the Ribbon at the top, go to Design and select "Add Existing Fields". Drag the fields you want shown out to the "Detail" area of the form.
6. Select all of the fields and well as labels, go to Arrange in the Ribbon at top, and select Tabular.
7. Add a "Clear Filter" button by selecting "Button" in the Ribbon under Design. Press the "Esc" key when the wizard pops up. Press the "F4" key. Under Event, click the "..." on "On Click", select Code Builder, and enter the code below.
Me.FilterOn = False
Me.Combo16 = Null

Arrange the form how you like from there.

As for populating a combo box from another combo box:
1. Create a second combo box - I copied and pasted in your database, it made Combo26
2. Add the below code after Me.Requery in the Combo16 AfterUpdate code
Me.Combo26 = vbNullString
Me.Combo26.Requery
3. Select "..." under Row Source in the Data tab in the Property Sheet
4. Delete the fields selecteed and drag the field "For" down first then drag down "Item"
5. Change "Item" Total from Group By to Where and paste "Like [Forms]![Table]![Combo16]" in the Criteria without the ""

[Jerseynjphillypa]

Thanks for all the help so far.

I got the query not to show the repeats using the method both of you recommended, but the combo box is not showing anything listed.

[TG_W]

Clear the Column Widths. That should do the trick.

I am attaching the database you uploaded yesterday with the guidance provided in my previous post since I had a chance to play with it last night.

[Jerseynjphillypa]

Thanks for posting your file. When I was taking a look, the second combo box is still not filtering correctly, when you first select a item for the first combo box

Also when I tried to recreate the file, I get a mismatch error. I do not know what I am doing wrong.

[TG_W]

Sorry about that. I forgot to go back and add the filter after updating the second box. My sincerest apologies.

Test combo box.accdb

All of the code is below for easy viewing.
Private Sub Combo16_AfterUpdate()
Me.FilterOn = False
Me.Filter = "[Item] Like '*' &[Forms]![Table]![Combo16] & '*'"
Me.FilterOn = True
Me.Requery
Me.Combo26 = vbNullString
Me.Combo26.Requery
End Sub

Private Sub Combo26_AfterUpdate()
Me.FilterOn = False
Me.Filter = "[Item] Like '*' &[Forms]![Table]![Combo16] & '*' and [For] Like '*' &[Forms]![Table]![Combo26] & '*'"
Me.FilterOn = True
End Sub

Private Sub Command25_Click()
Me.FilterOn = False
Me.Combo16 = Null
Me.Combo26 = Null
End Sub


Not sure why you are getting the mismatch error. How are you trying to recreate?

[Jerseynjphillypa]

Thanks for all your help again. I was missing one part of my code. But thank you for working through the problem with me. This was really hopeful.

Also how would I eliminate the row called (NEW) since I want to do calculation. Such I want to find the total sum of Quantity and Total for each item and by for at the end of the form.

[TG_W]

In the Property Sheet, set Allow Additions to No.

Copy all of the Text Boxes in the Detail portion of the form and Paste in the Form Footer. Click inside each box and add "=Sum([InsertFieldNameHere])". ID would look like =Sum([ID]). So on and so forth. If you only need it for certain fields and for the sake of easier alignment, copy and paste all of the fields and delete the ones you don't want to sum.

Glad I could help!

[Jerseynjphillypa]

Thanks for all your help and guidance. I enjoyed learning all these new tricks in Access.

[TG_W]

You're quite welcome. Glad I could help!

[Jerseynjphillypa]

Sorry i have another question.

Is there a way I can use the combo box in the reverse too. So if I select "For" first and then the "item".