applefruit
asked on
MS Access Dynamic combo box populate from database
Hi there, i am facing a real problem, I created 2 tables.
MovieCategory(MovieCategor yID, CategoryName)
Movies(MovieID, MovieCategoryID, Title, ratings ...)
I have populated the MovieCategory tables with some data, and i want to test out my Movie form that i have created.
In my Movie form as attached in the screenshot, i have to fill in all the relevant movie details, however, i populated a dropdownlist from the MovieCategory table showing the Categoryname.
The problem that i face now is i want to enter the Movie datas into the movie table, but my dropdownlist is populated with the CategoryName, i need to change it to its repective MovieCategoryId but i am unable to do this. I have tried lots of methods but i still can't figure out how to solve this. Will really need some help here.
The code attached below is something that i have tried, but to no avail. Can anyone please advice me on how to go about it?
MovieCategory(MovieCategor
Movies(MovieID, MovieCategoryID, Title, ratings ...)
I have populated the MovieCategory tables with some data, and i want to test out my Movie form that i have created.
In my Movie form as attached in the screenshot, i have to fill in all the relevant movie details, however, i populated a dropdownlist from the MovieCategory table showing the Categoryname.
The problem that i face now is i want to enter the Movie datas into the movie table, but my dropdownlist is populated with the CategoryName, i need to change it to its repective MovieCategoryId but i am unable to do this. I have tried lots of methods but i still can't figure out how to solve this. Will really need some help here.
The code attached below is something that i have tried, but to no avail. Can anyone please advice me on how to go about it?
Private Sub ddlCategoryName_Change()
Dim strOne As String
strCatName = Me.ddlCategoryName.SelText
Dim strSql As String
strSql = "SELECT MovieCategoryId FROM MovieCategory WHERE CategoryName =" + strCatName
Me.ddlMovieCategoryId.RowSource = strSql
Me.ddlMovieCategoryId.Requery
End Sub
screenshot-forms.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DUDE! its complicated to explain to you why i did that in the first place. I understood you solution fully. And it solves all my problems! ROCK ON MAN. Thanks.
Generally, the selection CBO would go at the top--perhaps even in the form header. But, you can leave it at the bottom if you like. Also, it seems to me that you should select by Movie, not by MovieCategory--am I missing something?
Anyway, for the view/update CBO follow the directions I gave above.
For the selection CBO, the steps are the same except for step 1. Do NOT assign a control source--leave it unbound. In step 2, you can make the rowsource from the Movie table or the MoveCategory table--I'm confused what you want in that regard.