Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Add Record To A Table.

    I have a form that queries info from a table. Once the form is populated I want the user to be able to press a button and write that data to a new table. I also need to add a counter to the form. So for every new record, a number is added to it. I also need the counter to populate in the form b/c I am creating a report to show the results. I added and add new record button but I don't know where its updating. Thanks for anyone who can help.

  2. #2
    Join Date
    Jun 2004
    Posts
    6
    If the "new table" (Table2) is not a temporary table (that is, you will create it and just want to add the Table1 record from the form to Table2) this might work for you:

    Dim db as database
    Dim rst as recordset
    Dim MaxCounter as Integer

    set db = currentdb
    set rst = db.openrecordset ("select max(CounterField) as MaxCount from Table2")
    if not rst!EOF then
    MaxCounter = rst!MaxCount + 1
    else
    MaxCounter = 1
    end if
    set rst = db.openrecordset ("select * from Table2")
    with rst
    .addnew
    !CounterField = MaxCounter
    !Field1 = Me.Field1Equivalent
    !Field2 = Me.Field2Equivalent
    !Field3 = Me.Field3Equivalent
    .update
    end with
    set rst = nothing
    set db = nothing
    end if


    Hopefully that'll help,
    -smr

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    I'm not very good with that language but with a little help I think we can figure it out. I build a table called table 2. Here is the code that I have.

    Dim db As Database
    Dim rst As Recordset
    Dim MaxCounter As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select max(CounterField) as MaxCount from Table2")
    If Not rst!EOF Then
    MaxCounter = rst!MaxCount + 1
    Else
    MaxCounter = 1
    End If
    Set rst = db.OpenRecordset("select * from Table2")
    With rst
    .AddNew
    !CounterField = MaxCounter
    !Field1 = Me.LEASE_NO
    !Field2 = Me.name
    .Update
    End With
    Set rst = Nothing
    Set db = Nothing
    End If
    Private Sub Command16_Click()
    End Sub
    Private Sub Command17_Click()
    On Error GoTo Err_Command17_Click
    DoCmd.Close
    Exit_Command17_Click:
    Exit Sub
    Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    End Sub

    Is this right? What would I call the text box to see the counter number on the form?

Posting Permissions

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