By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Populate Combo Box from other table to view records in current Form

P: 4
Hello. I am new in Access and I need assistance from Pro's

I have a data entry form from 2 main tables and they have relationships with other tables.

My main table only contains Field ID's, because all of these fields are referenced from other tables.

On my form, these fields are Combo Boxes, wherein you can add/select from the list to populate the table.

All works fine, except I would like to create a combo box to find records based on my current form. But because the form came from a table that contains ID's only, the combo box lists only ID's.

My question is, is there a way to populate the data in the combo box that would reference the actual data from another table, and once its selected from the combo box, that the form will show the data associated?

Ex. Table1 has Fields RoomID, BuildingID, LocationID, AreaID.

then I have tblRoom (roomid, room), tblBuilding (BuildingID, Building), tblLocation (LocationID, Location), and tblArea (AreaID, Area)

The reason why I have it like this is because of the search combination that we need to make after. I already have a query based from all of those tables and I have a form to select the data needed for us to print.

but updating the data entry form takes time as we click back/forward on the recordselect.

Pleae help! I have been searching for answers and really could not find one.

Thank you in advance
May 11 '10 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
ComboBoxes can have multiple columns. It is perfectly possible to have a ComboBox, bound to your ID field, which displays other details from the table (in our example case [tblRoom]) but saves the ID as the value of the ComboBox. The ID column itself can even be hidden if you choose.

Does this answer your question?
May 11 '10 #2

P: 4
Can you possibly show me how to do it?
May 11 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
If you can narrow down what bit you don't understand. As it is the question is too open-ended for that to be practicable. You're really expected to ask for help when you've tried what you know. That way the questions are more specific.

I think I've given you something to start from, even if you are really new to Access. Have a try, and let us know what you struggle with, if you do.
May 11 '10 #4

P: 4
what i've done so far is to place a combo box on the form and use the fields from my form as the rowsource. i've been researching just to give me an idea on how to start but i did not find any to start with. i'm sorry but this is the best i could do for now.
May 12 '10 #5

P: 4
this is what i've done so far and i honestly lost

create a new query from the table that contains only ID's
i've added also the other fields from other tables related to the main table with ID's

so now on the query i have :
Expand|Select|Wrap|Line Numbers
  1. [tblroomlist].[room]
  2. [tblbuilding].[building]
  3. [tblfloor].[floor]
  4. [tblarea].[area]
  5. [tblData].[roomid]
  6. [tbldata].[buildingid]
  7. [tbldata].[floorid]
  8. [tbldata].[areaid]
on the data entry form, added a combo box, source = new query
added code to the combo box using recordsetclone
Expand|Select|Wrap|Line Numbers
  1. private sub combo73_afterupdate ()
  2. dim rs as object
  3. set rs = me.recordsetclone
  4. rs.findfirst "[room] = '" & me.combo73 & "' "
  5. if not rs.eof then me.bookmark = rs.bookmark
  6. end sub
i really need help. please tell me how to populate a combo box or use a recordsetclone so i could select from a combo box that would appear on the form and will make things easier for me to update entries instead of clicking back/forward

thank you again
May 12 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
RG360: I've been researching just to give me an idea on how to start but i did not find any to start with. i'm sorry but this is the best i could do for now.
OK. I'm not here to judge how advanced you are. If you don't get very far on your first attempt than that is not an issue. Just that you make the attempt.
RG360: What I've done so far is to place a combo box on the form and use the fields from my form as the rowsource.
You may have to help me a little here. I don't understand what you're trying to express. Forms don't contain fields. They contain controls. Forms are however connected to a recordsource which contains fields, and controls are often bound to underlying fields. Perhaps it would be easier at this point if you posted the RecordSource of the form, and the RowSource of your ComboBox control, in here for us to have a look at.
May 12 '10 #7

NeoPa
Expert Mod 15k+
P: 31,494
From your post #6 I see there are a few points to bring up. These should help you in dealing with us on the forum as well as help you generally in your work :
When posting any code on here please :
  1. Ensure you have Option Explicit set (See Require Variable Declaration).
  2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
  4. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
May 12 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.