none
Saving Data from a Query that uses DLookup RRS feed

  • Question

  • Hi,

    I am working on an inventory system for a tool rental shop at my university. I have three tables: DetailsTools and TrackingDetails has all the basic information about all the students (Name, Barcode, ID etc.). Tools has the 'Names' of all the tools available at the shop and their 'Quantity'. Tracking is meant to create a log of rental activity. Tracking contains fields like 'ToolNeeded' (combo box based off Tools table), 'StudentName', 'ID', 'CheckoutTime' etc.

    1. I am trying to scan a barcode (from a student ID card) and input the barcode to a form (ToolTracker) based off a query based off the Tracking table. However, once the barcode field has been filled in, I pull other fields like Name, ID etc. corresponding to the entered barcode from the Details table using a DLookup. The form and the query successfully pull the required data from the Details table, but I want all the data from all the barcode scans to be saved to the Tracking table. Could someone suggest a way to save the data from a query to a table (Tracking) so that a new data entry is automatically created in this table whenever a barcode is scanned or a tool is rented out.

    As an alternative, I used an Append Query which saved all the data I needed to the table I specified. However, now I am not able to link the ToolTracker form to the query as it is now an action query. Is there a workaround for this?

    2. The Tools table has a field 'Quantity' that I want to be updated when someone checks out a tool using the Trackingform/query. Could someone please suggest a method to do this? Is there some other function in Access that is similar to DLookup, but writes/updates data in a different table instead of looking it up? 

    I need this urgently, and I would really appreciate any help I can get. Please let me know if some part of my request is not clear. Thanks a lot.

    Best,

    Midhun

    Monday, January 11, 2016 12:34 AM

Answers

  • Let's start with the basis of the database's logical model.  What you have here are two entity types Students and Tools which should be represented by tables, in broad outline, as follows:

    Students
    ….Barcode (PK)
    ….FirstName
    ….LastName

    Tools
    ….ToolID  (PK)
    ….ToolName
    ….InitialQuantity

    There is a many-to-many relationship type between these which is represented by a table which resolves the relationship type into two one-to-many relationship types.  This is you Tracking table:

    Tracking
    ….Barcode (FK)
    ….ToolID  (FK)
    ….DateTimeCheckedOut
    ….DateTimeReturned
    ….QuantityCheckedOut

    The primary key of this table is a composite one made up of the Barcode, ToolID and DateTimeCheckedOut.  Give the QuantityCheckedOut column a DefaultValue property of 1.  You do not need to store any other student data such as the student's names in this table, and should not do so as it would introduce redundancy and put the table at risk of update anomalies.  The Barcode column in this table maps to a row in Students, so to return the values from columns in Students is merely a matter of joining the tables in a query.  The same is true of the tool.

    Create a form based on the Tracking table and set its DateEntry property to True.  The form will open at an empty new record into which you can scan the barcode.  Bind a combo box to the Barcode column and set it up to hide the BoundColumn and show the student names.  Bind another combo box to the ToolID column and set it up similarly to show the tool name.

    In the form's BeforeInsert event procedure assign the current date/time to the DateTimeCheckedOut column with:

    Me.DateTimeCheckedOut = Now()

    There is no need to write data to the table, this is done automatically by the use of a bound form set up in the above way.  

    The InitialQuantity column in the tools table records the total number of each tool, both those currently in stock and those currently checked out.  As further tools are acquired or written off this number can be increased or decreased.  The current stock in hand need not be stored as a value in a table, but can be computed by subtracting the sum of the quantity of the tool currently checked out from the initial quantity for that tool, which can be done with a query like this:

    SELECT ToolName,
    InitialQuantity - SUM(QuantityCheckedOut) AS StockInHand
    FROM Tools INNER JOIN Tracking
    ON Tools.ToolID = Tracking.ToolID
    WHERE DateTimeReturned IS NULL
    GROUP BY ToolName, InitialQuantity;

    This is a very simple model for an inventory application.  For a more developed example take a look at Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the basic methodologies for a small inventory database, and includes provision for stock-takes etc as well as day to day transactions.  A fully developed commercial inventory management system is very much more complicated of course.

    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:44 AM
    Thursday, January 14, 2016 10:23 PM
  • for item 1 what you do is this:

    in design view of your tracker screen - the bar code field (that you are scanning into) - right click and change that to a combobox

    then in its properties; make it's control source the table that has all the student info; add all the fields you want; also in the properties of this same bar code field you will need to change the field count, field sizes etc....so that when you manually trigger the combobox and open it - you see all the data.....

    but you want to see this info on screen, not just when you manually trigger the combo box - and so what you do is put in all unbound text boxes and make their controls to be a column of the bar code field i.e.

    =me.barcodetextbox.column(2)

    then whenever you scan in a valid bar code (valid meaning it matches one in the student table that is its control source) all these unbound text boxes will display all the info that you seek to view.....

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:45 AM
    Monday, January 18, 2016 7:26 PM

