VB System Database Error

[denvermuseka]

this is the error i get when i want to search for info in my system:
run-time error '-2147217904 (80040e10)':
no value given for one or more required parameters.
this is the code..... please help.....
[rs.Open sql, conn 'this is where the error is!!!!
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
lstItem.SubItem s(6) = rs(5).Value
lstItem.SubItem s(7) = rs(6).Value
If IsNull(rs(7).Va lue) Then
lstItem.SubItem s(8) = 0
Else
lstItem.SubItem s(8) = Val(rs(7).Value ) - Val(BooksTotalO ut(rs(0).Value) )
End If
If lstItem.SubItem s(8) = 0 Then
lstItem.SubItem s(9) = "Not Available "
Else
lstItem.SubItem s(9) = "Available"
End If
lstItem.SubItem s(10) = Val(BooksTotalO ut(rs(0).Value) )
lstItem.SubItem s(11) = Val(lstItem.Sub Items(8)) + Val(lstItem.Sub Items(10))
lstItem.SubItem s(12) = rs(8).Value
lstItem.SubItem s(13) = rs(9).Value
lstItem.SubItem s(14) = rs(10).Value
lstItem.SubItem s(15) = rs(11).Value
lstItem.SubItem s(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListIt ems.Count = 0 Then MsgBox "No data found", vbInformation, ""
' Set rs = Nothing
'Set rs = Nothing
End Sub]

[Rabbit]

We need to see the sql

[denvermuseka]

Public rs As New ADODB.Recordset
Public conn As New ADODB.Connectio n
Public sql As String
Public ConString As String
Public CurrentUser As String
Public LoginSuccess As Boolean
Public UserTitle As String
Public TempBorrowerID As String
Public TempBorrowerNam e As String
Public TempBookID As String
Public TempBookName As String
Public TempBookCopy As String
Public TempCopyBorrow As String
Public BorrowDate As String
Public DueDate As String
Public mTransID As String
Public TempContact As String
Public userlog As Integer
Public rAdd As Boolean, rDelete As Boolean, rUpdate As Boolean, rPrint As Boolean

Sub Main()
'ConString = "Provider=Micro soft.Jet.OLEDB. 4.0;User ID=admin;Data Source=" & App.Path & "\Data.mdb; Jet OLEDB:Database Password="
ConString = "Provider=Micro soft.Jet.OLEDB. 4.0;User ID=admin;Data Source=" & App.Path & "\xDatax.librar y;Jet OLEDB:Database Password=librar y"
conn.Open ConString
With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimisti c
End With
SplashFrm.Show
'LoginFrm.Show
'MainFrm.Show
'AccountsConfig Frm.Show
End Sub

[Rabbit]

I still don't see the sql.

[denvermuseka]

sqls:
Dim mrs As New ADODB.Recordset
Dim msql As String
Sub AddBooks(BookID As String, ISBN As String, Title As String, Author As String, Category As String, Publisher As String, Date_Acquired As String, NoOfCopies As String, bSection As String, CreatedBy As String, DateAdded As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BooksInfo Where bookid='" & BookID & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate book id found.Please enter another book id.", vbInformation, ""
Exit Sub
End If
With rs
.AddNew
!BookID = BookID
!ISBN = ISBN
!Title = StrConv(Title, vbProperCase)
!Author = StrConv(Author, vbProperCase)
!Category = Category
!Publisher = StrConv(Publish er, vbProperCase)
!Date_Acquired = Date_Acquired
!NoOfCopies = NoOfCopies
!bSection = bSection
!CreatedBy = CreatedBy
!DateAdded = DateAdded
!DateModify = DateModify
!LastModifyBy = LastModifyBy
.Update
End With
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub UpdateBooks(Boo kID As String, ISBN As String, Title As String, Author As String, Category As String, Publisher As String, Date_Acquired As String, NoOfCopies As String, bSection As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Update BooksInfo Set BooksInfo.Booki d='" & BookID & "',BooksInfo.is bn='" & ISBN & "',BooksInfo.ti tle='" & StrConv(Title, vbProperCase) & "',BooksInfo.au thor='" & StrConv(Author, vbProperCase) & "',BooksInfo.Ca tegory='" & Category & "',BooksInfo.pu blisher='" & StrConv(Publish er, vbProperCase) & "',BooksInfo.Da te_Acquired='" & Date_Acquired & "',BooksInfo.no ofcopies='" & NoOfCopies & "',BooksInfo.bS ection='" & bSection & "',BooksInfo.Da teModify='" & DateModify & "',BooksInfo.La stModifyBy='" & LastModifyBy & "' Where BooksInfo.BookI D='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub DeleteBooks(Boo kID As String)
If rs.State = adStateOpen Then rs.Close
sql = "Delete * From BooksInfo Where bookid='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub BooksSearch(lst Books As ListView, SearchFor As String, SearchIn As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If SearchIn = "Book ID" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.BookI D like '" & SearchFor & "%' " & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.BookI D"
ElseIf SearchIn = "ISBN" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.ISBN like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.ISBN"
ElseIf SearchIn = "Book Title" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Title like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Title "
ElseIf SearchIn = "Author" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Autho r like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Autho r"
ElseIf SearchIn = "Category" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Categ ory like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Categ ory"
ElseIf SearchIn = "Publisher" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Publi sher like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Publi sher"
Else
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion,BooksInfo.C reatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" From BooksInfo" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" ORDER BY BooksInfo.BookI D;"

End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
lstItem.SubItem s(6) = rs(5).Value
lstItem.SubItem s(7) = rs(6).Value
If IsNull(rs(7).Va lue) Then
lstItem.SubItem s(8) = 0
Else
lstItem.SubItem s(8) = Val(rs(7).Value ) - Val(BooksTotalO ut(rs(0).Value) )
End If
If lstItem.SubItem s(8) = 0 Then
lstItem.SubItem s(9) = "Not Available "
Else
lstItem.SubItem s(9) = "Available"
End If
lstItem.SubItem s(10) = Val(BooksTotalO ut(rs(0).Value) )
lstItem.SubItem s(11) = Val(lstItem.Sub Items(8)) + Val(lstItem.Sub Items(10))
lstItem.SubItem s(12) = rs(8).Value
lstItem.SubItem s(13) = rs(9).Value
lstItem.SubItem s(14) = rs(10).Value
lstItem.SubItem s(15) = rs(11).Value
lstItem.SubItem s(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListIt ems.Count = 0 Then MsgBox "No data found", vbInformation, ""
' Set rs = Nothing
'Set rs = Nothing
End Sub
Function GetBookID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT Max(BooksInfo.B ookID) AS MaxOfBookID" & _
" FROM BooksInfo"
rs.Open sql, conn
If IsNull(rs(0).Va lue) Then
'GetBookID = "Book ID - " & Format(1, "00000")
GetBookID = Format(1, "00000")
Else
'GetBookID = "Book ID - " & Format(rs(0).Va lue, "00000")
GetBookID = Format(rs(0).Va lue + 1, "00000")
End If
End Function
Sub cboCategory(cbo Cat As ComboBox)
Dim a As Integer
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BookCategory Order by Catname"
rs.Open sql, conn
cboCat.Clear
cboCat.AddItem ""
cboCat.ItemData (0) = 0
Do While Not rs.EOF
cboCat.AddItem rs!Catname
cboCat.ItemData (a) = rs(0).Value
rs.MoveNext
Loop
End Sub
Sub AddCat(CatID As String, Catname As String, CreatedBy As String, DateAdded As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BookCategory Where Catname='" & Catname & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate name found. Please enter another name", vbInformation, ""
Exit Sub
End If
With rs
.AddNew
!CatID = CatID
!Catname = StrConv(Catname , vbProperCase)
!CreatedBy = CreatedBy
!DateAdded = DateAdded
!DateModify = DateModify
!LastModifyBy = LastModifyBy
.Update
End With
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub UpdateCat(CatID As String, Catname As String, OldCat As String, DateModify As String, LastModifyBy As String)
If CategoryInUse(O ldCat) = False Then
If rs.State = adStateOpen Then rs.Close
sql = "Update bookcategory set bookcategory.ca tid='" & CatID & "',bookcategory .catname='" & StrConv(Catname , vbProperCase) & "',bookcategory .DateModify='" & DateModify & "',bookcategory .LastModifyBy=' " & LastModifyBy & "' Where bookcategory.ca tname='" & OldCat & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
Else
MsgBox "Can't Update Record. Category in use!", vbExclamation, "Item can't be deleted " 'End If
End If
End Sub
Public Sub DeleteCat(Catna me As String)
If CategoryInUse(C atname) = False Then
If rs.State = adStateOpen Then rs.Close
sql = " Delete * From BookCategory Where Catname='" & Catname & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
Else
MsgBox "Can't Delete Record. Category in use!", vbExclamation, "Item can't be deleted " 'End If
End If
End Sub
Public Sub DisplayCategory (lstCat As ListView)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
sql = "select * from bookcategory Order by catid"
rs.Open sql, conn
lstCat.ListItem s.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstCat.ListItem s.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
rs.MoveNext
Loop
End Sub
Function GetCatID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT max(BookCategor y.CatID) AS MaxOfCatID " & _
" FROM BookCategory;"
rs.Open sql, conn
If IsNull(rs(0).Va lue) Then
GetCatID = Format(1, "00000")
Else
GetCatID = Format(rs(0).Va lue + 1, "00000")
End If
End Function
Function CategoryInUse(C atname As String) As Boolean
Dim rst As New ADODB.Recordset
rst.Open " SELECT BooksInfo.Categ ory" & _
" FROM BooksInfo" & _
" WHERE (((BooksInfo.Ca tegory)='" & Catname & "'));", conn
If Not rst.EOF And Not rst.BOF Then
'If Catname = rst.Fields(0).V alue Then
CategoryInUse = True
Else
CategoryInUse = False
End If

'Set rst = Nothing
End Function
Function BooksTotalOut(B ookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(BorrowedBoo ks.CopiesBorrow ) AS SumOfCopiesBorr ow" & _
" FROM BorrowedBooks" & _
" Where (((BorrowedBook s.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).V alue) Then
BooksTotalOut = rst(0).Value
Else
BooksTotalOut = 0
End If
BooksTotalOut = Val(BooksTotalO ut) + Val(BooksTempTo talOut(BookID))
Set rst = Nothing
End Function
Function BooksTempTotalO ut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(TempBorrowe dBooks.CopiesBo rrow) AS SumOfCopiesBorr ow" & _
" FROM TempBorrowedBoo ks" & _
" Where (((TempBorrowed Books.BookID)=' " & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).V alue) Then
BooksTempTotalO ut = rst(0).Value
Else
BooksTempTotalO utt = 0
End If
Set rst = Nothing
End Function
Sub InventoryBooks( lstBooks As ListView, mDate As Date, BookType As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If BookType = "All" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "#" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
ElseIf BookType = "Circulatio n" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "# and booksinfo.bSect ion='Circulatio n'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
ElseIf BookType = "Reserved" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "# and booksinfo.bSect ion='Reserved'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
If IsNull(rs(5).Va lue) Then
lstItem.SubItem s(6) = 0
Else
lstItem.SubItem s(6) = Val(rs(5).Value ) - Val(BooksTotalO ut(rs(0).Value) )
End If
lstItem.SubItem s(7) = Val(BooksTotalO ut(rs(0).Value) )
lstItem.SubItem s(8) = Val(lstItem.Sub Items(6)) + Val(lstItem.Sub Items(7))
lstItem.SubItem s(9) = rs(6).Value
rs.MoveNext
Loop
End Sub
Public Sub SaveTempInvento ry(lstData As ListView)
Dim a As Integer
If rs.State = adStateOpen Then rs.Close
rs.Open " Delete * From TempInventory", conn
If rs.State = adStateOpen Then rs.Close
sql = " Select * From TempInventory"
rs.Open sql, conn
If lstData.ListIte ms.Count > 0 Then
For a = 1 To lstData.ListIte ms.Count
With rs
.AddNew
!No = lstData.ListIte ms(a).Text
!BookID = lstData.ListIte ms(a).SubItems( 1)
!ISBN = lstData.ListIte ms(a).SubItems( 2)
!Title = lstData.ListIte ms(a).SubItems( 3)
!Author = lstData.ListIte ms(a).SubItems( 4)
!Date_Acquired = lstData.ListIte ms(a).SubItems( 5)
!CopiesOnHand = lstData.ListIte ms(a).SubItems( 6)
!CopiesBorrow = lstData.ListIte ms(a).SubItems( 7)
!TotalCopies = lstData.ListIte ms(a).SubItems( 8)
!Section = lstData.ListIte ms(a).SubItems( 9)
.Update
End With
Next
End If
If rs.State = adStateOpen Then rs.Close
End Sub
Sub DisplayBookCard (lstBooks As ListView, BookID As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If BookID <> "" Then
sql = " SELECT ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" From ReturnedBooks" & _
" Where ReturnedBooks.B ookID = '" & BookID & "'" & _
" GROUP BY ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" ORDER BY ReturnedBooks.T ransactionID, ReturnedBooks.D ate_Borrowed;"

Else
sql = " SELECT ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" From ReturnedBooks" & _
" GROUP BY ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" ORDER BY ReturnedBooks.T ransactionID, ReturnedBooks.D ate_Borrowed;"

End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
lstItem.SubItem s(6) = rs(5).Value
lstItem.SubItem s(7) = rs(6).Value
lstItem.SubItem s(8) = rs(7).Value
lstItem.SubItem s(9) = rs(8).Value
rs.MoveNext
Loop
End Sub

[denvermuseka]

sqls:
Dim mrs As New ADODB.Recordset
Dim msql As String
Sub AddBooks(BookID As String, ISBN As String, Title As String, Author As String, Category As String, Publisher As String, Date_Acquired As String, NoOfCopies As String, bSection As String, CreatedBy As String, DateAdded As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BooksInfo Where bookid='" & BookID & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate book id found.Please enter another book id.", vbInformation, ""
Exit Sub
End If
With rs
.AddNew
!BookID = BookID
!ISBN = ISBN
!Title = StrConv(Title, vbProperCase)
!Author = StrConv(Author, vbProperCase)
!Category = Category
!Publisher = StrConv(Publish er, vbProperCase)
!Date_Acquired = Date_Acquired
!NoOfCopies = NoOfCopies
!bSection = bSection
!CreatedBy = CreatedBy
!DateAdded = DateAdded
!DateModify = DateModify
!LastModifyBy = LastModifyBy
.Update
End With
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub UpdateBooks(Boo kID As String, ISBN As String, Title As String, Author As String, Category As String, Publisher As String, Date_Acquired As String, NoOfCopies As String, bSection As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Update BooksInfo Set BooksInfo.Booki d='" & BookID & "',BooksInfo.is bn='" & ISBN & "',BooksInfo.ti tle='" & StrConv(Title, vbProperCase) & "',BooksInfo.au thor='" & StrConv(Author, vbProperCase) & "',BooksInfo.Ca tegory='" & Category & "',BooksInfo.pu blisher='" & StrConv(Publish er, vbProperCase) & "',BooksInfo.Da te_Acquired='" & Date_Acquired & "',BooksInfo.no ofcopies='" & NoOfCopies & "',BooksInfo.bS ection='" & bSection & "',BooksInfo.Da teModify='" & DateModify & "',BooksInfo.La stModifyBy='" & LastModifyBy & "' Where BooksInfo.BookI D='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub DeleteBooks(Boo kID As String)
If rs.State = adStateOpen Then rs.Close
sql = "Delete * From BooksInfo Where bookid='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub BooksSearch(lst Books As ListView, SearchFor As String, SearchIn As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If SearchIn = "Book ID" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.BookI D like '" & SearchFor & "%' " & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.BookI D"
ElseIf SearchIn = "ISBN" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.ISBN like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.ISBN"
ElseIf SearchIn = "Book Title" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Title like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Title "
ElseIf SearchIn = "Author" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Autho r like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Autho r"
ElseIf SearchIn = "Category" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Categ ory like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Categ ory"
ElseIf SearchIn = "Publisher" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ionBooksInfo.Cr eatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Publi sher like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" Order by BooksInfo.Publi sher"
Else
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion,BooksInfo.C reatedBy, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" From BooksInfo" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Categ ory, BooksInfo.Publi sher, BooksInfo.Date_ Acquired,BooksI nfo.bSection,Bo oksInfo.Created By, BooksInfo.DateA dded, BooksInfo.DateM odify, BooksInfo.LastM odifyBy" & _
" ORDER BY BooksInfo.BookI D;"

End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
lstItem.SubItem s(6) = rs(5).Value
lstItem.SubItem s(7) = rs(6).Value
If IsNull(rs(7).Va lue) Then
lstItem.SubItem s(8) = 0
Else
lstItem.SubItem s(8) = Val(rs(7).Value ) - Val(BooksTotalO ut(rs(0).Value) )
End If
If lstItem.SubItem s(8) = 0 Then
lstItem.SubItem s(9) = "Not Available "
Else
lstItem.SubItem s(9) = "Available"
End If
lstItem.SubItem s(10) = Val(BooksTotalO ut(rs(0).Value) )
lstItem.SubItem s(11) = Val(lstItem.Sub Items(8)) + Val(lstItem.Sub Items(10))
lstItem.SubItem s(12) = rs(8).Value
lstItem.SubItem s(13) = rs(9).Value
lstItem.SubItem s(14) = rs(10).Value
lstItem.SubItem s(15) = rs(11).Value
lstItem.SubItem s(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListIt ems.Count = 0 Then MsgBox "No data found", vbInformation, ""
' Set rs = Nothing
'Set rs = Nothing
End Sub
Function GetBookID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT Max(BooksInfo.B ookID) AS MaxOfBookID" & _
" FROM BooksInfo"
rs.Open sql, conn
If IsNull(rs(0).Va lue) Then
'GetBookID = "Book ID - " & Format(1, "00000")
GetBookID = Format(1, "00000")
Else
'GetBookID = "Book ID - " & Format(rs(0).Va lue, "00000")
GetBookID = Format(rs(0).Va lue + 1, "00000")
End If
End Function
Sub cboCategory(cbo Cat As ComboBox)
Dim a As Integer
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BookCategory Order by Catname"
rs.Open sql, conn
cboCat.Clear
cboCat.AddItem ""
cboCat.ItemData (0) = 0
Do While Not rs.EOF
cboCat.AddItem rs!Catname
cboCat.ItemData (a) = rs(0).Value
rs.MoveNext
Loop
End Sub
Sub AddCat(CatID As String, Catname As String, CreatedBy As String, DateAdded As String, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Select * From BookCategory Where Catname='" & Catname & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate name found. Please enter another name", vbInformation, ""
Exit Sub
End If
With rs
.AddNew
!CatID = CatID
!Catname = StrConv(Catname , vbProperCase)
!CreatedBy = CreatedBy
!DateAdded = DateAdded
!DateModify = DateModify
!LastModifyBy = LastModifyBy
.Update
End With
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub UpdateCat(CatID As String, Catname As String, OldCat As String, DateModify As String, LastModifyBy As String)
If CategoryInUse(O ldCat) = False Then
If rs.State = adStateOpen Then rs.Close
sql = "Update bookcategory set bookcategory.ca tid='" & CatID & "',bookcategory .catname='" & StrConv(Catname , vbProperCase) & "',bookcategory .DateModify='" & DateModify & "',bookcategory .LastModifyBy=' " & LastModifyBy & "' Where bookcategory.ca tname='" & OldCat & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
Else
MsgBox "Can't Update Record. Category in use!", vbExclamation, "Item can't be deleted " 'End If
End If
End Sub
Public Sub DeleteCat(Catna me As String)
If CategoryInUse(C atname) = False Then
If rs.State = adStateOpen Then rs.Close
sql = " Delete * From BookCategory Where Catname='" & Catname & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
Else
MsgBox "Can't Delete Record. Category in use!", vbExclamation, "Item can't be deleted " 'End If
End If
End Sub
Public Sub DisplayCategory (lstCat As ListView)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
sql = "select * from bookcategory Order by catid"
rs.Open sql, conn
lstCat.ListItem s.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstCat.ListItem s.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
rs.MoveNext
Loop
End Sub
Function GetCatID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT max(BookCategor y.CatID) AS MaxOfCatID " & _
" FROM BookCategory;"
rs.Open sql, conn
If IsNull(rs(0).Va lue) Then
GetCatID = Format(1, "00000")
Else
GetCatID = Format(rs(0).Va lue + 1, "00000")
End If
End Function
Function CategoryInUse(C atname As String) As Boolean
Dim rst As New ADODB.Recordset
rst.Open " SELECT BooksInfo.Categ ory" & _
" FROM BooksInfo" & _
" WHERE (((BooksInfo.Ca tegory)='" & Catname & "'));", conn
If Not rst.EOF And Not rst.BOF Then
'If Catname = rst.Fields(0).V alue Then
CategoryInUse = True
Else
CategoryInUse = False
End If

'Set rst = Nothing
End Function
Function BooksTotalOut(B ookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(BorrowedBoo ks.CopiesBorrow ) AS SumOfCopiesBorr ow" & _
" FROM BorrowedBooks" & _
" Where (((BorrowedBook s.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).V alue) Then
BooksTotalOut = rst(0).Value
Else
BooksTotalOut = 0
End If
BooksTotalOut = Val(BooksTotalO ut) + Val(BooksTempTo talOut(BookID))
Set rst = Nothing
End Function
Function BooksTempTotalO ut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(TempBorrowe dBooks.CopiesBo rrow) AS SumOfCopiesBorr ow" & _
" FROM TempBorrowedBoo ks" & _
" Where (((TempBorrowed Books.BookID)=' " & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).V alue) Then
BooksTempTotalO ut = rst(0).Value
Else
BooksTempTotalO utt = 0
End If
Set rst = Nothing
End Function
Sub InventoryBooks( lstBooks As ListView, mDate As Date, BookType As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If BookType = "All" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "#" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
ElseIf BookType = "Circulatio n" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "# and booksinfo.bSect ion='Circulatio n'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
ElseIf BookType = "Reserved" Then
sql = " SELECT BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, Sum(BooksInfo.N oOfCopies) AS SumOfNoOfCopies , BooksInfo.bSect ion" & _
" From BooksInfo" & _
" Where BooksInfo.Date_ Acquired <= #" & mDate & "# and booksinfo.bSect ion='Reserved'" & _
" GROUP BY BooksInfo.BookI D, BooksInfo.ISBN, BooksInfo.Title , BooksInfo.Autho r, BooksInfo.Date_ Acquired, BooksInfo.bSect ion" & _
" ORDER BY BooksInfo.BookI D;"
End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a, 1, 1)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
If IsNull(rs(5).Va lue) Then
lstItem.SubItem s(6) = 0
Else
lstItem.SubItem s(6) = Val(rs(5).Value ) - Val(BooksTotalO ut(rs(0).Value) )
End If
lstItem.SubItem s(7) = Val(BooksTotalO ut(rs(0).Value) )
lstItem.SubItem s(8) = Val(lstItem.Sub Items(6)) + Val(lstItem.Sub Items(7))
lstItem.SubItem s(9) = rs(6).Value
rs.MoveNext
Loop
End Sub
Public Sub SaveTempInvento ry(lstData As ListView)
Dim a As Integer
If rs.State = adStateOpen Then rs.Close
rs.Open " Delete * From TempInventory", conn
If rs.State = adStateOpen Then rs.Close
sql = " Select * From TempInventory"
rs.Open sql, conn
If lstData.ListIte ms.Count > 0 Then
For a = 1 To lstData.ListIte ms.Count
With rs
.AddNew
!No = lstData.ListIte ms(a).Text
!BookID = lstData.ListIte ms(a).SubItems( 1)
!ISBN = lstData.ListIte ms(a).SubItems( 2)
!Title = lstData.ListIte ms(a).SubItems( 3)
!Author = lstData.ListIte ms(a).SubItems( 4)
!Date_Acquired = lstData.ListIte ms(a).SubItems( 5)
!CopiesOnHand = lstData.ListIte ms(a).SubItems( 6)
!CopiesBorrow = lstData.ListIte ms(a).SubItems( 7)
!TotalCopies = lstData.ListIte ms(a).SubItems( 8)
!Section = lstData.ListIte ms(a).SubItems( 9)
.Update
End With
Next
End If
If rs.State = adStateOpen Then rs.Close
End Sub
Sub DisplayBookCard (lstBooks As ListView, BookID As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If BookID <> "" Then
sql = " SELECT ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" From ReturnedBooks" & _
" Where ReturnedBooks.B ookID = '" & BookID & "'" & _
" GROUP BY ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" ORDER BY ReturnedBooks.T ransactionID, ReturnedBooks.D ate_Borrowed;"

Else
sql = " SELECT ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" From ReturnedBooks" & _
" GROUP BY ReturnedBooks.T ransactionID, ReturnedBooks.B ookID, ReturnedBooks.B ookTitle, ReturnedBooks.B orrowersID, ReturnedBooks.B orrowersName, ReturnedBooks.D ate_Borrowed, ReturnedBooks.D ate_Returned, ReturnedBooks.D ueDate, ReturnedBooks.C opiesReturn" & _
" ORDER BY ReturnedBooks.T ransactionID, ReturnedBooks.D ate_Borrowed;"

End If
rs.Open sql, conn
lstBooks.ListIt ems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListIt ems.Add(, , a)
lstItem.SubItem s(1) = rs(0).Value
lstItem.SubItem s(2) = rs(1).Value
lstItem.SubItem s(3) = rs(2).Value
lstItem.SubItem s(4) = rs(3).Value
lstItem.SubItem s(5) = rs(4).Value
lstItem.SubItem s(6) = rs(5).Value
lstItem.SubItem s(7) = rs(6).Value
lstItem.SubItem s(8) = rs(7).Value
lstItem.SubItem s(9) = rs(8).Value
rs.MoveNext
Loop
End Sub

[Rabbit]

Please output just the SQL. The code gets in the way.