Return multiple Values

[trk1616b]

I've got a Access form and i'm trying to write code to return a set of records based on an SQL query. The below code only returns 1 record. Anyone know how I can return multiple records? Thanks!

Dim db As Database
Dim rs As Recordset
Dim strSql As String
Dim TICKET_NUMBER As Long


strSql = "SELECT * "
strSql = strSql & "FROM [Service_Calls] "
strSql = strSql & "WHERE [ticket_no] = '"
strSql = strSql & TICKET_NO & "';"

Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL

If rs.RecordCount = 0 Then
MsgBox "There is no appointment for ticket number " & TICKET_NO & "."
Exit Sub
End If

 

[jrbarnett]

That will only return 1 record if there is only one record with the matching ticket number, which logically seems correct.

To return multiple records, adjust the data so that multiple records match the criteria entered (or change the SQL code to not be so restrictive on the data it returns), then use the various recordset functions to loop through the records in the recordset, whether there be 0, 1 or 1000.

John

 

[TheAceMan1]

How are ya trk1616b . . . . .

Your SQL looks just fine (have a look at the line continuation character in help to better view your SQL).

[blue]TICKET_NO[/blue] is all telling here. It appears you'll get multiple records if its value prescribes more than one appointment.

You tried a [blue]TICKET_NO[/blue] you know should return multiple records . . . Yes?

Whats is the source of [blue]TICKET_NO[/blue]?

See Ya! . . . . . .

 

[trk1616b]

Ticket_no is from a txt field on a form. I have 2 records in my database that have a ticket_number field of 123. When I enter in 123 for Ticket_No, the recordcount is only 1. That's why I'm confused.

 

[Eupher]

Okay, so you're using the recordset's RecordCount property to determine the number of records returned? In a DAO recordset, the value of RecordCount is not known until the recordset is fully populated, typically by using the MoveLast and MoveFirst methods. Until then, RecordCount will be 0 (if no records) or 1 (if one or more records).

Ken S.

 

[TheAceMan1]

trk1616b . . . . .

[blue]Eupher[/blue] is on target. Add the following line in [purple]purple[/purple] where you see it:

[blue]   [purple][b]rst.MoveLast[/b][/purple]

   If rs.RecordCount = 0 Then
      MsgBox "There is no appointment for ticket number " & TICKET_NO & "."
      Exit Sub
   End If[/blue]

See Ya! . . . . . .