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.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
lstItem.SubItems(6) = rs(5).Value
lstItem.SubItems(7) = rs(6).Value
If IsNull(rs(7).Value) Then
lstItem.SubItems(8) = 0
Else
lstItem.SubItems(8) = Val(rs(7).Value) - Val(BooksTotalOut(rs(0).Value))
End If
If lstItem.SubItems(8) = 0 Then
lstItem.SubItems(9) = "Not Available "
Else
lstItem.SubItems(9) = "Available"
End If
lstItem.SubItems(10) = Val(BooksTotalOut(rs(0).Value))
lstItem.SubItems(11) = Val(lstItem.SubItems(8)) + Val(lstItem.SubItems(10))
lstItem.SubItems(12) = rs(8).Value
lstItem.SubItems(13) = rs(9).Value
lstItem.SubItems(14) = rs(10).Value
lstItem.SubItems(15) = rs(11).Value
lstItem.SubItems(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListItems.Count = 0 Then MsgBox "No data found", vbInformation, ""
' Set rs = Nothing
'Set rs = Nothing
End Sub]
6  1102  Rabbit 12,516
Recognized Expert Moderator MVP
Public rs As New ADODB.Recordset
Public conn As New ADODB.Connection
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 TempBorrowerName 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=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=" & App.Path & "\Data.mdb;Jet OLEDB:Database Password="
ConString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=" & App.Path & "\xDatax.library;Jet OLEDB:Database Password=library"
conn.Open ConString
With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
SplashFrm.Show
'LoginFrm.Show
'MainFrm.Show
'AccountsConfigFrm.Show
End Sub
Rabbit 12,516
Recognized Expert Moderator MVP
I still don't see the sql.
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(Publisher, 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(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, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Update BooksInfo Set BooksInfo.Bookid='" & BookID & "',BooksInfo.isbn='" & ISBN & "',BooksInfo.title='" & StrConv(Title, vbProperCase) & "',BooksInfo.author='" & StrConv(Author, vbProperCase) & "',BooksInfo.Category='" & Category & "',BooksInfo.publisher='" & StrConv(Publisher, vbProperCase) & "',BooksInfo.Date_Acquired='" & Date_Acquired & "',BooksInfo.noofcopies='" & NoOfCopies & "',BooksInfo.bSection='" & bSection & "',BooksInfo.DateModify='" & DateModify & "',BooksInfo.LastModifyBy='" & LastModifyBy & "' Where BooksInfo.BookID='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub DeleteBooks(BookID 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(lstBooks 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.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.BookID like '" & SearchFor & "%' " & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.BookID"
ElseIf SearchIn = "ISBN" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.ISBN like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.ISBN"
ElseIf SearchIn = "Book Title" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Title like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Title"
ElseIf SearchIn = "Author" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Author like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Author"
ElseIf SearchIn = "Category" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Category like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Category"
ElseIf SearchIn = "Publisher" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Publisher like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Publisher"
Else
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection,BooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" From BooksInfo" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" ORDER BY BooksInfo.BookID;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
lstItem.SubItems(6) = rs(5).Value
lstItem.SubItems(7) = rs(6).Value
If IsNull(rs(7).Value) Then
lstItem.SubItems(8) = 0
Else
lstItem.SubItems(8) = Val(rs(7).Value) - Val(BooksTotalOut(rs(0).Value))
End If
If lstItem.SubItems(8) = 0 Then
lstItem.SubItems(9) = "Not Available "
Else
lstItem.SubItems(9) = "Available"
End If
lstItem.SubItems(10) = Val(BooksTotalOut(rs(0).Value))
lstItem.SubItems(11) = Val(lstItem.SubItems(8)) + Val(lstItem.SubItems(10))
lstItem.SubItems(12) = rs(8).Value
lstItem.SubItems(13) = rs(9).Value
lstItem.SubItems(14) = rs(10).Value
lstItem.SubItems(15) = rs(11).Value
lstItem.SubItems(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListItems.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.BookID) AS MaxOfBookID" & _
" FROM BooksInfo"
rs.Open sql, conn
If IsNull(rs(0).Value) Then
'GetBookID = "Book ID - " & Format(1, "00000")
GetBookID = Format(1, "00000")
Else
'GetBookID = "Book ID - " & Format(rs(0).Value, "00000")
GetBookID = Format(rs(0).Value + 1, "00000")
End If
End Function
Sub cboCategory(cboCat 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(OldCat) = False Then
If rs.State = adStateOpen Then rs.Close
sql = "Update bookcategory set bookcategory.catid='" & CatID & "',bookcategory.catname='" & StrConv(Catname, vbProperCase) & "',bookcategory.DateModify='" & DateModify & "',bookcategory.LastModifyBy='" & LastModifyBy & "' Where bookcategory.catname='" & 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(Catname As String)
If CategoryInUse(Catname) = 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.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstCat.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
rs.MoveNext
Loop
End Sub
Function GetCatID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT max(BookCategory.CatID) AS MaxOfCatID " & _
" FROM BookCategory;"
rs.Open sql, conn
If IsNull(rs(0).Value) Then
GetCatID = Format(1, "00000")
Else
GetCatID = Format(rs(0).Value + 1, "00000")
End If
End Function
Function CategoryInUse(Catname As String) As Boolean
Dim rst As New ADODB.Recordset
rst.Open " SELECT BooksInfo.Category" & _
" FROM BooksInfo" & _
" WHERE (((BooksInfo.Category)='" & Catname & "'));", conn
If Not rst.EOF And Not rst.BOF Then
'If Catname = rst.Fields(0).Value Then
CategoryInUse = True
Else
CategoryInUse = False
End If
'Set rst = Nothing
End Function
Function BooksTotalOut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(BorrowedBooks.CopiesBorrow) AS SumOfCopiesBorrow" & _
" FROM BorrowedBooks" & _
" Where (((BorrowedBooks.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).Value) Then
BooksTotalOut = rst(0).Value
Else
BooksTotalOut = 0
End If
BooksTotalOut = Val(BooksTotalOut) + Val(BooksTempTotalOut(BookID))
Set rst = Nothing
End Function
Function BooksTempTotalOut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(TempBorrowedBooks.CopiesBorrow) AS SumOfCopiesBorrow" & _
" FROM TempBorrowedBooks" & _
" Where (((TempBorrowedBooks.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).Value) Then
BooksTempTotalOut = rst(0).Value
Else
BooksTempTotalOutt = 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.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "#" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
ElseIf BookType = "Circulation" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "# and booksinfo.bSection='Circulation'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
ElseIf BookType = "Reserved" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "# and booksinfo.bSection='Reserved'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
If IsNull(rs(5).Value) Then
lstItem.SubItems(6) = 0
Else
lstItem.SubItems(6) = Val(rs(5).Value) - Val(BooksTotalOut(rs(0).Value))
End If
lstItem.SubItems(7) = Val(BooksTotalOut(rs(0).Value))
lstItem.SubItems(8) = Val(lstItem.SubItems(6)) + Val(lstItem.SubItems(7))
lstItem.SubItems(9) = rs(6).Value
rs.MoveNext
Loop
End Sub
Public Sub SaveTempInventory(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.ListItems.Count > 0 Then
For a = 1 To lstData.ListItems.Count
With rs
.AddNew
!No = lstData.ListItems(a).Text
!BookID = lstData.ListItems(a).SubItems(1)
!ISBN = lstData.ListItems(a).SubItems(2)
!Title = lstData.ListItems(a).SubItems(3)
!Author = lstData.ListItems(a).SubItems(4)
!Date_Acquired = lstData.ListItems(a).SubItems(5)
!CopiesOnHand = lstData.ListItems(a).SubItems(6)
!CopiesBorrow = lstData.ListItems(a).SubItems(7)
!TotalCopies = lstData.ListItems(a).SubItems(8)
!Section = lstData.ListItems(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.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" From ReturnedBooks" & _
" Where ReturnedBooks.BookID = '" & BookID & "'" & _
" GROUP BY ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" ORDER BY ReturnedBooks.TransactionID, ReturnedBooks.Date_Borrowed;"
Else
sql = " SELECT ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" From ReturnedBooks" & _
" GROUP BY ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" ORDER BY ReturnedBooks.TransactionID, ReturnedBooks.Date_Borrowed;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
lstItem.SubItems(6) = rs(5).Value
lstItem.SubItems(7) = rs(6).Value
lstItem.SubItems(8) = rs(7).Value
lstItem.SubItems(9) = rs(8).Value
rs.MoveNext
Loop
End Sub
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(Publisher, 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(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, DateModify As String, LastModifyBy As String)
If rs.State = adStateOpen Then rs.Close
sql = "Update BooksInfo Set BooksInfo.Bookid='" & BookID & "',BooksInfo.isbn='" & ISBN & "',BooksInfo.title='" & StrConv(Title, vbProperCase) & "',BooksInfo.author='" & StrConv(Author, vbProperCase) & "',BooksInfo.Category='" & Category & "',BooksInfo.publisher='" & StrConv(Publisher, vbProperCase) & "',BooksInfo.Date_Acquired='" & Date_Acquired & "',BooksInfo.noofcopies='" & NoOfCopies & "',BooksInfo.bSection='" & bSection & "',BooksInfo.DateModify='" & DateModify & "',BooksInfo.LastModifyBy='" & LastModifyBy & "' Where BooksInfo.BookID='" & BookID & "'"
rs.Open sql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Sub DeleteBooks(BookID 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(lstBooks 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.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.BookID like '" & SearchFor & "%' " & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.BookID"
ElseIf SearchIn = "ISBN" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.ISBN like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.ISBN"
ElseIf SearchIn = "Book Title" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Title like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Title"
ElseIf SearchIn = "Author" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Author like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Author"
ElseIf SearchIn = "Category" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Category like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Category"
ElseIf SearchIn = "Publisher" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSectionBooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" FROM BooksInfo" & _
" Where BooksInfo.Publisher like '" & SearchFor & "%'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" Order by BooksInfo.Publisher"
Else
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection,BooksInfo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" From BooksInfo" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Category, BooksInfo.Publisher, BooksInfo.Date_Acquired,BooksInfo.bSection,BooksIn fo.CreatedBy, BooksInfo.DateAdded, BooksInfo.DateModify, BooksInfo.LastModifyBy" & _
" ORDER BY BooksInfo.BookID;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
lstItem.SubItems(6) = rs(5).Value
lstItem.SubItems(7) = rs(6).Value
If IsNull(rs(7).Value) Then
lstItem.SubItems(8) = 0
Else
lstItem.SubItems(8) = Val(rs(7).Value) - Val(BooksTotalOut(rs(0).Value))
End If
If lstItem.SubItems(8) = 0 Then
lstItem.SubItems(9) = "Not Available "
Else
lstItem.SubItems(9) = "Available"
End If
lstItem.SubItems(10) = Val(BooksTotalOut(rs(0).Value))
lstItem.SubItems(11) = Val(lstItem.SubItems(8)) + Val(lstItem.SubItems(10))
lstItem.SubItems(12) = rs(8).Value
lstItem.SubItems(13) = rs(9).Value
lstItem.SubItems(14) = rs(10).Value
lstItem.SubItems(15) = rs(11).Value
lstItem.SubItems(16) = rs(12).Value
rs.MoveNext
Loop
If lstBooks.ListItems.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.BookID) AS MaxOfBookID" & _
" FROM BooksInfo"
rs.Open sql, conn
If IsNull(rs(0).Value) Then
'GetBookID = "Book ID - " & Format(1, "00000")
GetBookID = Format(1, "00000")
Else
'GetBookID = "Book ID - " & Format(rs(0).Value, "00000")
GetBookID = Format(rs(0).Value + 1, "00000")
End If
End Function
Sub cboCategory(cboCat 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(OldCat) = False Then
If rs.State = adStateOpen Then rs.Close
sql = "Update bookcategory set bookcategory.catid='" & CatID & "',bookcategory.catname='" & StrConv(Catname, vbProperCase) & "',bookcategory.DateModify='" & DateModify & "',bookcategory.LastModifyBy='" & LastModifyBy & "' Where bookcategory.catname='" & 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(Catname As String)
If CategoryInUse(Catname) = 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.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstCat.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
rs.MoveNext
Loop
End Sub
Function GetCatID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT max(BookCategory.CatID) AS MaxOfCatID " & _
" FROM BookCategory;"
rs.Open sql, conn
If IsNull(rs(0).Value) Then
GetCatID = Format(1, "00000")
Else
GetCatID = Format(rs(0).Value + 1, "00000")
End If
End Function
Function CategoryInUse(Catname As String) As Boolean
Dim rst As New ADODB.Recordset
rst.Open " SELECT BooksInfo.Category" & _
" FROM BooksInfo" & _
" WHERE (((BooksInfo.Category)='" & Catname & "'));", conn
If Not rst.EOF And Not rst.BOF Then
'If Catname = rst.Fields(0).Value Then
CategoryInUse = True
Else
CategoryInUse = False
End If
'Set rst = Nothing
End Function
Function BooksTotalOut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(BorrowedBooks.CopiesBorrow) AS SumOfCopiesBorrow" & _
" FROM BorrowedBooks" & _
" Where (((BorrowedBooks.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).Value) Then
BooksTotalOut = rst(0).Value
Else
BooksTotalOut = 0
End If
BooksTotalOut = Val(BooksTotalOut) + Val(BooksTempTotalOut(BookID))
Set rst = Nothing
End Function
Function BooksTempTotalOut(BookID As String) As String
Dim rst As New ADODB.Recordset
sql = " SELECT Sum(TempBorrowedBooks.CopiesBorrow) AS SumOfCopiesBorrow" & _
" FROM TempBorrowedBooks" & _
" Where (((TempBorrowedBooks.BookID)='" & BookID & "'));"
rst.Open sql, conn
If Not IsNull(rst(0).Value) Then
BooksTempTotalOut = rst(0).Value
Else
BooksTempTotalOutt = 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.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "#" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
ElseIf BookType = "Circulation" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "# and booksinfo.bSection='Circulation'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
ElseIf BookType = "Reserved" Then
sql = " SELECT BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, Sum(BooksInfo.NoOfCopies) AS SumOfNoOfCopies, BooksInfo.bSection" & _
" From BooksInfo" & _
" Where BooksInfo.Date_Acquired <= #" & mDate & "# and booksinfo.bSection='Reserved'" & _
" GROUP BY BooksInfo.BookID, BooksInfo.ISBN, BooksInfo.Title, BooksInfo.Author, BooksInfo.Date_Acquired, BooksInfo.bSection" & _
" ORDER BY BooksInfo.BookID;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a, 1, 1)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
If IsNull(rs(5).Value) Then
lstItem.SubItems(6) = 0
Else
lstItem.SubItems(6) = Val(rs(5).Value) - Val(BooksTotalOut(rs(0).Value))
End If
lstItem.SubItems(7) = Val(BooksTotalOut(rs(0).Value))
lstItem.SubItems(8) = Val(lstItem.SubItems(6)) + Val(lstItem.SubItems(7))
lstItem.SubItems(9) = rs(6).Value
rs.MoveNext
Loop
End Sub
Public Sub SaveTempInventory(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.ListItems.Count > 0 Then
For a = 1 To lstData.ListItems.Count
With rs
.AddNew
!No = lstData.ListItems(a).Text
!BookID = lstData.ListItems(a).SubItems(1)
!ISBN = lstData.ListItems(a).SubItems(2)
!Title = lstData.ListItems(a).SubItems(3)
!Author = lstData.ListItems(a).SubItems(4)
!Date_Acquired = lstData.ListItems(a).SubItems(5)
!CopiesOnHand = lstData.ListItems(a).SubItems(6)
!CopiesBorrow = lstData.ListItems(a).SubItems(7)
!TotalCopies = lstData.ListItems(a).SubItems(8)
!Section = lstData.ListItems(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.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" From ReturnedBooks" & _
" Where ReturnedBooks.BookID = '" & BookID & "'" & _
" GROUP BY ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" ORDER BY ReturnedBooks.TransactionID, ReturnedBooks.Date_Borrowed;"
Else
sql = " SELECT ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" From ReturnedBooks" & _
" GROUP BY ReturnedBooks.TransactionID, ReturnedBooks.BookID, ReturnedBooks.BookTitle, ReturnedBooks.BorrowersID, ReturnedBooks.BorrowersName, ReturnedBooks.Date_Borrowed, ReturnedBooks.Date_Returned, ReturnedBooks.DueDate, ReturnedBooks.CopiesReturn" & _
" ORDER BY ReturnedBooks.TransactionID, ReturnedBooks.Date_Borrowed;"
End If
rs.Open sql, conn
lstBooks.ListItems.Clear
Do While Not rs.EOF
a = a + 1
Set lstItem = lstBooks.ListItems.Add(, , a)
lstItem.SubItems(1) = rs(0).Value
lstItem.SubItems(2) = rs(1).Value
lstItem.SubItems(3) = rs(2).Value
lstItem.SubItems(4) = rs(3).Value
lstItem.SubItems(5) = rs(4).Value
lstItem.SubItems(6) = rs(5).Value
lstItem.SubItems(7) = rs(6).Value
lstItem.SubItems(8) = rs(7).Value
lstItem.SubItems(9) = rs(8).Value
rs.MoveNext
Loop
End Sub
Rabbit 12,516
Recognized Expert Moderator MVP
Please output just the SQL. The code gets in the way.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: nek |
last post by:
Greetings,
We try to automate database creation via script which gets executed
remotely for a number of servers.
This works fine for most of the servers. However, it occasionally
fails some...
|
by: maricel |
last post by:
Is it possible to catalog a DB2 UDB7.2 system/database from DB2 UDB8.1 using
Configuration Assistant of v8.1?
Thanks for your input.
maricel.
|
by: Ripul Handa |
last post by:
Hi
We are running IIS 5.0 cluster with cisco local director.
We are running a website on 2 webservers and I have been
observing that from past few days we have are getting
this error message of...
|
by: Dave |
last post by:
I am getting an intermittent database error on my asp page.
I am using Access 2003 with classic ASP.
The error is this:
Microsoft JET Database Engine Error 80040e10
No value given for one or...
|
by: mmakundi |
last post by:
Database Error
Io exception: The Network Adapter could not establish the connection:
I have installed the oracle database on windows XP sp2 succeesfully for the first time and when I try to...
| | |
by: fifko |
last post by:
Hello everybody,
I realize that this is most probably the best place to post this, but I just need some explanation from someone, who at least knows what's the following about... It's bugging me...
|
by: soidariti |
last post by:
database error - parse error, unexpected $, expecting kEND
AddUserAuthorisationToUsers.rb migration file
1. class AddUserAuthorisationToUsers < ActiveRecord::Migration
2. def self.up
...
|
by: Om |
last post by:
Hi All,
Can anyone explain me, why we have Resource and Distribution System
Database in SQL Server 2005. Whether they were also available in
previous versions also.
Thanks in Advance.
...
|
by: moroccanplaya |
last post by:
WordPress database error:
SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2011-04-13 08:52:59' AND (post_status = "publish") GROUP BY wp_posts.ID ORDER BY post_date DESC LIMIT -10,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| | |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
