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
    news:...

    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

    On Sunday, April 28, 2013 6:53:47 AM UTC-4, Douglas J Steele wrote:
    > 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
    >
    > news:...
    >
    >
    >
    > 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!


    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
    news:...

    On Sunday, April 28, 2013 6:53:47 AM UTC-4, Douglas J Steele wrote:
    > 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!
    >
    >
    >


    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

    On Friday, May 3, 2013 10:14:45 PM UTC-4, Douglas J Steele wrote:
    > 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
    >
    > news:...
    >
    >
    >
    > On Sunday, April 28, 2013 6:53:47 AM UTC-4, Douglas J Steele wrote:
    >
    > > 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!

    >
    > >

    >
    > >

    >
    > >

    >
    >
    >
    > 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.


    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.