searchy by Input Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all....I have a form where a list box holds 6 columns (Movie Name,
Customer Info and Movie Cass ID). At the below there is a button named search
where once clicked it will show input box and user will enter the cass ID.
Once entered, the system will move the focus to that cass id in the list box.
Can you help me how can I move the focus to listbox? I am able to get the
input box and capture the cass ID but not able to move the focus to list box
to that particular item.

Thanks for your help.
 
Ok..I found my solution but partial. Below is the code

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"


myvalue = InputBox(message, title)
Me.lblRentalList.Value = myvalue

End Sub

Now what I am looking for is lets say user by mistake enters something that
is not in the list box. Then I want system to display the message stating
invalid. How do I detect it is invalid? I am not getting this part.

Thanks.
 
Some subtle changes, but you will need to modify the dcount, to make sure
that the table and fields being checked, are correct... This option assumes,
that the Cass ID is stored in a table;

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"


myvalue = InputBox(message, title)

'if user clicks on cancel...
If Len(myvalue) = 0 Then Exit Sub

'If there is a CassID in the table, then this is a valid id...
If DCount("[CassID]","table name","[CassID]='" & myvalue & "'") > 0 Then
Me.lblRentalList.Value = myvalue
Else
MsgBox "Not a valid Cass ID"
End If
End Sub

Alternatively, you could iterate through your list box, to see if the value
entered is valid or not, instead of doing a dcount (the following example,
assumes your list box is called listListBox). NOTE: if you use this example,
you will need to ensure, that the bound column property, of your list box,
is the same column number, that your Cass ID is stored in. You have said,
that the list box contains 6 columns, so the bound column (which is the
ItemData property of the list box), must be the column number that the Cass
ID is in;

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"

Dim myLoop As Long
Dim blnFound As Boolean : blnFound = False

myvalue = InputBox(message, title)

'if user clicks on cancel...
If Len(myvalue) = 0 Then Exit Sub

'If there is a CassID in the list box, then this is a valid id...
For myLoop = 1 To listListBox.ListCount
If listListBox.ItemData(myLoop) = myvalue Then
blnFound = True
Exit For
End If
Next
If blnFound Then
Me.lblRentalList.Value = myvalue
Else
MsgBox "Not a valid Cass ID"
End If

End Sub
 
Mikey,

This problem is a perfect example of why Klatuu and I were
encouraging you to use a combobox to enter the Cass/DVD ID.
The combobox has a 'limit to list' property that will
generate exactly the message you are looking for if they
enter an ID that isn't in the list without writing a single
line of code.

Gary Miller
Sisters, OR



"learningMikey" <[email protected]>
wrote in message
 
Gary - Thanks for you input. Please don't think I am trying to ignore your
advice. I know this can be accomplished by combo box, but the end user
requests they see everything listed and then they do search to find the
appropriate cassette ID. And this can be only done either via list box or I
have a values displayed in the form.

Mikey
 
Ruskin - Thanks for your assistance. I wanted to ask you, the values in the
list box comes from query on form open event. I have 4 tables joined and I am
picking up only few fields from each to display. Will the dcount method work
here? My understanding was dcount only works with tables, however, the list
box is in form here. Please let me know and thanks for all your assistance.

Mikey

Ruskin Hardie said:
Some subtle changes, but you will need to modify the dcount, to make sure
that the table and fields being checked, are correct... This option assumes,
that the Cass ID is stored in a table;

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"


myvalue = InputBox(message, title)

'if user clicks on cancel...
If Len(myvalue) = 0 Then Exit Sub

'If there is a CassID in the table, then this is a valid id...
If DCount("[CassID]","table name","[CassID]='" & myvalue & "'") > 0 Then
Me.lblRentalList.Value = myvalue
Else
MsgBox "Not a valid Cass ID"
End If
End Sub

Alternatively, you could iterate through your list box, to see if the value
entered is valid or not, instead of doing a dcount (the following example,
assumes your list box is called listListBox). NOTE: if you use this example,
you will need to ensure, that the bound column property, of your list box,
is the same column number, that your Cass ID is stored in. You have said,
that the list box contains 6 columns, so the bound column (which is the
ItemData property of the list box), must be the column number that the Cass
ID is in;

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"

Dim myLoop As Long
Dim blnFound As Boolean : blnFound = False

myvalue = InputBox(message, title)

'if user clicks on cancel...
If Len(myvalue) = 0 Then Exit Sub

'If there is a CassID in the list box, then this is a valid id...
For myLoop = 1 To listListBox.ListCount
If listListBox.ItemData(myLoop) = myvalue Then
blnFound = True
Exit For
End If
Next
If blnFound Then
Me.lblRentalList.Value = myvalue
Else
MsgBox "Not a valid Cass ID"
End If

End Sub



learningMikey said:
Ok..I found my solution but partial. Below is the code

Private Sub btnSearch_Click()
Dim message, title, myvalue
message = "Please Enter The Cass/DVD ID"
title = "Mike's Video Rental"


myvalue = InputBox(message, title)
Me.lblRentalList.Value = myvalue

End Sub

Now what I am looking for is lets say user by mistake enters something that
is not in the list box. Then I want system to display the message stating
invalid. How do I detect it is invalid? I am not getting this part.

Thanks.
Click to expand...
Click to expand...
 
Back
Top