Adding to query

  • Thread starter Thread starter Book Man
  • Start date Start date
B

Book Man

My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table Loan

Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko
 
You might not need a Renewed field at all, but if you feel you need one, it
should probably go in the LOAN table. Let's say the standard loan time is
two weeks. When a student checks out a book, you should enter a row in
LOAN, set Loan Date to Date(), and set Loan Due to Date() + 14. When the
student comes in to renew before the due date, extend the Loan Due value.
When Loan Due - Loan Date is greater than 14, don't allow it to be renewed
again (perhaps by locking the Loan Returned field in the form you provide to
edit the data). A Renewed field could be handy, though. You allow the
clerk to enter data in a new row. After the row is saved, lock all controls
except Loan Returned, Fined, and Renew -- and leave Renew unlocked only if
its value is False (not renewed yet). If the clerk clicks Renew, extend the
Loan Due automatically and then lock Renew so that it can't be clicked
again.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Can You be a bit more clearer on where I have to go to
achieve this and the expressions which I must use.

Thanks
-----Original Message-----
You might not need a Renewed field at all, but if you feel you need one, it
should probably go in the LOAN table. Let's say the standard loan time is
two weeks. When a student checks out a book, you should enter a row in
LOAN, set Loan Date to Date(), and set Loan Due to Date () + 14. When the
student comes in to renew before the due date, extend the Loan Due value.
When Loan Due - Loan Date is greater than 14, don't allow it to be renewed
again (perhaps by locking the Loan Returned field in the form you provide to
edit the data). A Renewed field could be handy, though. You allow the
clerk to enter data in a new row. After the row is saved, lock all controls
except Loan Returned, Fined, and Renew -- and leave Renew unlocked only if
its value is False (not renewed yet). If the clerk clicks Renew, extend the
Loan Due automatically and then lock Renew so that it can't be clicked
again.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Book Man said:
My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table Loan

Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko
Click to expand...


.
Click to expand...
 
You'll do it all in code behind the form you provide to edit book loans.
For a new record, the best place to trigger setting the Loan Date and Loan
Due is in the AfterUpdate event of Book Number:

Private Sub Book_Number_AfterUpdate()
Me.[Loan Date] = Date
Me.[Loan Due] = Date + 14
End Sub

To control locking and unlocking of controls (assuming you have a Renew
field), use the form's Current event:

Private Sub Form_Current()
' If not on a new row
If Not Me.NewRecord Then
' Lock everything except returned, fine, and renew
Me.[Student Name].Locked = True
Me.[Book Number].Locked = True
Me.[Loan Date].Locked = True
Me.[Loan Due].Locked = True
' If Renew checked, then lock it, too
If Me.Renew Then Me.Renew.Locked = True
Else
' New row - unlock everything
Me.[Student Name].Locked = False
Me.[Book Number].Locked = False
Me.[Loan Date].Locked = False
Me.[Loan Due].Locked = False
Me.Renew.Locked = False
End If
End Sub

And finally, when the user updates the Renew check box, set the new due
date:

Private Sub Renew_AfterUpdate()
If Me.Renew = True Then
Me.[Loan Due] = Me.[Loan Date] + 28
Me.Renew.Locked = True
End If

You could also write code to check the value of Loan Returned after it is
updated and automatically calculate an appropriate fine if returned is later
than the due date.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Book Man said:
Can You be a bit more clearer on where I have to go to
achieve this and the expressions which I must use.

Thanks
-----Original Message-----
You might not need a Renewed field at all, but if you feel you need one, it
should probably go in the LOAN table. Let's say the standard loan time is
two weeks. When a student checks out a book, you should enter a row in
LOAN, set Loan Date to Date(), and set Loan Due to Date () + 14. When the
student comes in to renew before the due date, extend the Loan Due value.
When Loan Due - Loan Date is greater than 14, don't allow it to be renewed
again (perhaps by locking the Loan Returned field in the form you provide to
edit the data). A Renewed field could be handy, though. You allow the
clerk to enter data in a new row. After the row is saved, lock all controls
except Loan Returned, Fined, and Renew -- and leave Renew unlocked only if
its value is False (not renewed yet). If the clerk clicks Renew, extend the
Loan Due automatically and then lock Renew so that it can't be clicked
again.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Book Man said:
My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table Loan

Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko
Click to expand...


.
Click to expand...
Click to expand...
 
Can you expalin it more step by step e.g where i have to
go to Insert, file etc.

Many thanks
-----Original Message-----
You'll do it all in code behind the form you provide to edit book loans.
For a new record, the best place to trigger setting the Loan Date and Loan
Due is in the AfterUpdate event of Book Number:

Private Sub Book_Number_AfterUpdate()
Me.[Loan Date] = Date
Me.[Loan Due] = Date + 14
End Sub

To control locking and unlocking of controls (assuming you have a Renew
field), use the form's Current event:

