Executing two queries at the same time

[Knoxville]

I have now created two queries. First is a delete query that has has unique BorrowerID in its criteria. This allows the user to select which to delete. I have also made an update query to update the items borrowed when the delete query is run.
I want to run these queries at the same time, so when I choose the record to be deleted, the other query also runs and updates the item that has been borrowed.
How do I do this

 

[Jerry Stoner]

Put a command button on your form. In the click event put:
DoCmd.OpenQuery ("FirstqueryNameHere")
DoCmd.OpenQuery ("SecondqueryNameHere")

If you dont want user to see the You are about to blurb add
DoCmd.SetWarnings (False) at the beginning and DoCmd.SetWarnings (True) at the end.


[This message has been edited by Jerry Stoner (edited 05-04-2002).]

 

[Knoxville]

Thanks Jerry Stoner but this is not quite what I need. The problem I have is that my first query 'qryDelete' asks the user to specify which record to delete. I want my second query 'qryUpdate' to perform the its operation (unticking a box named Borrowed) upon the record specified in the first query.
Can this be done?

 

[Jerry Stoner]

Yes it can. Assuming your form is at the record you want to delete/append/modify put [Forms]![YourFormName].[YourField] in the criteria for the query and it will perform the action you want based on your current record.If you are using a subform and Ill bet you are the syntax is
[Forms]![YourMainForm].[YourSubform].[Form].[YourField]
I believe this is a continuation of a prior post(keep them together, it will be easier for us all).
If you still have trouble post your Form Names, subform names, query names amd revelent feilds and I or someone else will be able to help you.
Jerry

 

[Knoxville]

Still having problems!
My form name is frmBorrowDetails and this has a subform called subfrmBorrowDetails. In the subform there are fields such as CustomerID, BookID, Borrowed(yes/no), BorrowedID, StartDate, EndDate.
I have made two queries. qryDelete has BorrowedID as its field and I have put [Which] in the criteria so that I can choose which to delete.
My other query is qryFalse. This is a update query. Borrowed(yes/no) is its field and I have set its Update To:False. This unticks the Borrowed tick box.
What I'm trying to do is run these queries together. The qryFalse would run first unticking the Borrowed field and then qryDelete would run deleting the loan from record.
Thanks

 

[Jerry Stoner]

You only need 1 query.qryDelete.Have you put a command button on your form yet? In the clickevent put:
DoCmd.OpenQuery("qryDelete")
Me.[Forms]![frmBorrowDetails].[subfrmBorrowedDetails].[Form].[Borrowed].Value = 0

In the criteria for BorrowedID in your query put:
[Forms]![frmBorrowDetails].[subfrmBorrowDetails].[Form].[BorrowedID (OR WHATEVER YOUVE NAMED THAT FIELD)]

I am a little confused about your structure though.Wouldnt it be better to have a Book table and a borrowed table linked by bookID?
What Im getting at is it seems to me your borrowed checkbox should be in your main form not your subform. When you delete the subform record you should delete the whole record and flag the Book in the mainform as not borrowed. Better yet dont delete the record but flag it as returned with a checkbox. That way you keep a record of who borrowed what book when and not a snapshot of the current status as you are now doing. Anyway just a thought.
HTH


[This message has been edited by Jerry Stoner (edited 05-05-2002).]

 

[Jerry Stoner]

Rereading your post what I suggested probably wont work. The subform is in continous or datasheet view right? If its in form view it will work .However I think DBL put you in the right direction in your other post.See if this makes sense to you:
tblBooks
BookID - Primary Key
Title
Author
ISBN
BookDescription
etc.
OnLoan - Yes No


tblCustomer
CustID - Primary Key
CustFirstName
CustLastName
CustAddress
City
etc.

tblLoans
LoanID - PK
BookID - fk to tblBooks
CustID - fk to tblCust
StartDate
EndDate
Active - Yes/NO

frmBooks is your Main form and is based on a query with on loan set to True.

sfrmLoan is your subform and is based on a query that combines tblCust and tblLoans.
The query has Active criteria set to True so you only see current loan data.

You now have a form that shows only currently checked out books and the current loan status.

If you want to check out multiple items to a customer at the same time set up a new form frmCust with all relevant customer info and a subform frmBookCheckOut with the recorsource a query combining tblBooks and tblLoans. You need BookID and Title from tblBook (this will be a combo to select from)and all fields from tblLoans.Now you can go to a Customer - or enter a new one then select as many Books as you want to checkout. The advantages to deleting a record should be clear. You can at any time find all books ever checked out by a customer - all customers who ever checked out a book etc et el. You have a permanent record of all transactions but still dont have to wade through a large amount of records to see current checkout status.
The method you are currently trying to implement doesnt make sense because you 1) lose all historical data and 2)you are deleting the CustomerID and whatever else but still have a record that says Borrowed = No. What purpose does that serve?
HTH - Jerry


[This message has been edited by Jerry Stoner (edited 05-05-2002).]

 

[Knoxville]

Thanks for your advice Jerry, I will trry your idea out and post back if I need any more help.
Knoxville

 

[Knoxville]

Thanks Jerry. Your idea sounds much better and I have created the tables. However being a novice access user I am having difficulty implementing the forms you suggested. Could you please give a more descriptive explanation of how to create them. Any help would be appriciated as the idea sounds much better than mine.
Knoxville

 

[Jerry Stoner]

Ok first make a query qryBooks based on tblBooks. Put true in the criteria for OnLoan.
Let the wizzard build a form based on that query.
Next make your qryCustLoans as I described in my last post.Again let the wizzard make the form for you. Youll end up with a form frmCust with a subform frmLoans.
Next open frmBooks and add frmCust as a subform. Youll get a three tiered form Books, Cust, and Loans.
As long as you set up your queries right the wizzard will take care of forms for you including linking.If you get that figuring out frmCheckout should be a snap.
Good luck and post back if needed.

 

[Jerry Stoner]

Knoxville- put together a quick and dirty demo for you. Give me your email and Ill send it to you.
Are you running 97 or 2000?

 

[Knoxville]

Thanks Jerry thats great. I'm running 97 and e-mail address is:
paul.fairy@virgin.net

 

[Jerry Stoner]

Check your in box

 

[Knoxville]

Just got it. Thanks again Jerry!
Knoxville