Page 2 of 9 FirstFirst 123456789 LastLast
Results 16 to 30 of 132
  1. #16
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No need for file now. Have a look at the IIf function: https://support.office.com/en-us/art...0-647539c764e3

    Cheers,


    Vlad

  2. #17
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    So all of that works now.

    Im trying to add a login form using this: http://accesshosting.com/create-login-form-ms-access
    not quite working yet, not sure how to add the macro to the button. i already assigned it but its not functioning.
    what should i do with the textboxes? assign them to the columns? wouldnt that make the form edit the name in the colum? i want to prevent that ofcourse.
    Attached Files Attached Files

  3. #18
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here it is. You changed the table name and field name (password from userpassword in the code). Please note that the code makes reference to a form frmUserInfo that you do not yet have (in that one you do want to bind the fields from tblUser to allow them to change their password).

    On my site I have a free download that allows you to customize the look and functionality of an Access database for certain groups (levels) of users: http://forestbyte.com/ms-access-util...access-levels/. It might be an overkill for you but I thought I'll just point it out.

    Cheers,
    Vlad
    Attached Files Attached Files

  4. #19
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Oh great thanks, it all works now again. i changed some code and tabelnames.
    your system looks really nice, but indeed i think it would be overkill for my program. my current setup works now so thats fine.

    So moving on:
    -would it be possible to create some sort of error when a barcode is not found in my database? how would i do this or what is the term i need to search for online?
    - can i add multiple barcodes per record? should i just add some colums with additional barcodes?
    - sometimes i want to inactivate a record, so that it still is in the table but not active. Why? because sometimes the productbrand changes but it still is the same product.
    so it would be great to have multiple products but only 1 active. example:

    product 1 brand a several barcodes, active record, will scan correctly
    product 1 brand b several barcodes inactive record, will give error when scanned
    product 1 brand c several barcodes inactive record, will give error when scanned
    product 1 brand d several barcodes inactive record, will give error when scanned


    - is it possible to color a icon or something that will show green when scan is recognised and red when scan isnt recognised?

    - i changed the date format of houdbaarheidorigineleverpakking to month and year only, as this is the only info displayed on the product (its the expiracy date).
    is it possible to get a datepicker that only uses month and year? the datepicker in the form uses day month year as a format. (and if stored just use the first day of the month as a value)

    thanks again!
    Attached Files Attached Files

  5. #20
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    -would it be possible to create some sort of error when a barcode is not found in my database? how would i do this or what is the term i need to search for online?
    you would need to use dlookup or dcount to look for the barcodes
    - can i add multiple barcodes per record? should i just add some colums with additional barcodes?
    I wouldn't add additional fields in the same table; instead create a new join table holiding ProductID|Barcode|Active(Yes/No)
    - sometimes i want to inactivate a record, so that it still is in the table but not active. Why? because sometimes the productbrand changes but it still is the same product.
    so it would be great to have multiple products but only 1 active. example:

    product 1 brand a several barcodes, active record, will scan correctly
    product 1 brand b several barcodes inactive record, will give error when scanned
    product 1 brand c several barcodes inactive record, will give error when scanned
    product 1 brand d several barcodes inactive record, will give error when scanned
    See above new table with new Active field (Yes/No)
    - is it possible to color a icon or something that will show green when scan is recognised and red when scan isnt recognised?
    first question asks for error if not present - use the same dlookup formula as a conditional formating expression for the barcode field font color
    i changed the date format of houdbaarheidorigineleverpakking to month and year only, as this is the only info displayed on the product (its the expiracy date).
    is it possible to get a datepicker that only uses month and year? the datepicker in the form uses day month year as a format. (and if stored just use the first day of the month as a value)
    the date picker picks a date, there is no month picker. And you should keep it as a date (first of the month) in case you will need to do calculations (how many months to expiration date) - if you change it to month-year it will be harder. Simply apply Format("MMM-YYYY") to your expiration date field to present it as Jun-2018.

    I haven't touched your new db. Post back if you need help with any of these.

    Cheers,
    Vlad

  6. #21
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    for the dlookup i tried this after googling:

    Code:
    Private Sub Knop296_Click()
    BarcodeScanned = Nz(DLookup("Barcode", "Overzichtstabel", "Barcode= '" & Nz(Me.BarcodeScanned.Value) & "'"))
    
    
    If BarcodeScanned = "" Then
    MsgBox "Barcode  niet gevonden!"
    End If
    
    
    If Not BarcodeScanned = "" Then
        MsgBox "Barcode gevonden"
    DoCmd.RunCommand acCmdSaveRecord
    End If
    End Sub
    not only does it create a record anyway, it doesnt give the errors. And it saves the record either way even when barcodes dont match
    do i really need the NZ code by the way?


    - ill leave the datepicker as it is, its functional enough now with the format i picked.

    - Could you assist me with the join table? i dont really understand how to start. ive started looking at videos and tutorials but they arent very clear to me.
    ive created a table using a make table query like this:
    Click image for larger version. 

