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

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.
Similar Threads
  1. Carol Spears via AccessMonster.com

    Speed Data Entry By Pre-populating with previous entry

    Carol Spears via AccessMonster.com, May 9, 2005, in forum: Access Forms Coding
    Replies:
    2
    Views:
    125
    Carol Spears via AccessMonster.com
    May 9, 2005
  2. hfreedman

    New Entry to Pop-Up Entry Form

    hfreedman, Jan 11, 2006, in forum: Access Forms Coding
    Replies:
    1
    Views:
    118
    Allen Browne
    Jan 12, 2006
  3. graeme34 via AccessMonster.com

    How to check for non entered data on a data entry form

    graeme34 via AccessMonster.com, Feb 23, 2006, in forum: Access Forms Coding
    Replies:
    5
    Views:
    139
    graeme34 via AccessMonster.com
    Feb 23, 2006
  4. Manuel

    Enforcing Data Entry with VBA Code

    Manuel, Aug 12, 2006, in forum: Access Forms Coding
    Replies:
    3
    Views:
    85
    Rod Plastow
    Aug 13, 2006
  5. Data Entry India
    Replies:
    0
    Views:
    325
    Data Entry India
    Mar 31, 2008
  6. Michelle F

    Single Entry vs. All Entry Coding

    Michelle F, Aug 19, 2008, in forum: Access Forms Coding
    Replies:
    4
    Views:
    170
    John W. Vinson
    Aug 19, 2008
  7. David K.
    Replies:
    1
    Views:
    164
    Marshall Barton
    Apr 21, 2009
  8. Chris K
    Replies:
    2
    Views:
    373
    John W. Vinson
    Jul 25, 2010
Loading...