How to prevent listbox from being populated if certain criteria is met?

[Rino]

Greetings,

I have a specific situation here. What I'm trying to achieve is to
prevent a listbox from being populated from values provoded in
textboxes.

This is the scenario:

User selcets item from listbox1 and pass it to corresponding textboxes
by clicking on the command button. User than pass values from textboxes
to another Listbox2.
Now, what I'm trying to achieve is to prevent the listbox2 from adding
a new record if PartNumber which is the criteria already exists in the
listbox2

Visually:

1. ListBox2 with an existing record in it:
1, partNumDEC-771, Description1, $698

2. User wants to add another record with different part number. No
problem he adds new record. Now listbox2 looks like this:

1, partNumDEC-771, fdjf jsfskf fjsfjfj, $XXX
4, partNumADV-118, blablaldfj kkfkl, $XXX

3. If user selects item from listbox1 that has the same part number
that already exists in the listbox2 and try to populate listbox2 with
new item, he should than be prompted with the worning message that he
can not add new record that has the same part num.

Please help me resolve this problem. Thanks in advance

This is the code I already have that allows the user to add new records
to listbox2 from values in textboxes:


Once again txtPartNo which holds Part numbers should be the criteria

'----------------------------------------------------------------------------------------------------------------------------
Private Sub cmdAddToQuote_Click()

If txtPartNo = Me.lstResults2.Column(1) Then

MsgBox ("blablabla")

Else
With txtQuantity
.SetFocus
If Trim(.Text) <> "" Then
lstResults2.RowSource = lstResults2.RowSource & .Text
lstResults2.Requery
Else

End If
End With

With txtPartNo
.SetFocus
If Trim(.Text) <> "" Then
lstResults2.RowSource = lstResults2.RowSource & ";" &
..Text
lstResults2.Requery
Else

End If
End With

With txtDesc
.SetFocus
If Trim(.Text) <> "" Then
lstResults2.RowSource = lstResults2.RowSource & ";" &
..Text
lstResults2.Requery
Else

End If
End With

With txtPrice
.SetFocus
If Trim(.Text) <> "" Then
lstResults2.RowSource = lstResults2.RowSource & ";" &
..Text
lstResults2.Requery
Else

End If
End With

Dim dTotal As Integer
dTotal = CDec(txtPrice.Value * txtQuantity.Value)

lstResults2.RowSource = lstResults2.RowSource & ";" & dTotal &
";"
lstResults2.Requery

Me.txtTotal2 = dTotal

Dim Total As Integer
Dim I As Integer

If Me.lstResults2.ListCount > 0 Then
Total = 0
For I = 0 To Me.lstResults2.ListCount - 1
Total = Total + Me.lstResults2.Column(4, I)
Next I
End If
Me.txtTotal = Total

Dim stemp As String
stemp = Left(FirstName, 2)
stemp = stemp & Left(LastName, 2)
Quote_ID = UCase(stemp) & Format(Date, "mmddyy") &
Format(Now, "hhnnss")
Me.txtQuoteID = Quote_ID

Dim stemp2 As String
Dim QuoteItem_ID As String
stemp2 = Left(txtPartNo, 3)
QuoteItem_ID = UCase(stemp2) & Format(Date, "mmddyy") &
Format(Now, "hhnnss")
Me.txtQuoteItemID = QuoteItem_ID

cmdSelect.Enabled = False
End If
End Sub

'------------------------------------------------------------------------------------------------------------------------

-Rino

 

[Arvin Meyer [MVP]]

Why don't you delete the item from Listbox1 if it is moved to Listbox2? That
way it will be impossible to populate the same record more than once.

Have a look at the Senate listbox demo on the Access MVP website:

http://www.accessmvp.com/Arvin/Listbox.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Rino]

Arvin,

I appriciate your response.
However, I appologize for not being clear enough. The situation is that
listbox1 porovides choices for the user like combo box for example and
therefore for the future references and use I cannot allow the user to
be able to delete the choices that are offered to him/her.

The reason I need user not to be able to select the same option twice
is simply because it doesn't make sense since the user after selecting
the option can also pick number for quantity.

Cheers

-Rino

 

[Arvin Meyer [MVP]]

If you look at the example you will see that nothing is deleted from the
data, just from the recordset that fills the listbox. It might be more
accurate to say that when the listbox is requeried, the item is excluded
from the data returned.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Rino]

Arvin,

I see your point now. Somehow I haven't see the link you provided to
me. I have to say I found this approach very useful and I am going to
implement it now.

I would like to thank you for guiding me to the right direction. It is
really appriciated.

Have a good one

-Rino