Find Next

[bobsmyph]

I am trying write a program where i can have multiple way to search for a record (i.e. by ID #, Last Name, Address, etc) and want to be able to once i find the first one be able to click a 'Next' button and find the next one closest to the original search (and eventually go to previous too). i have included my finding code for searching by ID # and my find.
Find Next

Private Sub cmdNextWorkOrder_Click()
'MsgBox "in NEXT"
Clearscreen
If Not (MyRecSet.EOF = True) Then
'MsgBox "In if"
    MyRecSet.MoveNext
    Populate
    End If
End Sub

Find First

Dim prompt As String
   prompt$ = "Enter the full (complete) Work Order ID."
   ' get string to be used in the ID field search
   FindingWorkOrderNumber$ = InputBox(prompt$, "Work Order Search")
   'MsgBox (SearchStr)
   Dim MyRecSet As ADODB.Recordset
Dim CheckSet As ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = location of file
MyConn.Open
   Set MyRecSet = MyConn.Execute("SELECT [WorkOrder].WorkOrderNumber, [WorkOrder].ResidentsFirstName, [WorkOrder].ResidentsLastName," & _
"[WorkOrder].ServiceAddress ,[WorkOrder].Apartment, [WorkOrder].City, [WorkOrder].State, [WorkOrder].DateCompleted, [WorkOrder].Updated," & _
"[WorkOrder].Zip, [WorkOrder].PhoneNumber, [WorkOrder].ChargeTo, [WorkOrder].EmailAddress, [WorkOrder].OwnerCode, [WorkOrder].ServiceRequest, " & _
"[WorkOrder].OrderReceivedBy, [WorkOrder].OrderGivenTo,[WorkOrder].TypeOfUnit, " & _
"[WorkOrder].DateReceived,[WorkOrder].TimeReceived " & _
"From [WorkOrder] " & _
"Where [WorkOrder].WorkOrderNumber = " & FindingWorkOrderNumber & " Order by WorkOrderNumber")


holdworkorderid = MyRecSet.Fields("WorkOrderNumber")
holdtypeofunit = MyRecSet.Fields("TypeOfUnit")
holdresidentsfirstname = MyRecSet.Fields("ResidentsFirstName")
holdresidentslastname = MyRecSet.Fields("ResidentsLastName")
holdserviceaddress = MyRecSet.Fields("ServiceAddress")
holdapartment = MyRecSet.Fields("Apartment")
holdcity = MyRecSet.Fields("City")
holdstate = MyRecSet.Fields("State")
holdzipcode = MyRecSet.Fields("Zip")
holdphonenumber = MyRecSet.Fields("PhoneNumber")
holdchargeto = MyRecSet.Fields("ChargeTo")
holdownercode = MyRecSet.Fields("OwnerCode")
holdservicerequest2 = MyRecSet.Fields("ServiceRequest")
holdorderreceivedby = MyRecSet.Fields("OrderReceivedBy")
holdordergivento = MyRecSet.Fields("OrderGivenTo")
holdemail = MyRecSet.Fields("EmailAddress")
holddatecompletedmonth = Left(MyRecSet.Fields("DateCompleted"), 2)
holddatecompletedday = Right(Left(MyRecSet.Fields("DateCompleted"), 5), 2)
holddatecompletedyear = Right(MyRecSet.Fields("DateCompleted"), 4)
holddatereceived = MyRecSet.Fields("DateReceived")
holdtimereceived = MyRecSet.Fields("TimeReceived")
holdupdated = MyRecSet.Fields("Updated")
holdPNAC = Right(Left(MyRecSet.Fields("PhoneNumber"), 4), 3)
holdPNP = Right(Left(MyRecSet.Fields("PhoneNumber"), 9), 3)
holdPNS = Right(MyRecSet.Fields("PhoneNumber"), 4)

Populate
old:
End Sub

any and all help is greatly appreciated.

Thanks,

B.S.

[Chathura]

Welcome to VBF!
Your idea is clear but the senario is not clear. I guess your populate method assigns the values in variables to your objects.

You can filter your records using "where clouse" but you'll have to go back to the database.
There is another option, you can load all your records to combo boxes and change each combobox.listIndex value in combobox's Click or Change event. So when you want to move next, you only have to increase the value of listIndex property of each combo box.
Remember this method has certain advantages and disadvantages over the first option.

[bobsmyph]

what are the advantages and disadvantages?

[Chathura]

what are the advantages and disadvantages?

If you choose the first option, you'll have to go back to the db when you want to move to next or previous record. This is good because new updates will be make into count and a less memory is used to hold data. But will be bad, you have to go back to db. So, if you have to go to a db in a different pc, then it'll increase the network traffic.

The second option is good because the data is kept in the memory and there fore the data can be accessed quickly. But the data may not be updated. Higher amount of memory is used to hold data (therefore the applications' efficiency will be reduced).

May be some more things...!

[leinad31]

Break the searches into their own GUI (such as tab) to facilitate creation of parameter queries and development of appropriate result list.

eg. full match search as as with ID = value will either return a record or not, no sense using same GUI/search method for addresses which relies on partial match on one or more fields.

As to keeping it all in memory depends totally on what your data is and how much there is. Disregard such then app won't scale. Feed it several years worth of data and you can have your lunch while waiting for the app to load them into memory.

[bobsmyph]

Do you know where i could find some sample code of these ideas?

[bobsmyph]

i was able to get the searching by work order number part done and able to advance to the next record that way, but now i need to have a way to be able to search by last name. my problem here is that if i have multiple records with the same last name how am i going to advance them. does RecordSet keep multiple records that meet a certain criteria or does it only keep one. if some one could shead some light on this it would be helpful.