Page 1 of 6 123456 LastLast
Results 1 to 15 of 79

scan medicine inventory system

  1. #1
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42

    scan medicine inventory system

    Hi,

    I'm a pharmacy student trying to set up a scan logging system. I have a barcodescanner, computer and label software.
    I have a acces (database) with information about medicines, with all the information about the medicine in one row.
    So like this:
    aspirin| product code 12345| barcode 54321| white tablet | etc.

    i want to make a logbook for workers here to be able to work in.
    I want them to be able to scan a product with our barcode scanner (it scans and ''types'' the number and then presses enter).
    After scanning i want the logbook to recognise the code and give an error if not recognised, but if its recognised i would want to have the file automatically enter all the data from the database in the logbook.



    the ideal situation would be if the worker using this logbook would not be able to adjust anything except adding the expiracy date and batchnumber of the product, aswell as their initials.
    so the output would be like this:

    batchnumber | date| workerinitials| aspirin| product code 12345| barcode 54321| white tablet |

    i already have made the database, a form and i have a logbook table.
    I'm kinda struggling with making a system that will paste the information out of the database in my logbook.
    do i need to use a query for this? or vba code? i tried both but cant get it to work properly, even after watching dozens of videos. in excel i made this system with vlookup combined with vba code, but my labeling software doesnt really like excel so im trying access now.

    Can anybody help me in the right direction?

    Attached Files Attached Files

  2. #2
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    You shouldn't "paste" anything from the database ("productdatase" table) into the logbook ("scanlogboek" table). You simply need to link them by the unique barcode in a query to get the data from the "database" for the new scan. So the log table should not replicate any fields from the database table, it should only have the fields you want the users to modify (initials, expiration date, batch #, etc.) plus the foreign key barcode.

    Cheers,
    Vlad

  3. #3
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    Click image for larger version. 

Name:	Naamloos.png 
Views:	61 
Size:	44.6 KB 
ID:	34295like this? shouldnt i be enforcing referential integrity? i cant do this with these fields..
    i'm not getting any output..
    Click image for larger version. 

