Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2015
    Posts
    5

    Answered: Saving Query Data to a Table

    Hi,

    I am working on an inventory system for a tool rental shop at my university. I have three tables: Details, Tools and Tracking. Details 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 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.

    2. The Tools table has a field 'Quantity' that I want to be updated when someone checks out a tool using the Tracking form/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,
    Jacob

  2. Best Answer
    Posted by ranman256

    "you wouldnt use dlookup, you'd scan the barcode into a field of the lookup form. After entry this would open a form, the form's query pulls the data from the textbox. The query is the 'dlookup'.
    This found record can be edited/etc.

    Quantity wouldnt be changed, a query that counts the checkIN/checkOUT would show the count."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    you wouldnt use dlookup, you'd scan the barcode into a field of the lookup form. After entry this would open a form, the form's query pulls the data from the textbox. The query is the 'dlookup'.
    This found record can be edited/etc.

    Quantity wouldnt be changed, a query that counts the checkIN/checkOUT would show the count.

  4. #3
    Join Date
    Nov 2015
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    you wouldnt use dlookup, you'd scan the barcode into a field of the lookup form. After entry this would open a form, the form's query pulls the data from the textbox. The query is the 'dlookup'.
    This found record can be edited/etc.

    Quantity wouldnt be changed, a query that counts the checkIN/checkOUT would show the count.
    Thanks for your reply ranman256.. I am relatively new to using Access; I did not understand the method you suggested. Could you please break it down for me?

  5. #4
    Join Date
    Nov 2015
    Posts
    5
    Help anyone?

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you wouldn't use dlookup fo this. according to:-
    https://support.office.com/en-us/art...b-bed10dca5937
    you can use the DLookup function to get the value of a particular field from a specified set of records
    for now forget about using a barcode scanner... usually they are implemented as a keyboard wedge (ie act as if they are a keyboard), so you can design your form using keyboard inputs.

    what you want to do is design the form so that you can search for the specified value.
    how you do that is up to you

    it could be apply a filter to an exisitng form
    it could be a dedicated form specifically for the purpose of handling barcode input.



    you need to be a bit cute in designing a barcode only form as you need a cheat sheet of command codes which tell the barcoide form what the type of input is going to be.
    what I have done int he poas tis have a cheat sheet something like
    ID description
    c010 search for following barcode
    c345 search for product
    c999 display product
    c631 enter new stock level
    nn negative
    n0 zero ' as in numbers:0
    n1 one ' as in numbers:1
    n2 two
    n3 three
    ....
    n9 nine
    n10 tens
    n100 hundreds
    1000 thousands
    1000000 lakhs

    scan the command cheet sheet, whcih identifies what operation you want to do AND also provides a means of data entry (if required)

    these days Im more likely to use a barcode reader with a touch screen
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •