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

[RG360]

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

[NeoPa]

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?

[RG360]

Can you possibly show me how to do it?

[NeoPa]

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.

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

[RG360]

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 :

[tblroomlist].[room]
[tblbuilding].[building]
[tblfloor].[floor]
[tblarea].[area]
[tblData].[roomid]
[tbldata].[buildingid]
[tbldata].[floorid]
[tbldata].[areaid]

on the data entry form, added a combo box, source = new query
added code to the combo box using recordsetclone

private sub combo73_afterupdate ()
dim rs as object
set rs = me.recordsetclone
rs.findfirst "[room] = '" & me.combo73 & "' "
if not rs.eof then me.bookmark = rs.bookmark
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

[NeoPa]

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.

[NeoPa]

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.