Name:	Naamloo33s.png 
Views:	53 
Size:	23.8 KB 
ID:	34296

  4. #4
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Kinda, have a look at the updated file (it doesn't have the workers table). You don't want to link that in the query;instead change the control type of the initials field in your form from a textbox to a combobox and use the workers table as its rowsource to add the initials.

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #5
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    okay thanks im getting somewhere now:

    im having issues with my save button in ''ontstripformulier'' it doesnt save nor add the record.

    one table is a database for productinfo ''overzichtstabel''
    the other ''ontstriplogboek'' is a table where the form adds data about how many products are entered and how many are lost among some other fields.

    im trying to make a form that will enter this information and after that run the ''updatequery'' to update ''labeloutput''
    then my labelsoftware prints a label for each new record automatically.

    any help?
    Attached Files Attached Files

  6. #6
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Not sure I follow you, you have the form opening in "data entry" mode, I have added a record without any problems. How and when do you want to run the "updatequery" (which in fact is an append query, not an update)? You should look into using VBA instead of the macros, it is a lot more powerful and easier to read and understand.

    I have updated your form to bind the combobox to the initials field in the table.

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    thanks for the fix!

    id like the query to run after i press the save button on a second form that appears when the first form is filled in.
    but it doesnt save the data from the ''verlies'' textbox i added to the second form. the query does appear to be doing its job now when added to the savebutton.

    why is this?


    thanks again in advance
    Attached Files Attached Files
    Last edited by farmi; 06-06-2018 at 07:18 AM.

  8. #8
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Why do you need the second form? Have a look at the first form where I added your new field (verlies) and the button to run the query. Please note that you are missing a field in the table for Aantaletiketten - I changed it to a combo box from the list box you had, notice the green warning in design view in the upper left corner.

    In your format (with two forms) you were adding a new record in the first one (it was opened in Data Entry mode), but the second one for the verlies was opened in regular Edit mode so it was always displaying the first record; to make it work with two forms you would need to find the last added record (from the first one that you just closed with the square save button) in order to edit the verlies field.

    Cheers,
    Vlad
    Attached Files Attached Files

  9. #9
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    its because of the proces:
    the user will first enter data for the product, then unpack the products from their containers. some products will get lost or break, ''Verlies'' means ''loss''.
    1. I want the user to be able to first check the barcode with the form and enter data. Then unpack the products
    2. Then enter the loss in a new form.
    3. labelsoftware prints label from labeloutput

  10. #10
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    edit:doublepost

  11. #11
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    I see, but you can still do it with one form. Have a look at the updated file and notice the background color changes from the new to saved record. If you want two forms you will need to abandon the macro behind the save function and use VBA to save record then open the new form and go to the record with the highest ID (if you use gotolast you could go to the wrong record if sort order changes). Also, on the verlies form the way it is right now you should add some other fields (maybe lock them if you don't want them to be edited) to help identify what record are you adding "verlies" for.

    Cheers,
    Vlad
    Attached Files Attached Files

  12. #12
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    hey,
    Im not sure what i want with the 2 step form yet, i included it in 1 form for now but ill see what ill do when i put the form into practice.

    on another note:
    im having trouble displaying yes/ no in text format when running the appendquery. i just get 0 or -1 instead of ''ja'' or ''nee'' (yes/no in dutch)
    same goes for showing the initials of a ''medewerker'' instead of the id number.
    any idea why this isnt working?
    Attached Files Attached Files

  13. #13
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Sorry but can't look at your db as you have saved it in Access 2016 format and I only have 2010. Please save it in an older format and repost it.

    Cheers,
    Vlad

  14. #14
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    For the Ja/Nee issue you need to replace the straight reference to the fields in the query with calculated expressions (see below in red):
    Code:
    INSERT INTO LabelOutput ( Geneesmiddel, Uiterlijk, Cassettenummer, AdviesOntstrippenStofafzuiging, AdviesSTSOnderStofafzuiging, HoudbaarheidNieuw, Lichtgevoelig, Zwangerschapsbeleid, Opiumwetregistratie )
    SELECT Ontstriplogboek.*, Overzichtstabel.Geneesmiddel, Overzichtstabel.Uiterlijk, Overzichtstabel.Cassettenummer, Overzichtstabel.AdviesOntstrippenStofafzuiging, Overzichtstabel.AdviesSTSOnderStofafzuiging, [Overzichtstabel]![HoudbaarheidKoertGDS]*30+[Ontstriplogboek]![Ontstripdatum] AS HoudbaaheidNieuw, Overzichtstabel.Lichtgevoelig, Overzichtstabel.Zwangerschapsbeleid, iif(Overzichtstabel.Opiumwetregistratie=0,"Nee","Ja") As Opium
    FROM Ontstriplogboek INNER JOIN Overzichtstabel ON Ontstriplogboek.BarcodeScanned = Overzichtstabel.Barcode
    WHERE (((Exists (SELECT * FROM LabelOutput WHERE LabelOutput.InternId = Ontstriplogboek.InternID ))=False));
    For the initials you need to change the combo-box on the form - go to the data tab of the properties sheet and change the bound column from 1 to 2.

    Cheers,
    Vlad

  15. #15
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    42
    Quote Originally Posted by Gicu View Post
    Sorry but can't look at your db as you have saved it in Access 2016 format and I only have 2010. Please save it in an older format and repost it.

    Cheers,
    Vlad
    hmm its giving me an error that i cant save it because there are items in it that only work in 2016.

    @ the code you typed, you sure its correct? ''iif'' instead of ''if''? 'havent tried yet though, ill try it tomorrow when im back at my pc.

    thanksagain

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inventory System in Access
    By Antonww in forum Access
    Replies: 6
    Last Post: 11-29-2017, 05:35 AM
  2. Need Help:Basic Inventory System
    By shazi9b in forum Access
    Replies: 5
    Last Post: 09-25-2013, 11:48 PM
  3. An inventory system
    By stew8908 in forum Access
    Replies: 3
    Last Post: 05-08-2012, 04:50 PM
  4. Inventory System
    By Rawb in forum Database Design
    Replies: 8
    Last Post: 01-05-2011, 06:26 AM
  5. Replies: 1
    Last Post: 12-30-2008, 07:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums