combo-make some choices invisible

[Lungta]

Hi, thought I had seen something similar several months back but can't find
anything on it today.

We have a few items in a combo box that users should not use anymore for new
records, but some old records still need to display them. The vehicle types
have been phased out and marked as "inactive" on the table.

Does anyone know how to set up the row source for a combo box so that users
cannot see these items on new records, but can still see them displayed on
old records? I can only enable them totally or disable them totally so far.

row source now:
SELECT tblVehType.VehTypeID, tblVehType.VehType, tblVehType.VehTypeStatus
FROM tblVehType;

(VehTypeStatus is "Active"/"Inactive")

Any pointers would be much appreciated.

Thanks,

 

[Mr B]

Add a Where clause to your row source.

like:
SELECT tblVehType.VehTypeID, tblVehType.VehType, tblVehType.VehTypeStatus
FROM tblVehType WHERE VehTypeStatus = "Active";

This will cause only the records flagged as "Active" to be displayed in the
combo box.

 

[Lungta]

That's the problem - either they are always invisible (like in your solution)
or are always visible.

thanks anyways.

Any other suggestions?

 

[Mr B]

If you do not want the inactive recrods to show in the combo box, then the
solution is as I suggested. However, the previous records where a selection
has been made will not be affected unless the user need to make a different
choice, in which case they would not be able to choose an inactive option.

If this is what you want, you might want to check to see if there is an
existing value selected in the combo box and if so, lock it to prevent any
other selection from being made.

If none of this is what you want to do, you might have to find explain a
little more in detail exactly what you really want to do. You relly can't
have it both ways. :>)

It is possible to programatically change the row source for your combo box
on the fly, based on some other criteria. Would something like that be
appropriate?

 

[Lungta]

When I try your suggestion, the "inactive" choices are totally filtered out
of the picture and so become invisible all the time (existing records and new
records). If this is a freak thing, please let me know, but doesn't look like
it would be visible ever.

We need the row source to show all choices on existing records and only
"active" choices on new records. I believe it can be done 2 ways: with
criteria ("if VehTypeID>1000 then...") or with "if me.newrecord...". I just
don't know how to do it or which way is best.

If anyone can suggest a good way to do this it would be super.

Thanks,

 

[Mr B]

Lungta,

Give this a try:

Place the following code in the "Current" event of your form:

Dim strSql As String

If Me.NewRecord = True Then
strSql = "SELECT tblVehType.VehTypeID, tblVehType.VehType,
tblVehType.VehTypeStatus " _
& "FROM tblVehType WHERE VehTypeStatus = ""Active"";"
Else
strSql = "SELECT tblVehType.VehTypeID, tblVehType.VehType,
tblVehType.VehTypeStatus " _
& "FROM tblVehType;"
End If
With Me.NameOfComboBox
.RowSource = strSql
.Requery
End With

You will need to change the "NameOfComboBox" to the actual name of your
combo box.

What this will do is assign a different row source for your combo box when
you are on a new record.

 

[Lungta]

Hi, Mr. B,

Your solution is great - thanks! The word wrap made some weird quotes, I
think, but this works:

Private Sub Form_Current()
Dim strSql As String

If Me.NewRecord = True Then
strSql = "SELECT tblVehType.VehTypeID, tblVehType.VehType,
tblVehType.VehTypeStatus FROM tblVehType WHERE VehTypeStatus = ""Active"";"
Me.Combo12.RowSource = strSql
Me.Refresh
Else
strSql = "SELECT tblVehType.VehTypeID, tblVehType.VehType,
tblVehType.VehTypeStatus FROM tblVehType;"
Me.Combo12.RowSource = strSql
Me.Refresh

End If

' With Me.Combo12
' .RowSource = strSql
' .Requery
' End With

Existing records show everything in the combo, new records only show
"active" choices available.
Thank you very much for your help and patience. This will get us out of a
jam.

 

[Mr B]

Lungta,

Seems that you got it to work, but I just wanted to let you know that what I
was attempting to do was to just set the value of the "strSql" variable and
then use the With statement to populate the combo box. Word wraping in this
newsgroup can be misleading.

Good luck with your project.

 

[Lungta]

Hi,

When the quotes were giving problems, that was the 1st thing I tried
changing, so used something else instead. But you're right, I just tried
using your With method again and it works fine.

Thanks again for the help!