'VB in Access' and SQL: Library Management

[Sameer]

A question about VB in Access:

Please see the code:

Private Sub collectFromStudent_Click()
On Error GoTo Err_collectFromStudent_Click

Dim accNo As String
Dim queryString As String

accNo = InputBox("Enter accession number of book: ", "Library
Manager")

queryString = "DELETE * FROM studenttransaction WHERE accno= " &
accNo & ";"
DoCmd.RunSQL queryString

Exit_collectFromStudent_Click:
Exit Sub

Err_collectFromStudent_Click:
MsgBox Err.Description
Resume Exit_collectFromStudent_Click
End Sub


This is a event-function which gets activated when a user presses a
button. It accepts a accession number as integer.
Through VB and SQL code how can i determine whether such book with
accession code exists in a table 'studenttransaction' or not?
Whether the DoCmd.RunQSL returns some value?
How to check this?


-Sameer

 

[Brendan Reynolds]

You can do it with the Execute method of either the ADO Connection or the
DAO Database objects ...

Public Sub TestSub()

Dim lngCount As Long
Dim strSQL As String
Dim db As DAO.Database

strSQL = "DELETE * FROM tblTest WHERE TestNum = " & InputBox("TestNum")

If LCase$(Left$(InputBox("Use ADO? (y/n)"), 1)) = "y" Then
CurrentProject.Connection.Execute strSQL, lngCount
MsgBox "Records affected: " & lngCount
Else
Set db = CurrentDb
db.Execute strSQL
MsgBox "Records affected: " & db.RecordsAffected
End If

End Sub

 

[Albert D. Kallal]

It is not clear if you want to know this BEFORE you delete, or after?

Furhter, can there be more then one deletion to happen?

Lets assume just one delete at a time, you could use:

The follwing code would loop utnil the user hits cancel to exit the msgbox
prompt...

Dim accNo As String
Dim queryString As String
Dim rst As dao.Recordset


Do

accNo = InputBox("Enter accession number of book : ", "Library
Manager")

If accNo = "" Then
Exit Do
End If

queryString = "select accno from StudentTransaction" & _
" WHERE accno = " & accNo

Set rst = CurrentDb.OpenRecordset(queryString)

If rst.RecordCount > 0 Then
If MsgBox("Do you want to Delete acc " & accNo, _
vbYesNoCancel + vbQuestion, "Delete?") = vbYes Then
rst.Delete
End If
Else
MsgBox accNo & " not found ", vbExclamation, "not found"
End If

rst.Close
Set rst = Nothing

Loop

 

[Sameer]

Thanks for help!
When I tried to implement some code it gives an error
'User-defined type not defined' for the statement
Dim db As DAO.Database

Also the DAO.Database class is not available in Object Browser.
May I need to take references like VB?
What I have to do for successful compilation and making this class
available for the coding?
Please help.
-Sameer

 

[Brendan Reynolds]

The code demonstrates both ADO and DAO methods. If you want to use the ADO
method, you'll need a reference (select References from the Tools menu in
the VBA IDE) to the Microsoft ActiveX Data Objects 2.x Library, while if you
want to use the DAO method you'll need a reference to the Microsoft DAO 3.6
Object Library. From your description, you probably have the ADO reference
already, so if you want to use ADO you could just delete or comment out the
DAO demonstration. You may find the following link helpful ...

http://www.mvps.org/access/bugs/bugs0031.htm