Results 1 to 8 of 8
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    how to add new records to table based on text boxes value

    Have no idea how to tackle this one, what I amtrying to accomplish is append new records to table [tbl_JBPowerAssignment](primary key IDjb field, autonumber), into fields [JbNo] , [TermNo] & [TB]using values entered in boxes: cboJbAdd, txtTermAdd, txtTbAdd located on form.
    Example 1. (Values entered in boxes)

    cboJBAdd txtTermAdd txtTbAdd
    Jb-01 9 3

    In appended table [tbl_JBPowerAssignment], 9 newrecords
    JbNo
    TermNo
    TB
    Jb-01
    1
    1
    Jb-01
    2
    1
    Jb-01
    3
    1
    Jb-01
    4
    2
    Jb-01
    5
    2
    Jb-01
    6
    2
    Jb-01
    7
    3
    Jb-01
    8
    3
    Jb-01
    9
    3

    Example 2. (Values entered in boxes)
    cboJBAdd txtTermAdd txtTbAdd


    Jb-02 16 2

    16 new appended records
    JbNo
    TermNo
    TB
    Jb-02
    1
    1
    Jb-02
    2
    1
    Jb-02
    3
    1
    Jb-02
    4
    1
    Jb-02
    5
    1
    Jb-02
    6
    1
    Jb-02
    7
    1
    Jb-02
    8
    1
    Jb-02
    9
    2
    Jb-02
    10
    2
    Jb-02
    11
    2
    Jb-02
    12
    2
    Jb-02
    13
    2
    Jb-02
    14
    2
    Jb-02
    15
    2
    Jb-02
    16
    2

    JbNo is a text and the same for each new record, TermNoalways starts at 1 and is incremented by1 up to the number entered intxtTermAdd box, TB is the number of blocks with terminals assigned to it whichlooks like the one below

    (txtTermAdd valuedivided by TermNo value is always an even number):
    For example 1.

    TB 1
    TB 2
    TB 3
    1
    4
    7
    2
    5
    8
    3
    6
    9

    Example 2
    TB 1
    TB 2
    1
    9
    2
    10
    3
    11
    4
    12
    5
    13
    6
    14
    7
    15
    8
    16

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you wanting to do this in a query only or via code?
    The starting point is likely
    INSERT INTO tbl_JBPowerAssignment ("JbNo", "TermNo", "TB") VALUES (cboJbAdd, txtTermAdd, txtTbAdd)
    It is the VALUES part that needs to be modified according to the answer. You threw in just about everything but the kitchen sink and the name of the form, so I'll make one up

    If query, each has to be prefaced with Forms!frmFormName. and that form must be open and the controls must contain data to have any hope of getting anything meaningful out of it. Even if it's a query, there are other ways - a function comes to mind, but if you were going that route, might as well ditch the query and to it all in code.
    Sorry, I skipped over most of the posted data example because it didn't seem relevant to my understanding of the problem.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Micron, thanks for your replay, the way I understand append query is that it will append me only one record from textboxes on a form. What I need is to generate records as many times as the value in txtTermAdd box. I have created code which does that, however it is not exactly what I need. What I am getting with this code is the correct numbers of records but these are exactly the same.
    For example if the values in boxes are
    cboJBAdd txtTermAdd txtTbAdd
    Jb-03 100 2

    I am getting 100 new identical records. What I need is that for each new record TermNo increases by one 1,2,3,4...,up to 100 and at the same time code assigns "1" to TermNo from 1-50 and "2" to 51-100 in this example. This is my problem and don't have idea how correct it.

    Code:
    Private Sub btnJbAdd_Click()Dim i As Integer
    Dim db As Database
    Dim rec As Recordset
    
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tbl_JbPowerAssignment")
    For i = 1 To txtTermAdd.Value
    rec.AddNew
    rec("JbNo") = Me.cboJbAdd
    rec("TermNo") = Me.txtTermAdd
    rec("TB") = Me.txtTbAdd
    rec.Update
    Next i
    
    
    Set rec = Nothing
    Set db = Nothing
    End Sub

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll need to review everything and get back to you but I will not be able to do much today. I'm seeing the need for an inner loop that increments a value then passes back to the outer loop and increments that value and repeats all until the outer loop max is reached. It may involve 2 sql statements as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Quote Originally Posted by Micron View Post
    I'll need to review everything and get back to you but I will not be able to do much today. I'm seeing the need for an inner loop that increments a value then passes back to the outer loop and increments that value and repeats all until the outer loop max is reached. It may involve 2 sql statements as well.
    Thanks Micron, I am almost thre wth the code, have to figure oure out how to deal wit TB's
    Code:
    Private Sub btnJbAdd_Click()
    Dim i As Integer
    Dim db As Database
    Dim rec As Recordset
    Dim TbNo As Integer
    
    TbNo = txtTermAdd.Value
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tbl_JbPowerAssignment")
    For i = 1 To txtTermAdd.Value
    rec.AddNew
    rec("JbNo") = Me.cboJbAdd
    rec("TermNo") = (i - 1) + 1
    Select Case i
    Case Is <= (txtTermAdd.Value / txtTbAdd.Value)
    rec("TB") = "1"
    Case Is > (txtTermAdd.Value / txtTbAdd.Value)
    rec("TB") = "2"
    Case Else
    rec("TB") = "0"
    End Select
    rec.Update
    Next i
    
    Set rec = Nothing
    Set db = Nothing
    End Sub

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Not sure what is meant by this:
    Quote Originally Posted by HS_1 View Post
    (txtTermAdd valuedivided by TermNo value is always an even number):

    Here is the code I came up with. Seems to work using the 3 examples in Post #1.
    Code:
    Private Sub btnJbAdd_Click()
        Dim db As DAO.Database
        Dim rec As DAO.Recordset
    
        Dim JunctionBlock As String    'cboJbAdd
        Dim TerminalAdd As Integer    'txtTermAdd
        Dim TerminalBlock As Integer   'txtTbAdd
    
        Dim i As Integer, j As Integer, k As Integer   'counters
        Dim TbCount As Integer
    
        Set db = CurrentDb
        Set rec = db.OpenRecordset("Select * from tbl_JbPowerAssignment")
    
        'get values from form controls
        JunctionBlock = Me.cboJbAdd
        TerminalAdd = Me.txtTermAdd
        TerminalBlock = Me.txtTbAdd
    
        TbCount = TerminalAdd \ TerminalBlock   '<<-- using integer division
        '    Debug.Print TbCount
    
        'initialize variables
        j = 1
        k = 1
    
        'start loop
        For i = 1 To TerminalAdd
            rec.AddNew
            rec("JbNo") = JunctionBlock    'string
            rec("TermNum") = i
            rec("Tb") = j     'TerminalBlock
            rec.Update
    
            k = k + 1
            If k > TbCount Then
                k = 1
                j = j + 1
            End If
        Next i
    
        'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    
        MsgBox "Done"
        DoCmd.OpenTable ("tbl_JBPowerAssignment")
    
    End Sub
    Attached Files Attached Files

  7. #7
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Quote Originally Posted by ssanfu View Post
    PMFJI,

    Not sure what is meant by this:



    Here is the code I came up with. Seems to work using the 3 examples in Post #1.
    Code:
    Private Sub btnJbAdd_Click()
        Dim db As DAO.Database
        Dim rec As DAO.Recordset
    
        Dim JunctionBlock As String    'cboJbAdd
        Dim TerminalAdd As Integer    'txtTermAdd
        Dim TerminalBlock As Integer   'txtTbAdd
    
        Dim i As Integer, j As Integer, k As Integer   'counters
        Dim TbCount As Integer
    
        Set db = CurrentDb
        Set rec = db.OpenRecordset("Select * from tbl_JbPowerAssignment")
    
        'get values from form controls
        JunctionBlock = Me.cboJbAdd
        TerminalAdd = Me.txtTermAdd
        TerminalBlock = Me.txtTbAdd
    
        TbCount = TerminalAdd \ TerminalBlock   '<<-- using integer division
        '    Debug.Print TbCount
    
        'initialize variables
        j = 1
        k = 1
    
        'start loop
        For i = 1 To TerminalAdd
            rec.AddNew
            rec("JbNo") = JunctionBlock    'string
            rec("TermNum") = i
            rec("Tb") = j     'TerminalBlock
            rec.Update
    
            k = k + 1
            If k > TbCount Then
                k = 1
                j = j + 1
            End If
        Next i
    
        'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    
        MsgBox "Done"
        DoCmd.OpenTable ("tbl_JBPowerAssignment")
    
    End Sub
    ssanfu thank you for your help, It is exacty what I was after

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ssanfu thank you for jumping in.
    I was at the hospital most of the day because my wife had spinal surgery. Today I found out that my younger brother has lung cancer and they gave him 2 to 6 months; more if he goes for immunotherapy. It's turning out to be not such a great ending to the week and I just haven't had it in me to address anything that requires more than a morsel of thought.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-22-2015, 12:47 AM
  2. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  3. Replies: 3
    Last Post: 11-24-2012, 05:35 PM
  4. Replies: 15
    Last Post: 04-01-2011, 11:41 AM
  5. Replies: 15
    Last Post: 09-18-2010, 01:19 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