INSERT action in VBA

[Jllera]

I need some help good people!



I am trying to write a code for an add new button on one of my forms. This button is suppose to add the note that is entered on the form into the Notes table. I've done some research and tried to create the code myself but haven't been able to get it to work. This is what I have so far:


Private Sub Add_New_Click()

Dim dbs As Database

'Add this line to the CommNotes table.

Set dbs = OpenDatabase("DatabaseName", As String, As Database")

' Select specified records from FormName and add them to the CommNotes table.
dbs.Execute "INSERT INTO CommNotes"

End Sub

As you can see Im too familiar with how the constants and function work (meaning the language and how to input into the module in the correct order). Does anyone know how to do this? Can you understand what I am trying to accomplish?

[drexasaurus]

You don't usually need to open the database like that unless you are opening a file other than the one that is currently open. Usually you'd say something like:

Set dbs = CurrentDb 'This is the currently open database

And after you're done...

Set dbs = nothing

As far as the execute string, it doesn't seem complete (unless it's just an example of the kind of thing going on). I usually write in a different kind of SQL, but it would look something like this:

"INSERT INTO CommNotes VALUES(""Dog"", ""Cat"", #1/22/2014#, True, 17)"

You may want to search on SQL strings in general. This link seems okay:
https://support.office.com/en-us/art...rs=en-US&ad=US

[Jllera]

Thanks. I'll give it a try and I'll also check out your site.

[ssanfu]

Can you understand what I am trying to accomplish?

Not yet.

' Select specified records from FormName and add them to the CommNotes table.

Would you explain a little more about the structure (tables) of your dB and about the form?
I don't understand why you are trying to add a button to save"CommNotes". It appears that "FormName" is a bound form; the data would automatically be saved. No need for a save button.

[June7]

Data is committed to table when:

1. close table or bound form

2. move to another record

3. run code

Maybe you really need a form/subform arrangement.

[Jllera]

thanks guys. I got it.. My struggle now is my Save & New button. I want this button to save the current form and open a fresh blank form. this what I have for it so far.

Private Sub Save_New_Click()
DoCmd.Save acForm, "FormName"
DoCmd.?
End Sub
the ? mark indicates me not knowing what to use... lol.. sorry people, I'm new to this..

[June7]

The code is wrong approach. You don't save the form, you save the record.

Remember what I said about when record is committed? Just close the form or move to another record, even new record row.

DoCmd.GoToRecord , , acNewRec

[Jllera]

Thanks. I'll give that a try.