Page 3 of 9 FirstFirst 123456789 LastLast
Results 31 to 45 of 132
  1. #31
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    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.

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


    Vlad
    thanks a lot.
    So what is the function of the new append query? update barcodes? do i need to assign to a button?

    for the gpk table, i can just paste info from the overzichtabel? and then add gpk number?

    ive updated my current db again. https://drive.google.com/open?id=1Rp...yLoe_UXL9LbjuD

  2. #32
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The append query will transfer the info to the Labels table so you can print them, isn't that what you want it to do? You need to separate the info in your main table according to what each field pertains to (gpk versus knmp).

  3. #33
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    The append query will transfer the info to the Labels table so you can print them, isn't that what you want it to do? You need to separate the info in your main table according to what each field pertains to (gpk versus knmp).
    ah i see, so basically its just the same as my ''old'' appendquery.

    So about the barcodes, how do i link these codes to the right product? and how will i be able to use that active barcode in the combobox in the form? is it possible to make a popup form for the barcode recognition? so it only makes record entry's that are correct. (so the worker can check the barcode after scanning without immediatly creating a record. record will only be created after he presses save in the ontstripformulier savebutton.)
    i would also like the product name(geneesmiddel) to show with the barcode, for a visual check if the barcode/product is correct.

  4. #34
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached file and see if that is what you wanted.

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #35
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Ah yes thanks this is great.
    Ill work with it a bit.

    Is it not possible to have the Geneesmiddel column in both tables? that makes it easier for data entry, edit and checking.

    i tried to put this code in the barcode table and its not working.
    Code:
    SELECT [Overzichtstabel].[Geneesmiddel] FROM Overzichtstabel INNER JOIN Barcodes ON [Barcodes].[KNMPNummer]=[Overzichtstabel].[KNMPNummer];
    id want the original geneesmiddel data to be located in overzichtstabel but would be nice if it would show in barcodes too.

    Quote Originally Posted by Gicu View Post
    Have a look at the attached file and see if that is what you wanted.

    Cheers,
    Vlad
    i tried importing your subforms, but when i open the form in dataview i get a different view than when i use your file. in your file it shows the tables and in my file it shows actual (sub) forms.

  6. #36
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The Geneesmiddel field should go in the table with the entity it describes; if it is associated with a particular barcode it should be in barcodes, but if it is associated with a knmp number (meaning you can have multiple barcodes with the same Geneesmiddel and same knmpnumber) then it should be in Overzichtstabel. As a general rule you don't want to duplicate fields across tables, you can bring them together using queries.

    For the two subforms make sure they are linked properly (look at the child/master fields in the property sheet of the subform controls, they both need to be linked by the knmpnumber fields) and change the default view of the subforms to datasheet.

    Cheers,
    Vlad

  7. #37
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    The Geneesmiddel field should go in the table with the entity it describes; if it is associated with a particular barcode it should be in barcodes, but if it is associated with a knmp number (meaning you can have multiple barcodes with the same Geneesmiddel and same knmpnumber) then it should be in Overzichtstabel. As a general rule you don't want to duplicate fields across tables, you can bring them together using queries.

    For the two subforms make sure they are linked properly (look at the child/master fields in the property sheet of the subform controls, they both need to be linked by the knmpnumber fields) and change the default view of the subforms to datasheet.

    Cheers,
    Vlad
    what do you mean exactly. i tried putting this in the rowsource:
    Code:
    SELECT Barcodes INNER JOIN Overzichtstabel ON Barcodes.KNMPNummer = Overzichtstabel.KNMPNummer SET Barcodes.Geneesmiddel = [Overzichtstabel].[Geneesmiddel];
    doesnt work.
    i can make a query to fill out the field but then the tables will contain duplicate data right

  8. #38
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You should not have two fields called Geneesmiddel. If it pertains to a specific barcode (only one barcode per Geneesmiddel) it should ONLY be in the Barcodes table. If it pertains to knmpnumber (you can have multiple barcodes with the same Geneesmiddel) it should ONLY be in the Overzichtstabel table. Then you would make a select query joining the two tables by knmpnumber and bring it it from whatever table. If you have it in both tables you will end up with data integrity issues as every change will have to be mirrored in both tables.

    Vlad

  9. #39
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    ah ok i understand.

    I have another question. in the frmhomescreenadmin i want to make a searchfield that searches all fields from overzichtabel(and maybe barcodestable too) and gives results
    which code would i need for this? i tried several vba codes for the search button but it doesnt work.

  10. #40
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you go, you can add or remove fields from qrySearch, just make sure you use the same pattern (each field should have the criteria on a new OR line). Note that will work with partial searches (PARA will return both PARACETAMOL and PARACE).

    Cheers,
    Vlad
    Attached Files Attached Files

  11. #41
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    Here you go, you can add or remove fields from qrySearch, just make sure you use the same pattern (each field should have the criteria on a new OR line). Note that will work with partial searches (PARA will return both PARACETAMOL and PARACE).

    Cheers,
    Vlad
    Great thanks! exactly what i wanted. I imported it into my own db. i created a similar query and search for the data in labeloutput. This works perfectly.
    But the search i imported for the overzichtabel(and the 2 other tables) doesnt work when imported. i cant find any differences in the imported query and the code.. (i did change some output fields but before that it didnt work correctly either)

    If it pertains to knmpnumber (you can have multiple barcodes with the same Geneesmiddel) it should ONLY be in the Overzichtstabel table.
    ok i will do this, as that is what i need. Per 1 knmp number there is only 1 geneesmiddel(basically its unique name and linked unique id), but multiple barcodes per those 2.
    Same goes for gpknummer, per 1 gpk code there can be many knmp numbers.

  12. #42
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    How exactly does not work? Do you get errors or no records returned or ...? Have you checked the link types (you want a outer join from GPK to Overzichtstabel )? What is the name of the textbox on your admin form (I renamed the default you had Text4 or something to txtSearch in the sample file I've uploaded)?

    Cheers,
    Vlad

  13. #43
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    How exactly does not work? Do you get errors or no records returned or ...? Have you checked the link types (you want a outer join from GPK to Overzichtstabel )? What is the name of the textbox on your admin form (I renamed the default you had Text4 or something to txtSearch in the sample file I've uploaded)?

    Cheers,
    Vlad
    Click image for larger version. 

Name:	Naamloos.png 
Views:	23 
Size:	112.6 KB 
ID:	34595

    this is what i get after pasting the query in my db. it just gives random results or something? in your file it works correctly though.
    Click image for larger version. 

Name:	Naamloos.png 
Views:	23 
Size:	29.5 KB 
ID:	34596

    i havent touched your query etc. the names and links are the same. the funny thing is, the qryOntstripsearch which i made myself does work correctly but your's doesnt.(only in my file, in your file it does work correctly)

    the textbox is named txtSearch yes

  14. #44
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to compare the query's SQL in both files and see if you spot any differences.
    Vlad

  15. #45
    farmi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    73
    Quote Originally Posted by Gicu View Post
    Try to compare the query's SQL in both files and see if you spot any differences.
    Vlad
    ah i found the problem, geneesmiddel was being pulled from the wrong table. it works now

    Moving on, i still dont understand how i can make the ontrstripformulier check the barcode in the barcodes table and how to set these active inactive. it will require additional coding right? because currently the active /inactive box doesnt do anything right
    Last edited by farmi; 07-03-2018 at 08:30 AM.

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