It will be easier to accomplish what you want from a recordset rather
than a Bound Query. Since I have never seen the DB you are referencing I
have made some assumptions about the field names below. I would try this
first with a separate listbox and a Method driven by a pushbutton before
attempting it with your listbox bound to the query because you will have
to break that binding fist if you use the existing listbox.
Dim DB As REALSQLdatabase
Dim RSD As FolderItem
Dim rs As RecordSet
Dim PatientName As String
Dim i,n,colPtr As Integer
DB = New REALSQLdatabase
DB.DatabaseFile =
RSD = GetFolderItem("DatabaseName") // substitute your database file
name
If RSD <> Nil Then
DB.DatabaseFile = RSD
If DB.Connect() Then
rs = DB.SQLSelect("SELECT PatientNumber, PatientLastName,
PatientFirstName, PatientMiddleName, PatientRevNo, PatientRevDate FROM
Patients")
If rs <> NIL Then
n = rs.FieldCount
ListBox1.deleteAllRows
ListBox1.ColumnCount = n - 2 // subtract 2 since we combine 3
records for the name
While Not rs.EOF
ListBox1.AddRow rs.IdxField(1).Value // column 0 =
PatientNumber
// format the patient name
PatientName = rs.IdxField(2).Value + ", " +
rs.IdxField(3).Value + rs.IdxField(4).Value
ListBox1.cell(List.lastIndex,1) = PatientName // column 1 is
the patient name
colPtr = 2 // we have used 0 & 1 so the next column is 2
For i = 5 to n
ListBox1.cell(ListBox1.LastIndex,colPtr) =
rs.IdxField(i).Value
colPtr = lbPtr + 1 // add 1 for the next column
Next
rs.MoveNext
wend
rs.Close
Else
// Database ErrorAlert Message
MsgBox DB.ErrorMessage
End If
Else
// Database ErrorAlert Message
MsgBox "Cannot Contect to the Database"
End If
Else
MsgBox "Cannot find the Database "
End If
DB.Close
HTH
Tom
> Ok, I got the 2005 DB Example working as I found out I had
> one letter missing in the SQLQuery.
>
> Here is what I had first: SELECT PatientNumber,
> PatienLastName, PatientRevNo, PatientRevDate FROM Patients.
>
> It should of been this: SELECT PatientNumber,
> PatientLastName, PatientRevNo, PatientRevDate FROM Patients.
> As you can see I forgot the "t" in the PatientLastName.
> After that, it works GREAT! Wow, no error came up at all so
> it was a major pain to figure it out.
>
> Ok, now I would like to have the Patient Name be shown in the
> ListBox like
> this: Bevar, Jonathon M.
>
> NOTE: I have the form to enter the First Name, Middle Name
> and Last Name seperately, but I want the Listbox to combine
> all 3 into one entry. Is this possible in the SQLQuery function?
>
> Currently it is just the Last Name shown in the ListBox. How
> or what do I need to do to make it come out like the above as
> for: LastName comma then space then FirstName space then MiddleName?
>
> The ListBox has a heading of: Patient Name. Can I do what I
> want without breaking up the ListBox column. I want this way
> as some names are long or short and it will it look more PRO
> then having a bunch of columns.
>
> Thanks
>
> Jonathon
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
