Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2015
    Posts
    2

    Unanswered: Access Form - Create multiple records based on a number entered on a form

    Hello dbforums,

    This seems like it would a frequently asked question, but I am having a hard time finding an answer or related topic.

    I am creating a sample database that allows users to generate samples. One function that I'd like it be able to do is:
    1. User enters a number in a textbox
    2. User hits the an "Add" button
    3. Access takes what was entered in that textbox and create that many records to an existing table with a serial code.

    For example:
    1. Enter "2" into in textbox named DNACreate
    2. User hits the "Add" button
    3. The end result would be that in the tbl_DNA table, two records are created with serial codes of "CAN0001_AB_20150218_BP1D01" and "CAN0001_AB_20150218_BP1D02", with the last two digits increasing in sequence.

    Could this be achieved with an append query?

    Thank you for your time.

    Kelly

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can't think of a pure query way offhand. I'd use a For/Next loop and use its counter to increment your code. I'd use the AddNew method of a DAO recordset, but you could also execute SQL inside the loop.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    this should point you down the right track
    Code:
    Sub Add_records(Num As Long)
        Dim rs As Recordset
        Dim db As Database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("TABLENAME")
        For aa = 0 To (Num-1)
            rs.AddNew
            rs("feildname") = "CAN0001_AB_" & Format(Date, "YYYYMMDD") & "_BP1D" & Format(aa, "00")
            rs.Update
        Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub
    Last edited by myle; 02-18-15 at 17:01. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2015
    Posts
    2
    Thank you guys so much. I tried the code and apply it to my form and this is what I have now.
    Click image for larger version. 

Name:	samplesearchadd.jpg 
Views:	4 
Size:	24.7 KB 
ID:	16173

    Basically the header is where user inputs sample search criteria and details shows the results.
    The right highlighted textboxes is where the users enter how many samples they want to add. Once they hit, the specified number of the DNA/RNA samples gets created in the BloodDNASample table or BloodRNASample table, with MolecularSampleCode autopopulated based on the BloodParentSample Code ("Sample Code" in the image) and the code that you helped me with.

    If the user decides to add more samples another time, I need to make sure that the sample code goes in incremental sequence based on what was created last time.
    For example:

    1) the BloodDNASample table now has 2 records of "child" created from "parent", VBC0001_KC_20120213_BS1, with sample code of VBC0001_KC_20120213_BSD01 and VBC0001_KC_20120213_BSD02
    2) the user wants to create 1 more child from this parent, enters "1" into Me.DNAtoAdd and hits cmdAddDNA_Click()
    3) the BloodDNASample table now has 3 records named VBC0001_KC_20120213_BSD01, VBC0001_KC_20120213_BSD02, VBC0001_KC_20120213_BSD03

    Is there a way to do that? Would an If statement work? First looking at whether a parent has child or not, if no, then find the largest last two digit of child's sample code. Then I would continue on the AddNew code.
    This is what I have right now:

    Code:
    Private Sub cmdAddDNA_Click()
        Dim rs As Recordset
        Dim db As Database
        Dim DNAtoAdd As String
        Dim i As Long
              
        Set db = CurrentDb
        Set rs = db.OpenRecordset("BloodMolecularSample")
        DNAtoAdd = Me.DNAtoAdd
    'add if statement'
        For i = 1 To DNAtoAdd
            rs.AddNew
            rs("BloodDNASampleCode") = [BloodParentSampleCode] & "D" & Format(i, "00")
            rs.Update
        Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    End Sub
    I appreciate all your feedbacks or if you can point me to the right direction.

    Thank you.

Posting Permissions

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