'VB in Access' and SQL: Library Management

S

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
 
D

David W. Fenton

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?
Click to expand...

Do you want to do this *before* running the delete, or afterwards,
so you can report the results to the user?

If the latter, you can replace the DoCmd.RunSQL command to:

Dim db As DAO.Database
Set db = CurrentDB()
db.Execute queryString, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were deleted."
Set db = Nothing

If you want to know the count before hand, you could use DCount:

Dim lngRecordCount as Long
lngRecordCount = DCount("*", "studenttransaction", _
"[accno]=" & accNo)

Then you can use that to decide whether or not to run the SQL (not
that it matters -- if there are no matches, nothing will happen; but
it is more efficient to not hit the back end with a DELETE query
that deletes nothing at all).
 
Top