Private Sub Form_Current()
' If not on a new row
If Not Me.NewRecord Then
' Lock everything except returned, fine, and renew
Me.[Student Name].Locked = True
Me.[Book Number].Locked = True
Me.[Loan Date].Locked = True
Me.[Loan Due].Locked = True
' If Renew checked, then lock it, too
If Me.Renew Then Me.Renew.Locked = True
Else
' New row - unlock everything
Me.[Student Name].Locked = False
Me.[Book Number].Locked = False
Me.[Loan Date].Locked = False
Me.[Loan Due].Locked = False
Me.Renew.Locked = False
End If
End Sub

And finally, when the user updates the Renew check box, set the new due
date:

Private Sub Renew_AfterUpdate()
If Me.Renew = True Then
Me.[Loan Due] = Me.[Loan Date] + 28
Me.Renew.Locked = True
End If

You could also write code to check the value of Loan Returned after it is
updated and automatically calculate an appropriate fine if returned is later
than the due date.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Book Man said:
Can You be a bit more clearer on where I have to go to
achieve this and the expressions which I must use.

Thanks
-----Original Message-----
You might not need a Renewed field at all, but if you feel you need one, it
should probably go in the LOAN table. Let's say the standard loan time is
two weeks. When a student checks out a book, you
Click to expand...
should
enter a row in
LOAN, set Loan Date to Date(), and set Loan Due to
Click to expand...
Date
() + 14. When the
student comes in to renew before the due date, extend the Loan Due value.
When Loan Due - Loan Date is greater than 14, don't allow it to be renewed
again (perhaps by locking the Loan Returned field in
Click to expand...
the
form you provide to
edit the data). A Renewed field could be handy, though. You allow the
clerk to enter data in a new row. After the row is saved, lock all controls
except Loan Returned, Fined, and Renew -- and leave Renew unlocked only if
its value is False (not renewed yet). If the clerk clicks Renew, extend the
Loan Due automatically and then lock Renew so that it can't be clicked
again.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in
Click to expand...
table
Loan
Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko



.
Click to expand...
Click to expand...


.
Click to expand...
 
Have you designed any forms to work with this data? All the code I
suggested should be in the form you give the user to edit the data. You
should never let a user edit data directly in a table or query. I suppose
if I were doing it, I'd design an outer form to list all the Students and a
subform on that to show loaned books that haven't been returned yet. The
code should be in the subform's VB module. The record source for the
subform should be a query something like:

SELECT LOAN.*
FROM LOAN
WHERE LOAN.[Loan Returned] IS NULL;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Can you expalin it more step by step e.g where i have to
go to Insert, file etc.

Many thanks
-----Original Message-----
You'll do it all in code behind the form you provide to edit book loans.
For a new record, the best place to trigger setting the Loan Date and Loan
Due is in the AfterUpdate event of Book Number:

Private Sub Book_Number_AfterUpdate()
Me.[Loan Date] = Date
Me.[Loan Due] = Date + 14
End Sub

To control locking and unlocking of controls (assuming you have a Renew
field), use the form's Current event:

Private Sub Form_Current()
' If not on a new row
If Not Me.NewRecord Then
' Lock everything except returned, fine, and renew
Me.[Student Name].Locked = True
Me.[Book Number].Locked = True
Me.[Loan Date].Locked = True
Me.[Loan Due].Locked = True
' If Renew checked, then lock it, too
If Me.Renew Then Me.Renew.Locked = True
Else
' New row - unlock everything
Me.[Student Name].Locked = False
Me.[Book Number].Locked = False
Me.[Loan Date].Locked = False
Me.[Loan Due].Locked = False
Me.Renew.Locked = False
End If
End Sub

And finally, when the user updates the Renew check box, set the new due
date:

Private Sub Renew_AfterUpdate()
If Me.Renew = True Then
Me.[Loan Due] = Me.[Loan Date] + 28
Me.Renew.Locked = True
End If

You could also write code to check the value of Loan Returned after it is
updated and automatically calculate an appropriate fine if returned is later
than the due date.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Book Man said:
Can You be a bit more clearer on where I have to go to
achieve this and the expressions which I must use.

Thanks

-----Original Message-----
You might not need a Renewed field at all, but if you
feel you need one, it
should probably go in the LOAN table. Let's say the
standard loan time is
two weeks. When a student checks out a book, you should
enter a row in
LOAN, set Loan Date to Date(), and set Loan Due to Date
() + 14. When the
student comes in to renew before the due date, extend
the Loan Due value.
When Loan Due - Loan Date is greater than 14, don't
allow it to be renewed
again (perhaps by locking the Loan Returned field in the
form you provide to
edit the data). A Renewed field could be handy,
though. You allow the
clerk to enter data in a new row. After the row is
saved, lock all controls
except Loan Returned, Fined, and Renew -- and leave
Renew unlocked only if
its value is False (not renewed yet). If the clerk
clicks Renew, extend the
Loan Due automatically and then lock Renew so that it
can't be clicked
again.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table
Loan

Book Number = Primary Key in table Instance and ISBN
is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record
source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so
far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by
the
same student?

Many Thanks

Marko



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top