Shopping basket - Append query from record in List box

[cgiAlexis]

Hello there,
I'm trying to create a library database in Access 2003 where the process of checking out books can also require a batch of books to be issued.

There are some limits to this project:

1. I can't download anything directly to the computer because of the network priveleges set up by the managers of our IT infrastructure. So primarily could you guys post code snippets please :)
2. This project has to be built in the Office 2003 suite because the IT guys won't update until my company buys an upgrade.

At the moment I have a form frmNewIssues set up so I can select the details of the patron and select the details of the book I want to issue ([Type], [Spine reference], [Title]) from combo boxes. The combo boxes fill in parts of a select query that the list box of all my books that I can issue is populated with since even if we have a title there are often multiple copies.

***
For your next trick; when the user double clicks a row in the presented books list box I'd like to somehow store that book ID (The Access id for the book which is a hidden bound column) and present it in any format to show the librarian which books they have selected for issue.
***

Eventually I will take the stored id's and run an append query where we join to book id's and customer data into an Issued table which contains which book was issued to whom.

Summary:
Require code for onDoubleClick a list box, to take the Bound column of [bookID] of the double clicked row and store it (In an array, temporary table or anything!) so that I can display the double clicked books in a list (In a separate list box or new form or something).

[zmbd]

cgiAlexis
Welcome to Bytes.
Sounds somewhat like a normalization issue.
I wrote a database back in the early 80's for a lending library based on the old index card files.

Basically, to build a book there were tables for authors, types, etc...
The table for the book would have the information for that title linked back in. I used the ISBN as the primary key back then as no two will duplicate.
Then there was a table for the "asset" this linked to the book tables and had an entry for each asset "in-stock"
So say we had 2 "where the wild things are..." then there was an entry for each book. For this table we used a simple autonumber as the primary key and what the assets were tracked by.
We had a table for the students, Fname, Lname, studentID, and ofcourse a primary key for tracking. The extra key was needed because the school student id would repeat after four years.
The final table had a primary key for the transaction, a link to the student primary key, a link to the asset primary key, and the check out date and a returned date.
A query pulled against a null/zerolength returned date for checked out assets and so forth.
Mind you this was a VERY long time ago on an old, old, old system; however, this is kind of what you will need for your application.


I do not want to discourage you in anyway from seeking help here, and please understand that what help we can give is yours; however, so as to not "re-invent the wheel" have you looked at the lending library templates available on the net for little or no cost?

These do tend to be fairly basic; thus may not meet your needs in which case, we'll certainly try to get you to where you want to be with the project.

The other issue we may have is that you are using ACC2003. Most of us have moved on to ACC2007/2010/2013 and there are some differences so bare with us if something doesn't quite work right the first time

opps.. gota run and have fun at the real job (^.-)

[cgiAlexis]

I went and read this - http://bytes.com/forums/feedback/919...hnical-threads - Mega facepalm. Everyone's efforts are appreciated on my threads :)

[ADezii]

An alternative Method of storing the IDs is to concatenate them to a String Variable, then extract them from an Array created by the Split() Function at the appropriate time.

1. Create 2 Form Level Variables to hold the String and the Array generated from the Split() Function.
   

   Private strBooks As String
   Private varBooks As Variant

2. Build the String of Dbl-Clicked Books in the DblClick() Event of the List Box.
   

   Private Sub lstBooks_DblClick(Cancel As Integer)
     strBooks = strBooks & Me![lstBooks] & ","
   End Sub

3. Play back the Books (Bound ID Columns) Dbl Clicked, then RESET the String Variable.
   

   Private Sub Command2_Click()
   Dim intCtr As Integer
   
   varBooks = Split(Left$(strBooks, Len(strBooks) - 1), ",")
   
   For intCtr = LBound(varBooks) To UBound(varBooks)
     Debug.Print varBooks(intCtr)
   Next
   
   strBooks = ""       'RESET
   End Sub

[cgiAlexis]

Thanks for your reply zmbd!

Started looking around for Access 2003 templates for a lending library, but I have to warn you and future posters that there are some extra caveats that prevent normal program development.

• I can only use the Office 2003 suite, the IT management company that my company uses currently only supplies 2003 and won't allow an upgrade until a new contract is signed.
• I cannot download any files to the network I am building the database on, the same management company doesn't allow downloads to protect security.

The database has already been normalised; One table for patrons with their ID autonumber primary key, one table for publications with the same, another for books again with the same but with the publication primary key stored against to book so essentially the book fields look like [BookID][PubID][CopyNumber].

