Results 1 to 9 of 9
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Automatically fill fictional S/N of a product

    Hello,



    I am having a complex issue that I don't even know how to name - therefore a bit confusing title of the topic.

    I keep information about my products' serial numbers. The serial number is first entered when I receive the product from my supplier. Then it is inserted again when I am creating a sales order. The database is able to link these 2 serial number inputs and it knows that it is the same unit and it pairs the supplier and the customer.

    It works like a charm, but some products have no serial numbers. So I am storing them with dummy serial numbers (for example FICTIONAL_00001, FICTIONAL_00002 and so on). Right now I need to look in my OrderDetails Table for the last number used and manually calculate the iteration. I got annoyed of this, so I am trying to automate this at the moment.

    Another problem is that I want to keep information about how many pieces of a product do I have in stock. So in case of the dummy products, when I am creating a sales order, I need to choose a dummy S/N that has been assigned to this product type, so it removes itself from the storage.

    So far I managed to accomplish this:
    • When I am doing a purchase order and enter "f" into the serial number field (stands for fictional), an After Update code runs, looks into the Order Details Table, calculates the correct dummy number (Format and Dcount functions), sets it into the field and sets focus on the next one (that's 100 % perfect result that I'm looking for)




    Now the complications begin when I am trying to do the sales order part. In human language, I want this to happen:

    I input "f" that says that I am selling a product that has no S/N. The form jumps to the Product Type combo box where I choose the product type (already done and working). After I choose the product type (After Update event), I need the database to look into the Order Details Table, pull information about products in stock filtered by the selected product type, select the earliest dummy S/N and set it to the form text box.

    For example the query result would be:
    Product SerialNumber
    Item 1 FICTIONAL_00010
    Item 1 FICTIONAL_00015
    Item 1 FICTIONAL_00017
    Item 1 FICTIONAL_00033

    Now, I would want FICTIONAL_00010 to be inserted in my form control.

    I tried some coding that would run the query with a parameter based on the product type that I choose but no good results so far. And also I have no idea how to choose the earliest dummy S/N and actually insert it to a text box.


    Any advice would be greatly appreciated!

    Tomas

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show us a screen capture of your tables and relationships --your relationships window. Make sure you extend boxes to show all fields before capturing the screen image.

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Click image for larger version. 

Name:	relationships.jpg 
Views:	13 
Size:	115.0 KB 
ID:	36987

    Is this good enough or will it be necessary for me to translate the names for you?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It works like a charm, but some products have no serial numbers. So I am storing them with dummy serial numbers (for example FICTIONAL_00001, FICTIONAL_00002 and so on). Right now I need to look in my OrderDetails Table for the last number used and manually calculate the iteration. I got annoyed of this, so I am trying to automate this at the moment.
    use what is commonly called 'dmax+1'

    Since your codes are not numeric you many need to play around with it a bit - but providing the length of the number including preceding zeros is constant (5 digits) then this should work

    newvalue="FICTIONAL_" & format(dmax("val(right(serialnumber,5))","OrderDet ails","Serialnumber like 'FICTIONAL_*'"),"00000")

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Ajax View Post
    use what is commonly called 'dmax+1'

    Since your codes are not numeric you many need to play around with it a bit - but providing the length of the number including preceding zeros is constant (5 digits) then this should work

    newvalue="FICTIONAL_" & format(dmax("val(right(serialnumber,5))","OrderDet ails","Serialnumber like 'FICTIONAL_*'"),"00000")
    Thanks! This is actually a part that I already figured out. I got it like this:

    Me.SerialNumber.Value = "FIKTIVNI_" & Format(DCount("*", "tblObjednavkyDetail", "[SerialNumber] Like ""FIKTIVNI_" & "*"" ") + 1, "00000")


    I can't figure out the latter part :/

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    only issue with dcount is if you delete a record, your 'count' gets smaller so you calculate the same 'new' value again.

    I don't understand the 'latter part' so can't help with that

  7. #7
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Ajax View Post
    only issue with dcount is if you delete a record, your 'count' gets smaller so you calculate the same 'new' value again.

    I don't understand the 'latter part' so can't help with that
    Well, that's true, but there is no reason to delete any record in this table. Definitely not allowed via user forms:



    This is meant by the latter part:

    I input "f" that says that I am selling a product that has no S/N. The form jumps to the Product Type combo box where I choose the product type (already done and working). After I choose the product type (After Update event), I need the database to look into the Order Details Table, pull information about products in stock filtered by the selected product type, select the earliest dummy S/N and set it to the form text box.

    For example the query result would be:
    Product SerialNumber
    Item 1 FICTIONAL_00010
    Item 1 FICTIONAL_00015
    Item 1 FICTIONAL_00017
    Item 1 FICTIONAL_00033


    Now, I would want FICTIONAL_00010 to be inserted in my form control.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Well, that's true, but there is no reason to delete any record in this table. Definitely not allowed via user forms:
    you have faith that this cannot happen by accident. It's your application so down to you.

    I need the database to look into the Order Details Table

    select the earliest dummy S/N


    Now, I would want FICTIONAL_00010 to be inserted in my form control.
    i'm busy on other things so don't have time to ask lots of questions but would think you would just use

    Code:
    formcontrol=dmin("serialnumber","orderdetails","serialnumber like 'FIKTIVNI_*'")
    pull information about products in stock filtered by the selected product type
    this I can't answer

  9. #9
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Ajax View Post
    you have faith that this cannot happen by accident. It's your application so down to you.

    i'm busy on other things so don't have time to ask lots of questions but would think you would just use

    Code:
    formcontrol=dmin("serialnumber","orderdetails","serialnumber like 'FIKTIVNI_*'")
    this I can't answer
    Thanks a lot! This solved my whole issue. I simply didn't know that there was a DMin function. Here is the solution:

    Code:
    Private Sub cboFKProduktID_AfterUpdate()
        If SerialNumber = "f" Then
            SerialNumber.Value = DMin("SerialNumber", "qryProduktyNaSkladuPodleSN", "[IDProduktu] = [cboFKProduktID]")
            Me.Refresh
        End If
    End Sub
    Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  2. Fictional store Database design
    By RJConroy86 in forum Database Design
    Replies: 1
    Last Post: 01-26-2015, 02:43 PM
  3. Subform won't automatically fill.
    By IncidentalProgrammer in forum Forms
    Replies: 2
    Last Post: 01-22-2015, 12:45 PM
  4. Automatically fill in field in a loop
    By RachelBedi in forum Forms
    Replies: 3
    Last Post: 10-24-2012, 12:40 PM
  5. Replies: 4
    Last Post: 02-13-2012, 05:49 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