I'm new to the forum and fairly new to Access. Writing code is not my strong point!
I know this Cascading Combo Box thing has been written about a lot here, but I can't seem to find a solution that matches my problem.
I have a form that enters data with combo boxes into a table that contains a log of ICT equipment, with fields such as Room Number, Department, Category etc. This table is called tblAssets. I also have tables called tblRoom, tblDept etc, which my combo boxes use as look-up lists. These tables are updated when a new room number or whatever is entered.
At the bottom of the form I require a subform that can be used to view already entered records, but I need the form to be filtered. I would like the subform to contain two more combo boxes. The first should show a list of sorting options (to sort by room, department etc). I have another table containing this list. The second combo box should then (for example) provide a list of room numbers if the first box was set to Room. Finally (continuing with the "sort by room" example) the subform should update to show the records that the tblAssets table contains that include the room selected with the second combo box.
I hope this makes sense! I can manage the code to update the second combo box (I think!). I expect I have probably gone about this the wrong way, and I am quite prepared to start again if need be.
I'm not an expert at this, but I have encountered the same problem you describe. I can't give you specifics, but maybe I can point you in the right direction.
First, I'm not sure you need the subform. Maybe you want it for appearances or organization, but I don't think it's necessary.
Then, get your first two combo boxes working. The first combo box determines the list of the second combo, right? Okay, then the second combo determines the list of the third combo, no? I might have misunderstood your goal.
Once you get the idea of coding one combo to determine another combo, the rest of your work should be a lot easier.
Also, I assume your room name and room number are separate fields. Otherwise, you will probably have to use some code to identify the part of the field data you want displayed. . . a bit more tricky, at least for me.
I hope this helps get you started or past your current hurdle. Let me know if I can be of any further help.
If you want to avoid coding you can try somthing like,
use the query builder for the rowsource in the second combo box and in that query use the expression builder to set the criteria for the common field to the value of the first combobox.
If this does not fix your problem then you will need to use code to assign the rowsource of combobox 2 after combobox 1 is selected.
Private Sub Combo1_AfterUpdate()
Dim strSQL As String
'-- If the output of combo1 is the table name then
strSQL = Me.Combo1.Value
'-- If you need a SQL Query then
strSQL = "Query string ..." & Me.Combo1.Value & _
" ... Rest of SQL string"
Me.Combo2.RowSource = strSQL
Hello, everyone--Access 2007 newbie here and I'm trying to throw a computer equipment tracking DB together for my unit.
I am a new Access 2007 user and have almost no VB knowledge. I have followed MANY, MANY tutorials and tips, hints, and tricks, but they've all been unsuccessful (even the downloadable Sampe DB from Microsoft...). Unfortunately I'm not able to post the database, but here goes...
I have four tables:
Device Type -- Number (FK for PK_Device_ID)
Make -- Number (FK for PK_Make_ID)
Model -- Number
PK_Make_ID -- AutoNumber
Make -- Text
PK_Model_ID -- AutoNumber
Model -- Text
MakeID -- Number (FK for PK_Make_ID)
PK_Device_ID -- AutoNumber
Device Type -- Text
ModelID -- (FK for PK_Model_ID)
The "Model" field in "tblComputers" is a number b/c it had been a FK for PK_Model_ID, but I have since broken "tblModel"'s relationship with "tblComputers" and plugged it straight into "tblMake."
I am trying to do a (supposedly) simple synchronized combo box setup. A user selects "Device Type" from its combo box (cboDevice), and the models are limited to a certain item (i.e. laptop, printer, etc.). Then the user selects "Make" in a combo box (cboMake), and the choices in the "Model" combo box (cboModel) are limited to that particular manufacturer.
I'm sure the "Device Type" portion is all messed up as I was just trying to get at least the "Make" and "Model" part working, but nothing yet.
I have struggled for over 12 hours to get this thing to work without success. Can someone please help me out here? I'd greatly appreciate it!
Chris, It appears that you need to revisit your table design. From what I can tell by your descriptions device type should report to models not models to device type. If your goal is to identify each piece of equipment by category then you would want to remove ModelID from tblDeviceType and add DevID to tblModel. You could then have a combo box that selects the device type (printer, PC, notebook...), you would then use the value of that combo box to filter another combo box that would select the model. The same logic could be applied to a selection by Make. In this case there is no reason for the Computers table unless you want to add more information such as serial #, owner,...
Yeah, I do have some other pieces of data to capture, so the tblComputers isn't superfluous.
Thanks for the tip on the tblDeviceType--although I'm getting a little ahead of myself on that one since I'd be happy at this point to get just "make" and "model" working.
I think I've figured out where I'm going wrong (sort of). When I enter the following VB code into the AfterUpdate for cboMake...:
me.cboModel.RowSource = "SELECT Model FROM" & _
"tblModel WHERE MakeID = " & _
Me.cboMake & _
" ORDER BY Model"
me.cboModel = me.cboModel.ItemData(0)
...the query that SHOULD be generated here is NOT passed into the RowSource value of cboModel. I don't know enough about Access or VB to understand why, but this code is copied character-for-character from the numerous Microsoft tutorials out there, with only the combo box names changed, so there shouldn't be any reason for it not to work.
Ah, good catch, there, but that still didn't solve it...it's still refusing to pass the VB query code into the cboModel RowSource...
OK, so I can't attach my original DB, but I went ahead and put together a bare bones version of it with the relevant parts that are malfunctioning. I saved it in Access 2003 and Access 2007 formats. Could you please take a look at it and see what I'm doing wrong?
m new to access 2007...n making database of spares.which includes part no,main eqpt,nomenclature quantity and location. all these info is in main table(T_db1).i hav made a form..to issue any spare..it includs:
quantity to b issued
i want ,,whenevr i enter part no,..main eqpt,nomen,location shud b automaticlly come..i hav tried alot in cascading these combo boxes..bt :/
2nd problem is i want ,whenvr i issue any spare...this info is also save to anothr table...