All I need to do now is add entries to the Issue table where the bookID, patronID and date are filled in when we issue a new book. Creating a new issue adds the issueID autonumber primary key field so in the end we end up with:
[issueID](The bookID)(The patronID)(The date issued)

To re-iterate:

1. I can retrieve a single patronID from the combo boxes on the form and running a query to see that selected ID.
2. I can retrieve a single bookID from selecting an item on the list of my form and running a query to see the selected ID.

So now I need to turn those two select queries into a single append query, that I think I will allow me to add the specific information I want to the Issue table. Then I can retrieve all the pretty information like titles and surnames later on from those stored IDs.

All help is greatly appreciated, thank you! :D

[zmbd]

cgiAlexis
no, your database is not normalized if you haveing to use the method ADezii has given you. Don't get me wrong, ADezii has good code and of the solution works for you, great. However, you are going to find some limitations with storing the loaned assets in this mannor - especially when you go to do inventory.

Instead I suggest that you seriously consider a table such as I described for "the transaction" with a link to the student primary key, a link to the asset primary key, and the check out date and a returned date. You need an absolute ID for the asset and an absolute ID for the person checking out your asset. You would have a record for each asset checked out.
Your form would be quite simple.
Parent - recordset on the "student" has controls with the basic "student" information, PK, Fname, Lname, StudentID. Perhaps an unbound text box/combobox to help with searches, a popup details form....
Subform, parent on the student child on the FK_student, record set on the transaction table (actually a query so that we can use lookup fields for the human readable text).

Some of the concepts for the parent form are here and the articles should provide some insperation.
-filtering-

• Microsoft Access / VBA Insights Sitemap
• 45.How to select Records in an Access Subform from a Combo Box
  
• 46.Cascaded Form Filtering
• 55.Multiple Combobox Filter with Clear Option
• 168.Example Filtering on a Form.

[cgiAlexis]

Dear zmbd,

Your continuing support is very helpful in aiding me look at my database in terms of future improvements however I apparently haven't framed my original question correctly which has ended up with us talking across ways.

Currently I have the three tables that will make up the basis for this solution.

One Patron table with a patronID autonumber field, one Book table with the same again for bookID and one Issue table that takes patronID and bookID and sticks them in a record with an autonumber generated issueID.

At the moment I have an append query that takes the patron's details specified at the 'top' of the form with cascading boxes requerying away. Then at the 'bottom' of the form I have the same system of requerying combo boxes sorting out my book filters (Physical/Digital, Dewey reference, title). The append takes the returned bookID and patronID and slaps them straight into my Issues table.

However what I am looking for help on (and the reason I've selected ADezii's reply for now although not been able to test it because... my machine has died and it's taking all day to get the hardware replaced and files resyncronised) is the ability to store somehow the books that will eventually be applied via the Append query to my Issues table.

I'd like to show either on the fly or as a dialogue box before running the append which books have been selected for issue. In my library (A technical library) it is standard procedure to issue a batch of books with individiual issues being a rarity.

Thank you both and anyone else seeing this for helping me with this, in the meantime the rest of my database continues development (except today) but this is an issue that is very handy in some specific circumstances.

[zmbd]

There are a couple of ways to do that, and ADezii's method is one.
If you use the PF-CF concept, you would bring up the patron's information in the parent form.
The CF would then filter down to all of the currently assigned materials. If you use a query and have a returned date field for the CF recordset, then you could return only those materials without a returned date value.
Adding the materials to the CF with a Lookupfield would be somewhat interesting in that the recordsource for the control would have to reflect the action so that you didn't double assign one of the collection. This could be triggered by the after update event...
You're still doing a "batch" at time this way. The query for the recorsource for the lookup field would be the only tricky aspect.

[cgiAlexis]

Indeed,

I've not tried out ADezii's method yet still because time constraints meant that single-issue (Append query) and batch-transfer (Update query) methods are 'good enough' for now.

But I can confirm that so far with the one form you can see all books that don't have a patronID against them in the Issues table.

Selecting a book gets the bookID via bound column and along with the patronID found by using cascading combo boxes at the 'head' of the form enters the data into Issues via an Append query when you press the 'Issue book' command button.

Avoiding double issues is done by requerying the book list, since the list excludes books with entries in Issues then they dissapear each time you press the 'Issue book' cmd button.

After I set up detection of books that are out of date tomorrow I think I'll try and sqeeze in zmbd's and ADezii's solutions and maybe try using a temporary table to store book ID's in using 'onDoubleClick' (Add another exclusion so books in the temporary table and in the Issue table) before taking bookIDs from the temporary table.

I just wish there was a 'neater' way of doing this ;)

Again, thank's everyone especially zmbd for all your help.