Data Entry VBA

Discussion in 'Access Forms Coding' started by Haas C, Apr 27, 2013.

  1. Haas C

    Haas C Guest

    OK, to avoid confusion, all i want is the code for a procedure where if I press a button on the form it will call up a VBA procedure which would essentially update/add a frecord to the table. I KNOW THIS CAN BE DONE WITHOUT CODE, but I'm looking for VBA code for other purposes:

    I have a table called tblContacts
    There are two fields in this table: CompanyName and PhoneNumber

    I created a form where the user can see if a company name exists through a Combo Box with the source being the list of entries in the tblContacts field - if it doesn't, he enters a new company into the a txtCompanyName box and a phone number into the txtPhoneNumber box on the form. Next, user presses a button on the form which would call a VBA procedure which runs and takes the values from txtCompanyName and txtPhoneNumber and adds to the tblContacts table

    I know that you can do this easily through Form Wizard OR a RECORDSOURCE (thanks Larry), but I wanted to know how to program this into a VBA procedure.. This is a simple example to learn the technique so I cna apply it to one with multiple fields, etc.

    Thank you for your help in advance!
     
    Haas C, Apr 27, 2013
    #1
    1. Advertisements

  2. Dim strSQL As String

    strSQL = "INSERT INTO tblContacts(CompanyName, PhoneNumber) " & _
    "VALUES('" & Replace(Me.txtCompanyName, "'", "''") & "', '" &
    Me.txtPhoneNumber & "'")
    CurrentDb.Execute strSQL, dbFailOnError.

    Pay close attention to the quotes!

    "Haas C" wrote in message

    OK, to avoid confusion, all i want is the code for a procedure where if I
    press a button on the form it will call up a VBA procedure which would
    essentially update/add a frecord to the table. I KNOW THIS CAN BE DONE
    WITHOUT CODE, but I'm looking for VBA code for other purposes:

    I have a table called tblContacts
    There are two fields in this table: CompanyName and PhoneNumber

    I created a form where the user can see if a company name exists through a
    Combo Box with the source being the list of entries in the tblContacts
    field - if it doesn't, he enters a new company into the a txtCompanyName box
    and a phone number into the txtPhoneNumber box on the form. Next, user
    presses a button on the form which would call a VBA procedure which runs and
    takes the values from txtCompanyName and txtPhoneNumber and adds to the
    tblContacts table

    I know that you can do this easily through Form Wizard OR a RECORDSOURCE
    (thanks Larry), but I wanted to know how to program this into a VBA
    procedure. This is a simple example to learn the technique so I cna apply it
    to one with multiple fields, etc.

    Thank you for your help in advance!
     
    Douglas J Steele, Apr 28, 2013
    #2
    1. Advertisements

  3. Haas C

    Haas C Guest

    I copied and pasted in the code as you've written it and it fails at the Code level...I get a compiled error..."expected: end of statement.
     
    Haas C, Apr 30, 2013
    #3
  4. Looks as though you may have been the victim of word-wrap.

    strSQL = "INSERT INTO tblContacts(CompanyName, PhoneNumber) " & _
    "VALUES('" & Replace(Me.txtCompanyName, "'", "''") & "', '" & _
    Me.txtPhoneNumber & "'")
    CurrentDb.Execute strSQL, dbFailOnError.


    "Haas C" wrote in message

    I copied and pasted in the code as you've written it and it fails at the
    Code level...I get a compiled error..."expected: end of statement.
     
    Douglas J Steele, May 4, 2013
    #4
  5. Haas C

    Haas C Guest

    Many Thanks - that did the trick!
     
    Haas C, May 15, 2013
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.