Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Auto Fill Rows Based on a criteria

    Hi There,

    I am working on a databse to track and store product serial number data. here is my problem. We create work orders for products in lots between 1 and 100. On certain products we need to track serial number information for each individual unit because there are more numbers associated with the serial number (i.e vendor batch, calibration records). So lets say that the work order number is 1234567. The serial numbers would be the range from 123456700 to 123456799, if the work order was for 100 pices.

    What i am looking for is a way to have access populate those 100 rows in the serial records table I have set up, which will mean that the operators do not have to either key or scan 100 numbers.

    I have a separate table set up to track the work order information and I can link the two tables via the work order number field.

    I am at a complete loss of how to do this, if it is even possible. Any help would be greatly appreciated!

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I'm hoping this may be close to what you're looking for:

    Code:
    dim x as integer
    dim ThisOrderNumber as string
    dim db as Database 
    dim rs as recordset
    
    set db = currentdb
    set rs = db.openrecordset("SerialTable")
    
    ThisOrderNumber = txt_Ordernumber
    
    with rs
         for x = 0 to (TotalPiecesNeeded - 1)
            .addnew
            !OrderNumber = cint(ThisOrderNumber)
            if x < 10 then
                  !SerialNumber = cint(ThisOrderNumber &"0" &x)
            else
                  !SerialNumber = cint(ThisOrderNumber &x)
            endif
            .Update
         next x
    end with
    
    rs.close
    db.close
    If your serialNumber is stored as Text just erase the CInt and brackets.

    This is really only pseudocode, I'm sure you'll need to tweak it to actually get your desired result.

    Sam, hth
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Aug 2009
    Posts
    61
    Thanks for that, I will give it a try and let you know how it goes. I have another question for you. I have two tables, ElectSchedule (used to collect work order information) and SerialNumbers (used to collect serial number information). The linking field between them is WO (work order). Is there a way to have the form used to enter the WO populate the WO field in both tables?

    I read a little bit about it and some people say to use INSERT INTO, but I cannot seem to get that working.

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Without seeing your table layout and what kind of information you're trying to populate the second table with, it's hard to say. (I don't want to guess and be flat wrong)

    Sam
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    I have attached the design of each table. I would like the WO from ElectSchedule to write to WO of SerialNumbers.

    Let me know if this is not enough information.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I think I'm confused. (It's almost quitting time and my brain is fried)

    Isn't that essentially what the code snip I gave you does? Basically after creating your WO record in the ElectSchedule table. Then open the SerialNumbers table and add the WO and SerialNumber for each SerialNumber using the WO number from the ElectSchedule table. You can do this all from your ElectSchedule Form as long as it's unbound (if it's bound I'm not sure how you'd do that...I don't deal with bound forms at all, but, I'd imagine you could still use your addrecord routine and fire it from there).

    What else are you trying to populate from the ElectSchedule form?

    Sam, yup...definitely coming up on beer thirty.
    Good, fast, cheap...Pick 2.

Posting Permissions

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