Solved

MS Access Dynamic combo box populate from database

Posted on 2008-10-11
3
1,665 Views
Last Modified: 2013-11-28
Hi there, i am facing a real problem, I created 2 tables.

MovieCategory(MovieCategoryID, 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?
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

Open in new window

screenshot-forms.jpg
0
Comment
Question by:applefruit
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22694546
I'm confused why you have a dropdown for both CategoryName and CategoryID:  they both represent the same column in movie table.

Nevertheless, let me proceed with an answer for the CategoryName cbo.
1.  Make MovieCategoryID the control source for that Cbo  (then it will be bound)
2.  Make Rowsource = "Select MovieCateoryID, CategoryName from MovieCategory"
3. Make Number of Columns = 2
4. Make Column Widths = 0;    (this hides the ID column)
5. Make Bound Column = 1
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22694569
OK, I see reason for two CBO's now:  one for selecting records; one for viewing/updating records.

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.
0
 

Author Closing Comment

by:applefruit
ID: 31505324
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now