Professional Web Applications Themes

Many form records to many table records - Microsoft Access

Hi, I would like to add more than 1 record from my form to a table. I have written and tested the code to open the table and I can write 1 record but that's all. According to Help I can nest with statements, which I have tried using both the form name and one of the form field names and still get just one record. I've also tried to construct a For Each statement but get hung up on the syntax as it won't accept Me.Fieldname or Me!Fieldname as the variable. Once again. Thanks...

  1. #1

    Default Many form records to many table records

    Hi,

    I would like to add more than 1 record from my form to a
    table. I have written and tested the code to open the
    table and I can write 1 record but that's all.

    According to Help I can nest with statements, which I have
    tried using both the form name and one of the form field
    names and still get just one record.

    I've also tried to construct a For Each statement but get
    hung up on the syntax as it won't accept Me.Fieldname or
    Me!Fieldname as the variable.

    Once again. Thanks
    Anne Guest

  2. #2

    Default Re: Many form records to many table records

    Not sure I understand you. Can you explain further? Thanks
    "Anne" <ca> wrote in message
    news:05bd01c36dae$68458730$gbl... 


    Ephraim Guest

  3. #3

    Default Re: Many form records to many table records

    I have a continuous form that lists all of the order
    details for a particular order. I've added a button that
    lets the user invoice this order. What I want to do is
    have the code behind the button add a record to the
    invoice header table (1 record) as well as add a record
    for each of the order details displayed on the form to the
    invoice details table (many records - 1 for each order
    detail).

    The code I have written so far adds the invoice header
    correctly but I can't get any more than one record in the
    invoice details table.

    VB Help says I can use a For Each or nest With statements
    but I 'Think' I am having trouble at the syntax level.

    For Each Me!Fieldname or Me.Fieldname in Form is
    unacceptable.

    Nested With statements may be the better way to go but
    again, if I reference either a field within the form or
    the form itself I get only 1 record added to the invoice
    details table.

    Anne



     
    Thanks [/ref]
    have [/ref]
    get 
    >
    >
    >.
    >[/ref]
    Anne Guest

  4. #4

    Default Many form records to many table records

    This is a repost from August 29


    I have a continuous form that lists all of the order
    details for a particular order. I've added a button that
    lets the user invoice this order. What I want to do is
    have the code behind the button add a record to the
    invoice header table (1 record) as well as add a record
    for each of the order details displayed on the form to the
    invoice details table (many records - 1 for each order
    detail).

    The code I have written so far adds the invoice header
    correctly but I can't get any more than one record in the
    invoice details table.

    VB Help says I can use a For Each or nest With statements
    but I 'Think' I am having trouble at the syntax level.

    For Each Me!Fieldname or Me.Fieldname in Form is
    unacceptable.

    Nested With statements may be the better way to go but
    again, if I reference either a field within the form or
    the form itself I get only 1 record added to the invoice
    details table.

    Any and all suggestions welcome

    Anne

    Anne Guest

  5. #5

    Default Many form records to many table records

    Hi Anne,

    Looks like the first thing you need to do is count the
    records on your form in order to add the appropriate
    number of recs to the invoice_detail table. So, on
    the "invoice" button on the order details form you would
    do something like this:

    Invoice_Button On_Click()
    Dim rs As DAO.Recordset
    Dim intCount As Integer
    ' ensure you have a reference to
    ' MS DAO 3.6 library (for A2K2)
    Set rs = Me.Recordset
    rs.MoveLast
    intCount = rs.RecordCount

    Call addInvoiceRecs(intCount, Me.Order_id.value)
    'I'm assuming you'll want to populate the order_id in the
    'invoice table - probably a foreign key?? If not, don't
    'pass the second argument.

    End Sub

    Sub addInvoiceRecs(numRecs As Integer, OrdID As String)
    Dim intCounter As Integer
    Dim db as Database
    Dim rsInvoice as DAO.Recordset
    On Error GoTo err_Invoice_Recs

    Set db = CurrentDB
    Set rsInvoice = db.OpenRecordset("Invoice_Table_Name")
    With rsInvoice
    For intCounter = 1 to numRecs
    .addNew
    .Fields("order_id").Value = ordID
    .Update
    Next intCounter
    End With
    rsInvoice.Close
    Set rsInvoice = Nothing
    Exit Sub

    exit_Invoice_Recs:
    Exit Sub
    err_Invoice_Recs:
    MsgBox err.Description & vbCrLf & err.Number
    rsInvoice.close
    Resume exit_Invoice_Recs
    End Sub

    Regards,
    Jen
     
    the 
    Jen Guest

Similar Threads

  1. Question add records to access table using adobe flash feedback form
    By Unregistered in forum Web Design
    Replies: 0
    Last Post: August 23rd, 04:03 AM
  2. Replies: 1
    Last Post: February 12th, 12:52 PM
  3. How to get the last N records from a table
    By Ricardo in forum IBM DB2
    Replies: 3
    Last Post: October 22nd, 03:40 AM
  4. Replies: 1
    Last Post: July 31st, 07:47 PM
  5. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139