I have a form which displays the fields of a particular record. On the same form I have a combo box which is limit list to one of the fields of the table. What I would like it to is when a selection is made in the combo box to update the rest of the forms display according to the new selected record. Is there a way to do this or the only way is to close the form down and start it up again at the particular record.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Update record on form accroding to the selection of a combo box 1
- Thread starter aizeret
- Start date
That's a very interesting question. I have a question too,
that has something to do with yours:
I have a form with 2 combo boxes.
The 2nd combo should have it's options, according to the selected option in the 1st combo box.
I have a table that holds 2 fields, like:
category sub-Category
-------- -------------
books science_fiction
books general-studies
books computing
disks pop
disks rock
disks house
etc....
Now, The 1st combo-box in my form, holds the list of categories (a result of a select distinct query).
I.E.: books, disks, etc.
I'd like the 2nd combo-box to hold a list which it's options
are "pop, rock, computing" if "disks" is selected in the 1st combo, and to hold "science_fiction, general-studies,computing" if "books" is selected on the 1st combo box, etc.
Anyone?
that has something to do with yours:
I have a form with 2 combo boxes.
The 2nd combo should have it's options, according to the selected option in the 1st combo box.
I have a table that holds 2 fields, like:
category sub-Category
-------- -------------
books science_fiction
books general-studies
books computing
disks pop
disks rock
disks house
etc....
Now, The 1st combo-box in my form, holds the list of categories (a result of a select distinct query).
I.E.: books, disks, etc.
I'd like the 2nd combo-box to hold a list which it's options
are "pop, rock, computing" if "disks" is selected in the 1st combo, and to hold "science_fiction, general-studies,computing" if "books" is selected on the 1st combo box, etc.
Anyone?
-
1
- #3
The criteria for the row source of the second combo box needs to be the value of the first combo box.
Make sure combo1 is named properly - then edit the row source of combo2 - use the expression builder or type:
[Forms]![frmXXX]![Combo2] (of course, combo2 should be named properly as well)
After Update of first combo box you need to requery the second (and probably set the value to null)
sub Combo1_afterupdate
combo2 = null
combo2.requery
end sub
Make sure combo1 is named properly - then edit the row source of combo2 - use the expression builder or type:
[Forms]![frmXXX]![Combo2] (of course, combo2 should be named properly as well)
After Update of first combo box you need to requery the second (and probably set the value to null)
sub Combo1_afterupdate
combo2 = null
combo2.requery
end sub
Here's how to do it, if you're new to it - sorry if I oversimplify:
In design view of the form, right click combo2 and view its poperties. Click the row source property, then the ... and you will be in a QBE Grid.
Under the category field, click in the criteria section. Click the ... on the tool bar for the expression builder.
In the expression builder, - first column - Double click Loaded Forms, then double click the form which contains the combo boxes.
In the second column, double click the control which is combo1 and click OK
Result should be criteria in Category
[Forms]![frmXXX]![cboXXX]
Close the qbe grid, saving changes - And IMPORTANT if you are in 97 - click off the row source property to any other property - if not, change to row source might not be saved.
In design view of the form, right click combo2 and view its poperties. Click the row source property, then the ... and you will be in a QBE Grid.
Under the category field, click in the criteria section. Click the ... on the tool bar for the expression builder.
In the expression builder, - first column - Double click Loaded Forms, then double click the form which contains the combo boxes.
In the second column, double click the control which is combo1 and click OK
Result should be criteria in Category
[Forms]![frmXXX]![cboXXX]
Close the qbe grid, saving changes - And IMPORTANT if you are in 97 - click off the row source property to any other property - if not, change to row source might not be saved.
- Thread starter
- #7
Hello avivit,
This is how I would do it....
The 1stCombo look at the properties, click on the 'Event' tag, go down the list until 'After Update'....this should be changed to [Event Procedure]. When you click on this Access creates a function in VB. You have to feel in the body of it. Add the following code so the function looks like..
Private Sub 1stCombo_AfterUpdate()
if 1stCombo = books then
me![2ndCombo].RowSource = "science_fiction;general_studies;computing"
else
me![2ndCombo].RowSource = "pop;rock;house"
endif
Me.Requery
End Sub
The 2ndCombo Properties, click on the 'Data' tag, go down the list until ' RowSourceType'.....this should be changed to 'Value List'.
This is how I would do it....
The 1stCombo look at the properties, click on the 'Event' tag, go down the list until 'After Update'....this should be changed to [Event Procedure]. When you click on this Access creates a function in VB. You have to feel in the body of it. Add the following code so the function looks like..
Private Sub 1stCombo_AfterUpdate()
if 1stCombo = books then
me![2ndCombo].RowSource = "science_fiction;general_studies;computing"
else
me![2ndCombo].RowSource = "pop;rock;house"
endif
Me.Requery
End Sub
The 2ndCombo Properties, click on the 'Data' tag, go down the list until ' RowSourceType'.....this should be changed to 'Value List'.
aizeret, but the sub-categories can be changed all the time.
I don't want to write the sub-categories specificly,
but would like to check the category, and select the appropriate options of sub-category (to the 2nd combo) according to the selection of 1st combo (select from the table of category and sub-category).
I don't want to write the sub-categories specificly,
but would like to check the category, and select the appropriate options of sub-category (to the 2nd combo) according to the selection of 1st combo (select from the table of category and sub-category).
Nancy. Thanks for keep replying.
BUT.. I am sorry, I am afraid I do not undertand something here, or maybe I should clarify myself.
I understood the row source thing perfectly(It is already written this way in my form, just without the forms!formname, for it is in the very same form, and it does not make a difference if I change it).
I am not new to this at all, just never had to do such thing before.
What I don't see is how to handle the criteria:
Where and how do I write to select only the options in the 2nd combo, that matches the first combo?
I.e. - What do I do in the "after_update" of the 1st combo, in order to make a list of right options in the 2nd combo?
( It should be something like aizeret offered, but I do not want to set in advance the possible values, as I replied to aizeret. I don't mind if it's done not in vba. Just want it done.
)
Thanks both
BUT.. I am sorry, I am afraid I do not undertand something here, or maybe I should clarify myself.
I understood the row source thing perfectly(It is already written this way in my form, just without the forms!formname, for it is in the very same form, and it does not make a difference if I change it).
I am not new to this at all, just never had to do such thing before.
What I don't see is how to handle the criteria:
Where and how do I write to select only the options in the 2nd combo, that matches the first combo?
I.e. - What do I do in the "after_update" of the 1st combo, in order to make a list of right options in the 2nd combo?
( It should be something like aizeret offered, but I do not want to set in advance the possible values, as I replied to aizeret. I don't mind if it's done not in vba. Just want it done.
)Thanks both
Solved at lasttttttt:
Eventually I succeeded writing the sql statement as should,
and with your both help I managed to do what I wanted, as follows:
in the combo2 row source I wrote:
SELECT DISTINCT tbl1.field2 FROM tbl1
WHERE tbl1.field1=[combo1];
(row source type: Table/Query)
In the after_update event of combo1 I wrote:
Me!combo2 = Null
Me!combo2.Requery
Somehow, my very simple sql statement did not work at the beginning,
and in any case I did not realize I needed the requery anyway.
THANK YOU both very much
Eventually I succeeded writing the sql statement as should,
and with your both help I managed to do what I wanted, as follows:
in the combo2 row source I wrote:
SELECT DISTINCT tbl1.field2 FROM tbl1
WHERE tbl1.field1=[combo1];
(row source type: Table/Query)
In the after_update event of combo1 I wrote:
Me!combo2 = Null
Me!combo2.Requery
Somehow, my very simple sql statement did not work at the beginning,
and in any case I did not realize I needed the requery anyway.
THANK YOU both very much
RegionsRob
Programmer
I've gotten this far in a database of my own. My question at this point is: how do I save the values in the 2 combo boxes to a table so that the next time I come back to this record, the choices that I already selected are displayed?
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question