deleting records

[ccs2]

If I have a dbase that issues items for loan such as DVD's or books how can I prevent the same item being loaned out more than once.

I have four tables:

tblCustomer, tblLoans, tblLoanList and tblDVD's

Do I need something to delete these records after they have been assigned?

CCS

 

[Remou]

I think this depends on your set-up. You may have a loaned date, so the "items to loan" form would not show that record, or would prevent a loan based on the date existing. You seen to have a table for items that have been loaned, so a form based on a query that included only the "no matches" might be more suitable.

 

[ProgramError]

Remou is right. You need to query whether an item is available on the days you want to loan it or with reverse logic you want to check a particular period and display all items which are going to be available.



Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.

 

[ccs2]

I do have a form based on a select query with the following:

From tblLoanID: LoanID, Date, CustomerID (foreign to tblCustomer)
From tblCustomer: FName, LName
From tblLoanList: LoanListID, Product (foreign to tblProduct)
From tblProduct: Title, Quantity, Price

How do I do the ‘no matches’?

CCS

 

[ProgramError]

open a new query
add all the fields you want to display
put in the criteria field of the date something like
BETWEEN [enter start date] AND [enter end date]
or if it is a one day loan then use
[please enter date]
put in the criteria field of the loanlistid or product
[please enter ID]
or
[please enter product name]
run the query

if it works then just create a form with the query as its record source to show the results. If you want to show when a product is available then use instead.

NOT(BETWEEN [enter start date] AND [enter end date])

This is a very simple query method.




Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.

 

[ccs2]

Thanks Ian, I will give this a go.

Cavan Stewart (UK)