Save current record in VB

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,
I've gone blank... I want to save the current record on a form from
within VB code, after changing a bound control. What's the easiest way to
do this, shy of requerying the form?

- Max
 
Max - this will save the record:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

If you want to trap any errors, you can use something along these lines -
this particular one applies to the Click event of a form control named
"Paid."

Private Sub Paid_Click()
On Error GoTo Err_Paid_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Paid_Click:
Exit Sub

Err_Paid_Click:
MsgBox Err.Description
Resume Exit_Paid_Click
End Sub

FYI: one way to get information of this type is to take advantage of the
code the Form control wizard creates for you when you use it to place a
command button on a form in Design View. One of the choices is a button
that will save a record for you. I use this feature often.

HTH

Paul
 
If you'd like to do it after changing the value of a bound control, you can
use the control's After Update event.
 
Hi Max

Either use:
DoCmd.RunCommand acCmdDeleteRecord
or
Me.Dirty = False

The wizard generates horrible obsolete code using DoMenuItem from Access 95.
Avoid it!
 
Sounds like you might have a better way to do it, but did you really mean
acCmdDeleteRecord?
 
Oh dear! Time to go back to bed, I think.

You're right - I meant acCmdSaveRecord :-/
 
Hi Graham,

I have the following bit of code which works beautifully on existing
records, but gives the error: 'The Command or action 'SaveRecord' isn't
available now'. Here is the code. Why is it tripping over New Records?:

Private Sub CommandGoToSwitchboard_Click()
On Error GoTo Err_CommandGoToSwitchboard_Click
If Me.NewRecord Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "fSwitchboard"
Else
DoCmd.OpenForm "fSwitchboard"
End If
Exit_CommandGoToSwitchboard_Click:
Exit Sub

Err_CommandGoToSwitchboard_Click:
MsgBox Err.Description
Resume Exit_CommandGoToSwitchboard_Click
End Sub

Thanks, Patrick

Graham Mandeno said:
Oh dear! Time to go back to bed, I think.

You're right - I meant acCmdSaveRecord :-/

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Paul James said:
Sounds like you might have a better way to do it, but did you really mean
acCmdDeleteRecord?
Click to expand...
Click to expand...
 
If the Form is on a NewRecord which has not been dirtied, i.e. no data entry
done, you cannot save it. Hence, you need to check the Form's Dirty
Property before trying to save the Record.

OTOH, why do you need to explicitly save the Record? Since you use bound
Form, the Record will be saved automatically (if validation is OK) when you
close the bound Form.

--
HTH
Van T. Dinh
MVP (Access)




Pat Dools said:
Hi Graham,

I have the following bit of code which works beautifully on existing
records, but gives the error: 'The Command or action 'SaveRecord' isn't
available now'. Here is the code. Why is it tripping over New Records?:

Private Sub CommandGoToSwitchboard_Click()
On Error GoTo Err_CommandGoToSwitchboard_Click
If Me.NewRecord Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "fSwitchboard"
Else
DoCmd.OpenForm "fSwitchboard"
End If
Exit_CommandGoToSwitchboard_Click:
Exit Sub

Err_CommandGoToSwitchboard_Click:
MsgBox Err.Description
Resume Exit_CommandGoToSwitchboard_Click
End Sub

Thanks, Patrick

Graham Mandeno said:
Oh dear! Time to go back to bed, I think.

You're right - I meant acCmdSaveRecord :-/

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Paul James said:
Sounds like you might have a better way to do it, but did you really mean
acCmdDeleteRecord?
Click to expand...
Click to expand...
Click to expand...
 
Hi Van,

The reason to explicitly save the record is that this form remains open as
the data entry people enter in patient records in batches, and having to
close the form (and thus the entire application as I 'hide' the Database
Window) in order to 'see' the records that have been recently entered slows
them down. Somtimes they have to review previously-entered forms, and
sometimes they will only receive some of a particular patient's data at
first, but will receive the rest before they are done with a particular batch
(i.e., before closing out the application), and the ability to 'see' that
patient helps them a lot.

I put a check in the code behind the Command Button that opens up my custom
'Switchboard' that 'dirties' the form if it is a New Record and THEN opens
the Switchboard. In the 'On Enter' property of the Combo Box that then shows
the list of patient records ('Patient ID' and 'Patient Initials'), I put:

Me.Refresh

and it seems to work like a charm. Records are saved and more immediately
visible in the Combo Box. Are there any 'pitfalls' to be aware of doing it
this way? Like I said, it seems to work fine, but I like to be sure.

Thanks!

Van T. Dinh said:
If the Form is on a NewRecord which has not been dirtied, i.e. no data entry
done, you cannot save it. Hence, you need to check the Form's Dirty
Property before trying to save the Record.

OTOH, why do you need to explicitly save the Record? Since you use bound
Form, the Record will be saved automatically (if validation is OK) when you
close the bound Form.

--
HTH
Van T. Dinh
MVP (Access)




Pat Dools said:
Hi Graham,

I have the following bit of code which works beautifully on existing
records, but gives the error: 'The Command or action 'SaveRecord' isn't
available now'. Here is the code. Why is it tripping over New Records?:

Private Sub CommandGoToSwitchboard_Click()
On Error GoTo Err_CommandGoToSwitchboard_Click
If Me.NewRecord Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "fSwitchboard"
Else
DoCmd.OpenForm "fSwitchboard"
End If
Exit_CommandGoToSwitchboard_Click:
Exit Sub

Err_CommandGoToSwitchboard_Click:
MsgBox Err.Description
Resume Exit_CommandGoToSwitchboard_Click
End Sub

Thanks, Patrick

Graham Mandeno said:
Oh dear! Time to go back to bed, I think.

You're right - I meant acCmdSaveRecord :-/

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sounds like you might have a better way to do it, but did you really mean
acCmdDeleteRecord?
Click to expand...
Click to expand...
Click to expand...
 
Do you mean that the ComboBox you mentioned in the last paragraph is on you
custom "Switchboard" form?

If that the case, timing is the likely cause which is overcome by your
refresh. The Record you have just saved is likely still to be in cache and
has not actually been updated into the Table and therefore not picked up by
the ComboBox RowSource. By the time the refresh (Requery???) is executed, it
is in the Table and therefore selected into the ComboBox list.

Try changing the code to:

....
If (Me.NewRecord) And (Me.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents
End If

DoCmd.OpenForm "fSwitchboard"
....

and most likely, you don't need refresh / requery since the code forces the
actual updating of the Record into the Table.
 
Back
Top