Combo Box Question

[Guest]

I have a combo box on my form (Form1) that lets users select an employee from
the pull down list. Sometimes employees will be put on the inactive list but
may have already been chosen on a record via Form1. What I want to know is if
it's possible to keep that inactive employee stored in this table but have it
removed from the combo box so they can't be selected again. I have another
form that I use to trigger this inactive state.

How can this work?

 

[Guest]

i'm no expert, but achieved something like this by redefining the combo's
rowsource in the on current event of the form so that it excluded inactive
employees if a new record, and included it if an existing record.

 

[Guest]

Can you show me an example of this?

i'm no expert, but achieved something like this by redefining the combo's
rowsource in the on current event of the form so that it excluded inactive
employees if a new record, and included it if an existing record.

 

[Guest]

Secret Squirrel,
Change the Row Source property of the combo box to a query that only returns
the active employees. You will also want to change the Limit To List property
to Yes which will deny users from manually typing in an inactive employee.
All of your existing records will remain unchanged.

Hope that helps
Dave

 

[Guest]

But what if a record exists with a now inactive employee? Will the table
still retain that employee even though it no longer shows up in the list?

 

[Guest]

Yes. The combo box is only showing records that it retrieves from the Row
Source property. It will not delete records if the Row Source results change.

Because the combo box is bound to a particular field in your underlying
table, it will still show old records, assuming, of course, that form1's
Record Source is different to that of the combo's Row Source.

 

[Guest]

in the on current event of form1 put something like this.


dim strSQL as String

strSQL="SELECT tblUsers.UserName FROM tblUsers"

if me.newrecord=true then
strSQL=strSQL & vbcrlf & "WHERE (((tblUsers.Inactive)=False))"
endif

strSQL=strSQL & ";"

me.combo.rowsource=strSQL
me.combo.requery


This will ensure that for previously entered records, all user names are
available to display,
but if a new record only those that are not inactive will appear.

HTH