Results 1 to 9 of 9
  1. #1
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72

    Sql to add record

    I have three forms: main (houses a listbox that retrieves all records) an Add form (used to add a new record) and an edit form to edit a record. I want the add form to be unbound and when the user fills in the text boxes, and clicks the add button, it will use vb or sql to add a new record to the table (Table1) how would I do this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    I would use VBA and SQL, something like (example shows a text, date, and number field):

    CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES('" & Me.controlname1 & "', #" & Me.controlname2 & "#, " & Me.controlname3 & ")"

    However, this is a very simple example and doesn't allow for any empty controls.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    Yeah I will ensure they aren't blank prior to execution. So that will work without having to open the table within the form?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Yes, that's what Execute is for. Just be sure to use your names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    It's giving me a syntax error on "INSERT INTO"

  6. #6
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    Fixed it a bit and now it says that I can't reference a property or control unless the control has the focus....
    Code:
    CurrentDb.Execute ("INSERT INTO Table1(Title, Assigned By, Requested By) VALUES ('" & Me.txtTitle.Text & "', #" & Me.boxAssigned.Text & "#, " & Me.boxRequested.Text & ")")
    ::EDIT:: forgot that you can't use the ".text" without focus, so I shifted back to .value and now it's back to the "INSERT INTO" error.

  7. #7
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    With a little bit of fixing some missing ' in there, I got her working! Thank you!


    Code:
    DoCmd.RunSQL ("INSERT INTO Table1([Title],[Assigned To],[Requested By]) VALUES ('" & Me.txtTitle.Value & "', '" & Me.boxAssigned.Value & "', '" & Me.boxRequested.Value & "')")

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Remove the outer parens () - see my example.

    My example includes syntax for text, date, and number fields. Use the appropriate syntax for each of your fields.

    Value is the default property of data controls. If not indicated it will be assumed so not required to type it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    Thank you, I got it working =)


    I'm trying to have the "Exit" button the sub form requery the listbox located on the main form but keep getting an error stating that Access doesn't allow this method from the current view??

    This is what I have to requery...
    Code:
    [Forms]![Main1]![lstActive].Requery

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-03-2013, 10:02 AM
  2. Replies: 3
    Last Post: 03-09-2013, 10:39 AM
  3. Replies: 2
    Last Post: 12-21-2012, 01:57 PM
  4. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  5. Replies: 3
    Last Post: 10-19-2012, 04:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums