Combo Box Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to remove an item from a combo box once a user has selected
it? What I am trying to do is create a form in which a user can choose the
shift to assign to an employee, once a shift is selected I want it to be
removed from the list so that only the shifts that are unfilled are listed.
Can this be done?
 
Shannon said:
Is there any way to remove an item from a combo box once a user has
selected it? What I am trying to do is create a form in which a user
can choose the shift to assign to an employee, once a shift is
selected I want it to be removed from the list so that only the
shifts that are unfilled are listed. Can this be done?
Click to expand...

If you can make the combo box's rowsource a query that returns only
unfilled shifts, then you can requery the combo box after the record is
saved that "fills" the shift.
 
I will try that...thanks

Dirk Goldgar said:
If you can make the combo box's rowsource a query that returns only
unfilled shifts, then you can requery the combo box after the record is
saved that "fills" the shift.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I'm a newbie, so I hope this explanation isn't too long for you. (I'm
writing it for those who may run across this in the future and know as
little as I did three weeks ago!)
I believe you can do this *fairly* easily in VBA. You basically need to
change the RowSource property in the AfterUpdate event of your combo box.

Let's call your combo box "cboShift". Its default RowSource is from a
table "tblWorkShifts", which has only one column showing all the work
shifts. Let's call this field "ShiftNumber".

The RowSource SQL statement for your combo box would then look something
like this:
"SELECT [ShiftNumber] FROM tblWorkShifts ORDER BY [ShiftNumber]"

In the AfterUpdate event of your combo box ("cboShift"):
==========
Private Sub cboShift_AfterUpdate()
'Declare two variables, which are explained below.
Dim strSQL As String
Dim strSQLWhere As String

'Create a constant containing the first part of your SQL string:
Const strSQLHead = "SELECT [ShiftNumber] FROM tblWorkShifts " & _
"WHERE "
'Note the space after the WHERE in the line above. You could also
'remove the & _ portion to keep the string on one line, but I can't
'do this using the forum's posting box.

'Define another constant containing the end of your SQL string:
Const strSQLOrderBy = " ORDER BY [ShiftNumber]"
'Note the space *before* the word ORDER in the line above. It is
'almost always best to put a space in the same part of an SQL
'string, such as *always* space at the end or *always* at the
'beginning; but in this code, it is easier to add it here.

'Now you must create the WHERE clause of your SQL string
'to exclude the choice you just made in your combo box.

strSQLWhere = "[ShiftNumber] <> " & Me.cboShift.Text & Chr(34)

'You probably will not need to include the ".Text" portion in the line
'above. The Text property captures what's currently showing in the
'combo box, which may *not* be the same as the stored value. However,
'since this code is in the AfterUpdate event, the value should be stored
'and you can omit ".Text" above. Also, note that Chr(34)
'represents the double-quote mark - search Access Help for character
'codes. You will have to add two more Chr(34) pieces if your field
'[ShiftNumber] is text and not numerical.

'Now, you create the full SQL string and make it the new RowSource
'for your combo box.

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
Me.cboShift.RowSource = strSQL
Me.Refresh
'You shouldn't need a Requery after changing a RowSource.

End Sub
==========

If you're having trouble getting this to work, it's probably an error in
the SQL string. There's an easy and excellent way of checking this out.
First, add the following two lines right *before* the
"Me.cboShift.RowSource = strSQL" line above:
Debug.Print strSQL
Stop
The first line prints your finished SQL string in the VBA Immediate Window
(and the second line stops code execution). When code execution stops, the
Visual Basic Editor should be showing. Open the Immediate
Window by pressing Ctrl+G or selecting the View menu, Immediate Window.
Select the entire string from your Immediate Window and copy it to the
Clipboard using Ctrl+C.
Next, start a new query in design view. From design view, select the View
menu, SQL View. Press Ctrl+V to paste the SQL string from the Immediate
Window. This will make it much easier to see if there are any missing
spaces or quotation marks. Try to switch the query to datasheet view. If
it works, your code should also work. If it doesn't work, you need to
identify the errors, trace them back in the code you created, and make the
appropriate changes. Remember that you cannot insert quotation marks
directly into VBA - use Chr(34) instead.

I learned how best to do this from JasonM of accessvba.com - he has an
excellent article at the URL below:
http://www.accessvba.com/showthread.php?s=&threadid=4895
(Other great people there are JustListenen and pbaldy.)

Again, I'm not sure how well this will work in changing the RowSource of a
combo box after selecting an item from the *same* combo box, but I'm pretty
sure it will work fine. You may also want to consider what happens if you
accidentally click on the wrong shift and need to change it; if you remove
the selected choice from the combo's RowSource, how can you change it back?
I might suggest using multiple combo boxes - one for Shift1, one for
Shift2, etc. The basic code remains the same, but if you would like help
on how to do this without repeating too much code, please let me know.

Thanks for your patience in reading all this, and I hope it helps!
 
Back
Top