Sql to add record

[lithium]

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?

[June7]

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.

[lithium]

Yeah I will ensure they aren't blank prior to execution. So that will work without having to open the table within the form?

[June7]

Yes, that's what Execute is for. Just be sure to use your names.

[lithium]

It's giving me a syntax error on "INSERT INTO"

[lithium]

Fixed it a bit and now it says that I can't reference a property or control unless the control has the focus....

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.

[lithium]

With a little bit of fixing some missing ' in there, I got her working! Thank you!

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

[June7]

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.

[lithium]

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

[Forms]![Main1]![lstActive].Requery