Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-06-2019, 09:26 AM   #1
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Library book checkout form

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
CheckoutMenu.jpg

Archivetbl.jpg

Book1tble.jpg

Userstbl.jpg

here is my error code
error.PNG

Cubsm22p is offline   Reply With Quote
Old 03-06-2019, 09:39 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,815
Thanks: 57
Thanked 1,273 Times in 1,254 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Library book checkout form

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 03-06-2019, 09:48 AM   #3
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

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

Cubsm22p is offline   Reply With Quote
Old 03-06-2019, 10:04 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,815
Thanks: 57
Thanked 1,273 Times in 1,254 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Library book checkout form

Quote:
Originally Posted by Cubsm22p View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 03-06-2019, 10:29 AM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Library book checkout form

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.
Cronk is offline   Reply With Quote
Old 03-06-2019, 04:20 PM   #6
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

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 is offline   Reply With Quote
Old 03-06-2019, 05:16 PM   #7
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

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

Cubsm22p is offline   Reply With Quote
Old 03-06-2019, 05:18 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Library book checkout form

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?
Cronk is offline   Reply With Quote
Old 03-06-2019, 05:46 PM   #9
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

Yea i got it to work using this:
Help.jpg

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


Thanks
Cubsm22p is offline   Reply With Quote
Old 03-06-2019, 08:58 PM   #10
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Library book checkout form

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
Cronk is offline   Reply With Quote
Old 03-07-2019, 06:47 AM   #11
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

WORKED.jpg



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

Last edited by Cubsm22p; 03-07-2019 at 07:16 AM.
Cubsm22p is offline   Reply With Quote
Old 03-07-2019, 07:17 AM   #12
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 36
Thanks: 5
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: Library book checkout form

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

Last edited by Cubsm22p; 03-07-2019 at 07:22 AM.
Cubsm22p is offline   Reply With Quote
Old 03-07-2019, 08:29 AM   #13
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Library book checkout form

Quote:
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 = '" & Forms![CheckOutMenu]![UserName] & "'") >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

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help In Book Lending Library System NotesAndSoft General 8 02-25-2014 07:19 AM
lending library alterations mutli checkout 570rm47 Forms 5 04-24-2012 01:47 PM
book library sample mikevds Forms 3 02-25-2009 11:55 PM
Book library form list boxes mikevds Forms 0 11-25-2008 05:04 PM
[SOLVED] Find the most popular book in a library Karolis Modules & VBA 3 12-28-2004 02:23 AM




All times are GMT -8. The time now is 10:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World