Single use of Combo box values

[Guest]

I have a continuous subform, the first field in which is "Rank." So if the
user thought this was the most valuable, the rank would be "1", second most
valuable would be "2", etc. Right now, the [Rank] field is a combo box with
a value list of 1-8 and Limit to List = Yes.

What I'd like to do is limit the use of each Rank to once. For example, if
the user used rank "1" in the first record, then in the next one the combo
box would only have options 2-8. Is this possible? Thanks in advance for
any replies.

 

[Guest]

Put this in your form's After Update event:

Dim strNewSource As String
Dim strLastUsed As String
Dim varList As Variant
Dim intX As Integer

strLastUsed = Me.Combo23
varList = Split(Me.Combo23.RowSource, ";")
For intX = 0 To UBound(varList)
If varList(intX) <> strLastUsed Then
strNewSource = strNewSource & varList(intX) & ";"
End If
Next intX
If strNewSource = "" Then
Me.Combo23 = strNewSource
Else
strNewSource = Left(strNewSource, Len(strNewSource) - 1)
Me.Combo23 = Left(strNewSource, 1)
End If
Me.Combo23.RowSource = strNewSource

 

[Guest]

Thanks for the response,

I entered that code, only changing [Combo23] to the name of my combo box,
[Rank]. When I did, I got an error "The value you entered isn't valid for
this field" any time I tried to leave a record after changing the value.
When I clicked on debug, the line:

Me.Rank = Left(strNewSource, 1)

(third from the bottom of what you posted) was highlighted in yellow. Any
ideas?

Put this in your form's After Update event:

Dim strNewSource As String
Dim strLastUsed As String
Dim varList As Variant
Dim intX As Integer

strLastUsed = Me.Combo23
varList = Split(Me.Combo23.RowSource, ";")
For intX = 0 To UBound(varList)
If varList(intX) <> strLastUsed Then
strNewSource = strNewSource & varList(intX) & ";"
End If
Next intX
If strNewSource = "" Then
Me.Combo23 = strNewSource
Else
strNewSource = Left(strNewSource, Len(strNewSource) - 1)
Me.Combo23 = Left(strNewSource, 1)
End If
Me.Combo23.RowSource = strNewSource

I have a continuous subform, the first field in which is "Rank." So if the
user thought this was the most valuable, the rank would be "1", second most
valuable would be "2", etc. Right now, the [Rank] field is a combo box with
a value list of 1-8 and Limit to List = Yes.

What I'd like to do is limit the use of each Rank to once. For example, if
the user used rank "1" in the first record, then in the next one the combo
box would only have options 2-8. Is this possible? Thanks in advance for
any replies.

 

[Guest]

Sorry, I don't really quite know off the top of my head. I only tested it my
poor old experimental form and it worked. The difference was I put it in the
After Update of the control. That would not work for you because at that
point it changes the value before the record would update, and give the wrong
value.

Is it possilbe your field is numeric and would like =
Cint(Left(strNewSource,1))?
Also try going in to debug mode, set a breakpoint on that line and see what
the value of strNewSource is.
Another (and maybe better - I just thought of it) is to change that line to:

Me.Rank.DefaultValue = Left(strNewSource, 1)
Notice there is another line that does the same thing (right after the IF)
Me.Rank = strNewSource - Change to
Me.Rank.DefaultValue = strNewSource

Thanks for the response,

I entered that code, only changing [Combo23] to the name of my combo box,
[Rank]. When I did, I got an error "The value you entered isn't valid for
this field" any time I tried to leave a record after changing the value.
When I clicked on debug, the line:

Me.Rank = Left(strNewSource, 1)

(third from the bottom of what you posted) was highlighted in yellow. Any
ideas?

Put this in your form's After Update event:

Dim strNewSource As String
Dim strLastUsed As String
Dim varList As Variant
Dim intX As Integer

strLastUsed = Me.Combo23
varList = Split(Me.Combo23.RowSource, ";")
For intX = 0 To UBound(varList)
If varList(intX) <> strLastUsed Then
strNewSource = strNewSource & varList(intX) & ";"
End If
Next intX
If strNewSource = "" Then
Me.Combo23 = strNewSource
Else
strNewSource = Left(strNewSource, Len(strNewSource) - 1)
Me.Combo23 = Left(strNewSource, 1)
End If
Me.Combo23.RowSource = strNewSource

I have a continuous subform, the first field in which is "Rank." So if the
user thought this was the most valuable, the rank would be "1", second most
valuable would be "2", etc. Right now, the [Rank] field is a combo box with
a value list of 1-8 and Limit to List = Yes.

What I'd like to do is limit the use of each Rank to once. For example, if
the user used rank "1" in the first record, then in the next one the combo
box would only have options 2-8. Is this possible? Thanks in advance for
any replies.