All replies

  • 2. The Tools table has a field 'Quantity' that I want to be updated when someone checks out a tool --

    Do not use a Quantity field.  Use a separate table to tracking tools out and in so there is an actual record of the transaction including date in.


    Build a little, test a little

    Tuesday, January 12, 2016 12:41 AM
  • Hi, Thanks for the help. Any suggestions for part 1? Is it possible to link a form to an action query?
    Wednesday, January 13, 2016 10:10 PM
  • Not know bar code interface.

    Build a little, test a little

    Thursday, January 14, 2016 3:33 AM
  • I got the bar code part figured out.. Do you know if I can save data from a query using dlookup (to gather data from two separate tables)?
    Thursday, January 14, 2016 8:45 PM
  • Let's start with the basis of the database's logical model.  What you have here are two entity types Students and Tools which should be represented by tables, in broad outline, as follows:

    Students
    ….Barcode (PK)
    ….FirstName
    ….LastName

    Tools
    ….ToolID  (PK)
    ….ToolName
    ….InitialQuantity

    There is a many-to-many relationship type between these which is represented by a table which resolves the relationship type into two one-to-many relationship types.  This is you Tracking table:

    Tracking
    ….Barcode (FK)
    ….ToolID  (FK)
    ….DateTimeCheckedOut
    ….DateTimeReturned
    ….QuantityCheckedOut

    The primary key of this table is a composite one made up of the Barcode, ToolID and DateTimeCheckedOut.  Give the QuantityCheckedOut column a DefaultValue property of 1.  You do not need to store any other student data such as the student's names in this table, and should not do so as it would introduce redundancy and put the table at risk of update anomalies.  The Barcode column in this table maps to a row in Students, so to return the values from columns in Students is merely a matter of joining the tables in a query.  The same is true of the tool.

    Create a form based on the Tracking table and set its DateEntry property to True.  The form will open at an empty new record into which you can scan the barcode.  Bind a combo box to the Barcode column and set it up to hide the BoundColumn and show the student names.  Bind another combo box to the ToolID column and set it up similarly to show the tool name.

    In the form's BeforeInsert event procedure assign the current date/time to the DateTimeCheckedOut column with:

    Me.DateTimeCheckedOut = Now()

    There is no need to write data to the table, this is done automatically by the use of a bound form set up in the above way.  

    The InitialQuantity column in the tools table records the total number of each tool, both those currently in stock and those currently checked out.  As further tools are acquired or written off this number can be increased or decreased.  The current stock in hand need not be stored as a value in a table, but can be computed by subtracting the sum of the quantity of the tool currently checked out from the initial quantity for that tool, which can be done with a query like this:

    SELECT ToolName,
    InitialQuantity - SUM(QuantityCheckedOut) AS StockInHand
    FROM Tools INNER JOIN Tracking
    ON Tools.ToolID = Tracking.ToolID
    WHERE DateTimeReturned IS NULL
    GROUP BY ToolName, InitialQuantity;

    This is a very simple model for an inventory application.  For a more developed example take a look at Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the basic methodologies for a small inventory database, and includes provision for stock-takes etc as well as day to day transactions.  A fully developed commercial inventory management system is very much more complicated of course.

    Ken Sheridan, Stafford, England

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:44 AM
    Thursday, January 14, 2016 10:23 PM
  • >>>I got the bar code part figured out.. Do you know if I can save data from a query using dlookup (to gather data from two separate tables)?

    DLookup function can get the value of a particular field from a specified set of records (a domain).
    If I don't misunderstand, you could refer to below:

    relData = DLookup("FieldName", "TableFirst", "ID=" & DLookup("ID", "TableSecond", "your criteria"))


    For more information, click here to refer about Application.DLookup Method (Access)

    Saturday, January 16, 2016 9:10 AM
  • for item 1 what you do is this:

    in design view of your tracker screen - the bar code field (that you are scanning into) - right click and change that to a combobox

    then in its properties; make it's control source the table that has all the student info; add all the fields you want; also in the properties of this same bar code field you will need to change the field count, field sizes etc....so that when you manually trigger the combobox and open it - you see all the data.....

    but you want to see this info on screen, not just when you manually trigger the combo box - and so what you do is put in all unbound text boxes and make their controls to be a column of the bar code field i.e.

    =me.barcodetextbox.column(2)

    then whenever you scan in a valid bar code (valid meaning it matches one in the student table that is its control source) all these unbound text boxes will display all the info that you seek to view.....

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:45 AM
    Monday, January 18, 2016 7:26 PM