Add Record Command Button Help!!

[Chris]

I have created a form that has several fields (combo boxes and text
boxes) for data entry into a table. I would like be able to add a
command button that adds the entered information into the table then
resets the form (blanks) after that record is added. A popup that
says "New Record Has Been Added" would be nice, but optional.

I have already tried the following with no luck:

Private Sub pwdsame_submit_Click()
On Error GoTo Err_pwdsame_submit_Click


DoCmd.GoToRecord , , acAddNew

Exit_pwdsame_submit_Click:
Exit Sub

Err_pwdsame_submit_Click:
MsgBox Err.description
Resume Exit_pwdsame_submit_Click

End Sub

Can someone give me a step by step; I am a novice at Access.

Thanks in advance.

 

[Guest]

The issue is that once you goto a new record, you've essentially already
added it. If you goto a different record or close the applciation, you'll
still have that new record out there. Based on what you said you wanted, I'd
use an UNBOUND form, and the ADD button then executes some ADO code to add a
new record and write the values out from the unbound form.

Some saple code:

Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset

Set dbConn = CurrentProject.Connection
Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic
recSet.Open "[YourTableNameHere]", dbConn, , , adCmdTable

recSet.AddNew ' Start a new record
recSet!RepPWID = Me.YourFirstField
recSet!ClientFname = Me.YourSecondField
recSet!ClientMI = Me.YourThirdField
(etc, with as many fields as you need to add)

recSet.Update ' Post the new record
recSet.Close

Set recSet = Nothing
dbConn.Close

 

[John W. Vinson]

I have created a form that has several fields (combo boxes and text
boxes) for data entry into a table. I would like be able to add a
command button that adds the entered information into the table then
resets the form (blanks) after that record is added. A popup that
says "New Record Has Been Added" would be nice, but optional.

Why write code to do what Access does automatically with no trouble at all???

Just moving to a new record - with the navigation buttons or (if you prefer) a
wizard-generated "go to the new record" button - saves the existing data and
brings up a blank record, already. You can put a Msgbox in the form's
AfterUpdate event if you REALLY want to annoy your users by popping up a
message to which they must respond (muttering "yes I know it added a new
record, that's what I was trying to DO").

THat said...

Private Sub cmdSave_Click
If Me.Dirty = True Then
Me.Dirty = False ' save the current record
End If
MsgBox "New Record has been added", vbOKOnly
DoCmd.GoToRecord acDataForm, Me.Name, acNewRecord ' go to new record
End Sub

John W. Vinson [MVP]