Thanks, but I think it's not what I'm trying to do.
I have a form with both Producers and Things column, and what I'm trying to do is to make Things list display onlyThings wich were made by Producer set in first column.
I could not agree more with wazz. He has put you on the right path. A query is really the most efficient way to do what you want to do.
However, because there are more ways than one to do things in Access, if you just don't want to use a query then, in this case (because you are going to pull your data from your single table "Things" table) you can just make the "Things" table to be the record source for your form. Then use a combo box to display the list of Producers, from the "Producers" table.
I would suggest that you use the Producer_ID and the Producer_name fields as the fields from the Producer table in your combo box. Then set the column count to 2 and set the column widths property to 0; 2
This should produce the list of Producers in the combo box where the combo box displays the Names of the Producers but returns the Producer_ID as its value.
Then, you can use the After Update event of your combo box to filter your form to show only the Things made by that Producere. You will need to create a filter statement and apply that filter. Something like this:
'Place the code below in the After Update event of your combo box:
Code:
Dim lngProducerID As Long
If Me.NameOfComboBox > 0 Then
lngProducerID = Me.NameOfComboBox
Me.Filter = "Producer_ID = " & lngProducerID & ""
Me.FilterOn = True
End If
In the code above, you must replace the "NameOfComboBox" with the acutal name of the combo box that you will create that has the Producer information.
That should get you the results you are wanting.
Now, the reason that wass and I both said that using a query would be a more efficient method is that instead of having to have this code that will create and apply a filter to your form, you could simply create a query that would include all the fields you need form the "Things" table but also has, as part of its design, a criteria on the Producer_Id field that refers to the value returned by your combo box that has the Producer information. You would just use this query as the record source for your form and not the "Things" table directly
Then place the following single statement in the After Update event of you combo box:
Me.Requery
Thats it.
Now you have at least two ways to get the results you want.
I have also attached a quick demo database that will show just how the use of the query works.
Thanks alot Mr. B for your long reply, that was indeed what I wanted, i just couldn't see it.
And thanks a lot for that database, it helped lot! I'm really grateful for that.
Rep points added, but I have just one more question.
Is it possible to have 2 combo boxes instead of 1 combo and 1 text field (like in your demo database)? I've been trying to do that, but somehow it keeps listing me only first record of that specific producer...
Ok, I have added the second combo box. However, I suspect that you may see results that you may not have expected. Using the same methodology that was employed with this form, no records are displayed in the form until a selection has been made from both combo boxes.
I suspect that you might really want a situation where when a Producer is selected that all of the Things that the selected Producer made would be displayed in the form. Then when a selection is made form the List of Things (which has been filtered to only show things made by the selected Producer) on the selected Thing would be displayed in the form.
I have attached an updated version of the database file that I had previously send. This file has two forms. I have included some notes on each form for explanation purposes.
Bah... i said it to early i guess... I mean, no, everything is working but as long as i have two tables only, and as topic says, there is this unlucky third table
Besides tables Producers and Things, i do have a third table Sales. Sales contains Producer_ID, Thing_ID and Sold_Date.
So i was able to create form in which i could choose Producer_ID and Things_ID (that where made by selected Producer_ID).
And the thing is, i want to create a form where i could add new records to the Sales.
Now the source of records in form is set to the query (so it can show Things made by Producer_IDonly), and that's why it can't add anything to table Sales.
Well, here is the demo Db again. This time look only at the first form.
Now, you did not get your total wish list. In order to make this work in a way that the user can simply add the sold date, I have used yet another form. This one is a popup type form and is used only to add the sold
date. If in your real world you need more data entry you can add that.
Using a continuous form brings certain complications when it comes to trying to had combo boxes for user to make selections for data entry.
I delayed with replay to check if I wont have any more problems/question, but it seems I don't and wont have (at least in this topic). I would like to thank you once again for your help and those demo databases. It helped me big time!
Marry me.
It is not necessary to make sure that you don't have more questions. You having questions and someone attempting to answer those questions is just exactly what causes all of us to learn. I have said before and will say again that I learn just as much if not more that those that I attempt to try to help.
It has been a pleasure working with you to get you the boost you needed to move on with your project.
Don't be a stranger. Remember the only dumb question is the one you did not ask.
By the way, I have only been married for 46 years. I am just a kid. (Really I just married very, very young.)
It is not necessary to make sure that you don't have more questions. You having questions and someone attempting to answer those questions is just exactly what causes all of us to learn.
Yea, that's why I delayed with reply. I was making sure everything is clear for me, so in case it wasn't i i could just ask in reply. Wasn't it what I wrote? Well, at least it was what I meant
)
