Find a Number and Date ...

[mrmunka]

I have create this user form that employees use to enter appointments
we have made. Currently the form has fields to enter values, with drop
down list etc... The next and final part to this project (thank god)
is a second form the user will use to search for an appointment they
made and then enter the remaining fields with the results and notes.

The form goes like this...

[Enter Client #:] [Text Box] [Search Button]
[Enter Appointment Date:]

[Showed List Box]
[Closed List Box]

Etc...

Here is the code I started with...

Private Sub Search_Click() 'Search Button
On Error GoTo errorHandler
Dim ClientNum As String
Dim AppDate As String
Dim startRow As Integer
Dim stopRow As Integer
ClientNum = ClientNum.text 'Enter Client # Text Box
If ClientNum = "" Then End
AppDate = AppDate.text 'Enter Appointment Date Text Box
If AppDate = "" Then End
ClientNum = Format(startDate, "#")
AppDate = Format(stopDate, "mm/dd/yy")
startRow = Worksheets("Appointment
Log").Columns("A").Find(ClientNum, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("Appointment Log").Columns("Z").Find(AppDate,
_
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Appointment Log").Range("C" & startRow & ":B" &
stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub

What I would like to change and do is to have a hidden label show up
if the search results found the values or reverse if nothing was
found, instead of the MsgBox. If the values entered were found,
highlight the row in the work sheet and allow the user to fill in the
fields below the search fields. I plan on removing the Report
worksheet at the bottom of the code posted above.

Here is a picture of the second form that has been created.
http://picasaweb.google.com/mrmunka/Excel

I've beat my head like crazy trying to get this all to work and I'm
finally at the end and would like some assistance from thoughs of you
out there with much more experience than myself. I greatly appreciate
any help provided. Thanks in advance.

 

[Guest]

I think your masjor problem is that text boxes returns text not numbers and
date. You need to convert the text

ClientNum = ClientNum.text 'Enter Client # Text Box
If ClientNum = "" Then End
AppDate = AppDate.text 'Enter Appointment Date Text Box
If AppDate = "" Then End

format converts numbers to text, you want text to number. Why is ClientNum
being set to a date????
from
ClientNum = Format(startDate, "#")
to
ClientNum = val(ClientNum)

from:
AppDate = Format(stopDate, "mm/dd/yy")
to:
AppDate = datevalue(stopDate)