Library book checkout form (1 Viewer)

Cubsm22p · Mar 6, 2019

I am having trouble with this function of checking books out. I am getting a syntax error I will post pictures of all the tables I use in my database and the form and help would be appreciated.

My thought was to have a form where user inputs ISBN and UserName then clicks an OK Button which then If they are valid entrys then add ISBN, UserName, Date, to the Archive table

here is my error code

theDBguy · Mar 6, 2019

Hi. I don't think your SQL statement is correct, but I am not sure how to correct it at this point. You said you only want the book added if all the input is correct/valid, but I don't see how you're doing this validation.

Cubsm22p · Mar 6, 2019

So basically when user enters ISBN and UserName Then once they click ok I want it to put a record in the Archive table With the ISBN and the UserName the user entered and also the Date

theDBguy · Mar 6, 2019

Cubsm22p said:
So basically when user enters ISBN and UserName Then once they click ok I want it to put a record in the Archive table With the ISBN and the UserName the user entered and also the Date

If that's all you need, then you don't need a WHERE clause, correct?

Cronk · Mar 6, 2019

For a start, I believe SQL is a reserved word. Change it to

Dim strSQL as string

The problem though is in the construction of the string. The query analyser does not know what Me.Combo is, nor other values you are trying to insert.

Add

Code:

debug.print strSQL

in your code before the Docmd.runSQL line, and try posting the output into a new query. That will tell you the problems.

Come back here then if you can't figure it out.

Cubsm22p · Mar 7, 2019

I got it to add a record to the Archive table when I click ok and my record source on the form is the Archive table.

I would like it to take away one book copy from the Books1 table with the matching ISBN. Any Idea on how I would do this?

Thanks

Cubsm22p · Mar 7, 2019

Here is What i have for a guess on the last issue

Cronk · Mar 7, 2019

I know what you are trying to do and how, but I already told you the problem is with the SQL string and suggested how you can see what the problem is. Did you try that?

Cubsm22p · Mar 7, 2019

Yea i got it to work using this:

But now i would like to add IF Books1.[NumOfCopys] == 0 then error cant check out!

Thanks

Cronk · Mar 7, 2019

Firstly in VBA, you only use one equal sign, eg

Code:

if A=B then

I will presume that Books1 in the expression Books1,NumOfCopys (square brackets here are superfluous), is a table not a Class object that has been created.
In that case, you cannot refer directly to a table like that in VBA.
Either open a recordset or use dLookup

Code for latter is

Code:

if dlookup("NumOfCopys", "Books1", "ISBN=" & Forms!CheckOutMenu.ISBN) =0 then

assuming ISBN is numeric

Cubsm22p · Mar 7, 2019

I tested it more and I cant get the IF statement to run : It always jumps to the ELSE

updated code still not working:

Code:

Private Sub Command47_Click()
Dim strSQL As String

If DLookup("NumOfCopys", "Books1", "ISBN=" & Forms!CheckOutMenu.ISBN) = 0 Then
MsgBox "Book is unavaliable."
Else
strSQL = "Update Books1 SET [NumOfCopys] = [NumOfCopys]-1 WHERE Books1.ISBN = Forms![CheckOutMenu]![ISBN]"
DoCmd.RunSQL strSQL

DoCmd.Save

MsgBox "Check Out Complete."
DoCmd.GoToRecord , , acNewRec
Me.CheckOutDate = Date

End If
End Sub

Thanks

Cubsm22p · Mar 7, 2019

Got it to Function properly using

Code:

Private Sub Command47_Click()
Dim strSQL As String

If DLookup("NumOfCopys", "Books1", "ISBN = Forms![CheckOutMenu]![ISBN]") = 0 Then
MsgBox "Book is unavaliable."
Else
strSQL = "Update Books1 SET [NumOfCopys] = [NumOfCopys]-1 WHERE Books1.ISBN = Forms![CheckOutMenu]![ISBN]"
DoCmd.RunSQL strSQL

DoCmd.Save

MsgBox "Check Out Complete."
DoCmd.GoToRecord , , acNewRec
Me.CheckOutDate = Date

End If
End Sub

Now my question is can I put 2 IF statements in one So i can limit the books a user checks out
example:
IF DLookup("BooksOut","User","UserName = Forms![CheckOutMenu]![UserName]") >6
Else
msgBox "too many books checked out"

I need to put this with that other IF statement, because members can only check out 6 books at a time

Cronk · Mar 7, 2019

Got it to Function properly using

Yes, your logic was back the front.
Also, glad that you posted code in your message. The attached snapshot was not that clear - equal signs looked like minus signs.
Yes, you can embed as many If /else/then clauses as you want. However, I would put it first. That is, check whether user can borrow and if not exit the sub. (And again, you have your logic back the front)

Code:

IF DLookup("BooksOut","User","UserName =[B][COLOR=red] '"[/COLOR][/B] [B][COLOR=red]&[/COLOR][/B] Forms![CheckOutMenu]![UserName] & [B][COLOR=red]"'[/COLOR][/B]") >6 then
'Else
     msgBox "too many books checked out"
     exit sub
endif


'Then rest of your code

BTW, you decrease the number of books on the shelf, but no code shown to increment the books held by a borrower when a book is taken

Library book checkout form (1 Viewer)

Cubsm22p

Registered User.

theDBguy

I’m here to help

Cubsm22p

Registered User.

theDBguy

I’m here to help

Cronk

Registered User.

Cubsm22p

Registered User.

Cubsm22p

Registered User.

Cronk

Registered User.

Cubsm22p

Registered User.

Cronk

Registered User.

Cubsm22p

Registered User.

Cubsm22p

Registered User.

Cronk

Registered User.

Similar threads

Users who are viewing this thread