473,320 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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).
Sep 9 '13 #1
8 1721
zmbd
5,501 Expert Mod 4TB
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 (^.-)
Sep 9 '13 #2
I went and read this - http://bytes.com/forums/feedback/919...hnical-threads - Mega facepalm. Everyone's efforts are appreciated on my threads :)
Sep 9 '13 #3
ADezii
8,834 Expert 8TB
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.
    Expand|Select|Wrap|Line Numbers
    1. Private strBooks As String
    2. Private varBooks As Variant
  2. Build the String of Dbl-Clicked Books in the DblClick() Event of the List Box.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub lstBooks_DblClick(Cancel As Integer)
    2.   strBooks = strBooks & Me![lstBooks] & ","
    3. End Sub
  3. Play back the Books (Bound ID Columns) Dbl Clicked, then RESET the String Variable.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Command2_Click()
    2. Dim intCtr As Integer
    3.  
    4. varBooks = Split(Left$(strBooks, Len(strBooks) - 1), ",")
    5.  
    6. For intCtr = LBound(varBooks) To UBound(varBooks)
    7.   Debug.Print varBooks(intCtr)
    8. Next
    9.  
    10. strBooks = ""       'RESET
    11. End Sub
Sep 10 '13 #4
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
Sep 10 '13 #5
zmbd
5,501 Expert Mod 4TB
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-
Sep 10 '13 #6
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.
Sep 10 '13 #7
zmbd
5,501 Expert Mod 4TB
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.
Sep 10 '13 #8
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.
Sep 11 '13 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Ian N | last post by:
Hi, I've been working on a PHP Shopping Basket and have hit a problem. I wanted update the DB with the contents of the Array which forms the basket. This isn't a problem, however i'm unsure how...
4
by: Richard Pain | last post by:
OK - I have a shopping basket with data in a database. Once the person has successfully completed their order I want to be able to send them an email with the products ordered roughly in the...
2
by: Paul Wagstaff | last post by:
Hi there I have 2 tables: tblAccuracy & tblClearance Users add new records to tblAccuracy using frmRegister. Under specific conditions I need to append the current record from frmRegister into...
2
by: Ray Holtz | last post by:
I have a form that shows a single record based on a query criteria. When I click a button it is set to use an append query to copy that record to a separate table, then deletes the record from the...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
6
by: AA Arens | last post by:
Hi, I have a database with 2 main forms. Contacts and companies. I share the base with two others via LAN. On the companies form I have buttons to navigate throught the records (>400). We are...
4
by: Rob Meade | last post by:
Hi all, I need to implement a simplistic shopping basket/checkout thingy... I kinda threw something together, but its not keeping more than one item in the session... I'm basically loading...
3
by: Samuel Shulman | last post by:
Hi I need to implement a shopping basket for my e-commerce website Scenarios are as follows 1. Website to offer shopping basket for all visitors including those who didn't log 2. Website...
4
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to...
4
by: Adam Tippelt | last post by:
Situation: When a user 'confirms' that they want to save the information they've inputted, I need to append the database records from a temporary table to a different table. Problem: The...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.