Name:	Naamloos.png 
Views:	36 
Size:	80.0 KB 
ID:	34485

    -do i need to link these records to the product id primary key from overzichtstabel?
    -where do i enter additional barcodes for the same product/record?
    -how do i add diferrent brands of the same product? (these different brands of the same product share a common code called a ''gpkcode'')
    just to clarify:
    1 product can have several barcodes, but it has 1 ''knmpnummer'' code
    several brands can make these products, all with their own different ''knmpnummer'' code
    1 these products of the same type and knmpnumber all share a ''gpkcode''

    considering these are medicines, the gpkcode is essentially which medicine is prescribed, knmp number refers to the product of that specific brand. one brand can have different barcodes(sometimes they import medicine from another country and have different barcodes)

  7. #22
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    ok i fixed the dlookup code myself:
    Code:
    Private Sub Knop296_Click()BarcodeScanned = Nz(DLookup("Barcode", "Overzichtstabel", "Barcode= '" & Nz(Me.BarcodeScanned.Value) & "'"))
    
    
    
    
    
    
    If Not BarcodeScanned = "" Then
        MsgBox "Barcode gevonden"
    If Not BarcodeScanned = "" = True Then DoCmd.RunCommand acCmdSaveRecord
    Else
    MsgBox "Barcode  niet gevonden!"
    End If
    
    
    End Sub
    this works . EDIT: no it doesnt work... still makes a record even if incorrect barcode.

    still struggling with the join table if anybody could help me please.

    heres the db file: https://drive.google.com/open?id=1Rp...yLoe_UXL9LbjuD
    Last edited by farmi; 06-20-2018 at 07:47 AM.

  8. #23
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You are contradicting yourself when you say you don't want a new (not-existing barcode) not to be entered (create a new record) and at the same time color it red. I think you need to allow to enter it then open another form to add it to the new Barcodes table (in the new form add a combobox to select the knmpnummer).
    I have modified your barcodes table to remove the extra fields. I also modified the append query (but not used it in the code) so you could see the new design. I changed the product form and table by removing the Barcode field from the table and replacing it on the form with a subform based on Barcode and linked by
    knmpnummer.

    Hopefully these changes will give you enough info to see how the new design needs to be implemented. I noticed the form that checks the barcodescanned has some controls which are missing from its record source, you want to correct that.

    Cheers,
    Vlad
    Attached Files Attached Files

  9. #24
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Ive thought about it and the red coloring is not necessary, as now i have a error message instead of the red color.
    i just want to block entry of non existing barcodes in this form.

    ill have a look at your adjustments.
    thanks

  10. #25
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    the reason i want to block unrecognised barcodes is because i dont want the user to be able to enter them in the log. this way the user must first find an admin that will fill out a form with product info and barcode. after this the user can scan the barcode and log the product. the reason i want an admin in this proces is because the admin can check if the data is entered correctly because the admin is responsible for the log. Most users will be ''untrained'', so we wont allow them to enter new products/barcodes

    i ditched the code i used previously and used a combobox for barcode entry

  11. #26
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Agree, combo box is great for that.
    Cheers,
    Vlad

  12. #27
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Could you please assist me on how to assign several barcodes per producttype and several identical products from different brands per producttype?

  13. #28
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I could not see the gpkcode you mentined earlier in the database. Can you show in a prototype how are gpkcode and knmpnummer related. Have you looked at the form I modified to add the subform for the barcode? In your other form where you add the scanned barcode you need to add the knmpnummer and gpkcode in combo boxes and allow users to select the two to populate the rest of the info on the form. I can't really help you with the actual design as I can't work with your 2016 db.

    Cheers,
    Vlad

  14. #29
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    I could not see the gpkcode you mentined earlier in the database. Can you show in a prototype how are gpkcode and knmpnummer related. Have you looked at the form I modified to add the subform for the barcode? In your other form where you add the scanned barcode you need to add the knmpnummer and gpkcode in combo boxes and allow users to select the two to populate the rest of the info on the form. I can't really help you with the actual design as I can't work with your 2016 db.

    Cheers,
    Vlad
    i dont see any of the changes you talked about, are you sure you have uploaded the correct version?

    here is a example of the data for the gpk code and related data. i have not made a gpk code colum in any table yet. i would probably would do this in the overzichtstabel, which is the main table of my database.
    Click image for larger version. 

Name:	Naamloos.png 
Views:	19 
Size:	9.2 KB 
ID:	34548

  15. #30
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    OK, so it looks like you have a one-to-many relationship between gpk and knmp and another one-to-many between knmp and barcode. You will need to add a joining table for the first one (you already made the Barcodes table for the second) where you would have UniqueID (autonumber, PK), GPKCode, KNMPNumber. The you need to review the existing tables and remove the duplicate fields. Finally you would use subforms linked by the appropriate fields to allow you to add multiple knmp numbers for any gpk code and multiple barcodes for any knmp.
    Click image for larger version. 

Name:	BarcodeSubform.JPG 
Views:	21 
Size:	80.7 KB 
ID:	34549

    I am attaching the file with the subform so you can see how it works.
    Cheers,
    Vlad
    Attached Files Attached Files

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, 06:35 AM
  2. Need Help:Basic Inventory System
    By shazi9b in forum Access
    Replies: 5
    Last Post: 09-26-2013, 12:48 AM
  3. An inventory system
    By stew8908 in forum Access
    Replies: 3
    Last Post: 05-08-2012, 05:50 PM
  4. Inventory System
    By Rawb in forum Database Design
    Replies: 8
    Last Post: 01-05-2011, 07:26 AM
  5. Replies: 1
    Last Post: 12-30-2008, 08: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
  •  
Other Forums: Microsoft Office Forums