Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: how to insert into a table using VBA?

    My tbl tblComp has 3 cols

    pk (auto num)
    Comp (text)
    desc(text)

    I would like to insert a value into this tabel for Comp from an input box. Is there any way to do with out using ado?

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: how to insert into a table using VBA?

    Originally posted by mikezcg
    My tbl tblComp has 3 cols

    pk (auto num)
    Comp (text)
    desc(text)

    I would like to insert a value into this tabel for Comp from an input box. Is there any way to do with out using ado?
    This is the general idea. There are other variations you could use but this is pretty simple.

    Dim rs as recordset, db as database, strComp as string

    Set db = CurrentDb

    Set rs = db.OpenRecordset("tblComp", dbOpenDynaset)

    strComp = InputBox(Whatever you want to prompt)

    You would now need to find the record that you wanted to add a comp value to. You could use the FindFirst method of the recordset to do this.


    rs.Edit
    rs.Fields("Comp") = strComp
    rs.Update
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Oct 2003
    Posts
    311
    a little messy but...


    Private Sub AddCo(co)

    Dim s As String
    Dim cmd As New ADODB.Command
    Dim lrecs As Long
    s = "Insert Into tblCompanies(Company) Values('" & co & "')"
    With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = s
    cmd.Execute lrecs
    End With

    If lrecs <> 1 Then
    MsgBox "See Database Administrator Imediately", vbCritical, "SERIOUS ERROR"
    End If

    Set cmd = Nothing

    End Sub

  4. #4
    Join Date
    Apr 2012
    Posts
    1

    record a table through a designed form

    rs.Edit
    ' if to add as a new entity type rs.addNew instead of rs.edit
    rs.Fields("Comp") = strComp
    rs.Update[/QUOTE]

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As an aside, you need to rename your Field named desc; this is a Reserved Word in Access and using it as a Field name could lead to problems, sooner or later.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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