Remove item from combobox based on table? 1

[ThinWhiteDude]

I've done a pretty thorough search of the archives and haven't found an answer to this problem:

Here's the setup.

I have a form with a couple of comboboxes:

cboDept gets its values from tblDepartment
cboEmSrc gets its values from tblEmissionsSource

As each dept has different emissions sources, the cboEmSrc list depends on which department is chosen. So, for example, the Alcohol Department may have these choices:

#1 Vat
#2 Vat
500 Distiller

and the Lab dept will have a different list.

What I would like is that when someone chooses Alcohol and then #2 Vat and makes some entries in other fields, then they choose 500 Distiller and make some entries for that, when they go to switch to the 500 distiller, I'd like the #2 Vat to not be on the list, as they've just done that one.

Is this possible with a cbo based on a table? I've done this before using a Value list and the AddItem and RemoveItem methods, but can’t get it to work for a cbo that uses a table.

Any help you can give me will be greatly appreciated

TWD

 

[VicRauch]

cboDept
cboEmSrc
txtField1
txtField2
I understand that after selecting #2 Vat from cboEmSrc and entering data into txtField1 and/or txtField2 [blue]Then saving the record[/blue] you do not want #2 Vat available on this new record because there is already an entry for #2 Vat.
I'm assuming this is either done on a daily basis, hourly basis, user basis, or some way to only allow one entry for a given choice from cboEmSrc. For this example, we'll allow only one entry per date.
So, this data has to be going into a table, we will call that table 'tblData' for this example.
The query that supplies the data for cboEmSrc currently is using the value from the cboDept to limit the choices. If you add to that, a Left-Outer join to your tblData, based on today's date, your list of avail data for cboEmSrc will only be the items not selected yet today.

http://www.vicrauch.com/test/db1.mdb

Go to that location and download (Save, don't Open) db1.mdb, then look at the only form and the two queries, plus the code behind the form. I think that will explain how to do this.

HTH,
Vic

 

[ThinWhiteDude]

Vic:

This looks beauteous. I will be applying this to my database tomorrow and will apprise you of the progress.

What an elegant solution. . . Thanks much!

TWD

 

[danvlas]

Base cboEmSrc on a query rather than the entire table:

Private Sub cboEmSrc_GotFocus()

Me.cboEmSrc.RowSource = "Me.cboSelect YourFieldList From tblEmissionsSource Where Department = " & Me.cboDept

End Sub

Whenever user selects the combo it will show the appropriate data.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html