ComboBox: Display value not in query

[bentheimmigrant]

I have a ComboBox where user is selected. However, this is filtered down to only include active members of staff.

What I'm trying to do is set the Row Source for this ComboBox to display the staff name, even if they're not active.

So, I currently have:
SELECT ID, FullName FROM tblUsers WHERE tblUsers.Active = Yes;

But I'm not quite sure how to also display the inactive user who was already chosen (i.e. before they became inactive). Any suggestions?

The form is Bound to a table, tblDocs, which has the field UserID (which is obviously the Control Source for the ComboBox).

TIA.

 

[MarkK]

You can't do both. Anything that is not in the list cannot be visible in the combo.

One work-around I've used before is prefix inactive names with something that sorts that active ones to the top of the list, like imagine a RowSource like...

SELECT UserID, IFF(Active, " ", "x-") & Fullname FROM tblUser ORDER BY IFF(Active, " ", "x-") & Fullname

So see how that would sort all the active users to the top of the combo for easy selection, and all the inactive users are still present in the list, but prefixed with the "x-"

 

[sneuberg]

You can't do both in the same control but as demonstrated in the attached database you can create a subform that has a record source like the combo box record source except without the restriction. You might be able to put them on top of other each other and switch their visibility in the form's current event.depending on the situation

 

[moke123]

if you are trying to re-use a form for both new data entry(only showing active users) and for existing records(showing both active and inactive) you can set the recordsource for the combo in the current event.

along the lines of...

if me.newrecord then
me.Mycombo.recordsource = "SELECT ID, FullName FROM tblUsers WHERE tblUsers.Active = Yes;"
else
me.Mycombo.recordsource = "SELECT ID, FullName FROM tblUsers ;"
end if

 

[bentheimmigrant]

Thanks for the input. It got me thinking...
Tried:
SELECT ID, FullName FROM tblUsers WHERE (tblUsers.Active = Yes OR tblUsers.ID = [Forms]![DocFrm]![UserID]);

But for some reason it would pop up a box asking for the user id.

So I wrote an OnCurrent sub to capture the Form's user ID, and write it into the query in place of the Form field reference above:

vbUser = nz(Me.UserID, 0)
Me.ComboBox.RowSource = "SELECT ID, FullName FROM tblUsers WHERE (tblUsers.Active = Yes OR tblUsers.ID = " & vbUser & ");"

And that seems to work. So... yay me!

 

[Gasman]

You need to concatenate the forms value with the sql string when using VBA. That is why it asked for the [Forms]![DocFrm]![UserID] as it has no idea what that would be.

strSQL = "SELECT ID, FullName FROM tblUsers WHERE (tblUsers.Active = Yes OR tblUsers.ID = " & [Forms]![DocFrm]![UserID])

 

[sneuberg]

You need to concatenate the forms value with the sql string when using VBA. That is why it asked for the [Forms]![DocFrm]![UserID] as it has no idea what that would be.

strSQL = "SELECT ID, FullName FROM tblUsers WHERE (tblUsers.Active = Yes OR tblUsers.ID = " & [Forms]![DocFrm]![UserID])

Yes but I think bentheimmigrant was putting the form reference directly in the row source query (no VBA). I think that should have worked.

 

[Gasman]

Yes but I think bentheimmigrant was putting the form reference directly in the row source query (no VBA). I think that should have worked.

Oh, I thought it was just in VBA, my mistake.