Reseting Textbox Bound to Required Date Field

[Guest_imported]

I have a form with a unique identification number which makes up a primary key together with a date and time. I've made the date and time required, but I wish to have a button to reset all the fields except the ID to add a new record for it (similar to how it looks for an all new record, but keeping the ID field which cannot be edited). However, MS Access 97 will not allow me to update the date or time's TextBox.Value with ""... anyone have an idea for a workaround?

Thanks,
Derek MacDonald
derekmd@hotmail.com

 

[jgoodman00]

I imagine that is because Allow Zero Length is false. You can either set this to true (found in table design grid), or better still change the zero length string assignation ("&quot to Null

HTH
James Goodman

 

[Guest_imported]

Run-time error '3314': The field 'start date' can't contain a Null value because the Required property for this field is set to True. Etner a value in this field.

I do not wish to allow the user to insert blank fields for the primary keys. I noticed Access is updating the record when I change the value or focus of the current field in the form... how do I disable automatic record updates and make the user explicity click an update/save button?

Thank you,
Derek MacDonald
derekmd@hotmail.com

 

[jgoodman00]

This sounds like a bit of a circular reference, in as much as you want to set a field to be empty, but a rule enforces it not to be null!!

If you change Allow Zero Length in table design for the relevant fields this should solve the above problem.

The second is quite simple. You need to create an event in the before_update event for the [bold]form[/bold]. This way, if a record is updated, before the changes are appended to the database the specified event will run. Within this event you need something similar to the following:


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("The record has been modified, do you want to save changes?", vbYesNo) = vbYes Then
'do nothing
Else
DoCmd.RunCommand acCmdUndo
End If
End If
End Sub James Goodman

 

[Guest_imported]

Thanks a lot, but that really doesn't work well. Each time the user changes the focus to enter values in another field, they get that popup window. When there's more than 10 fields, that can get a bit annoying. I'd rather have an Update button at the bottom of the form with Update for the whole form disabled altogether. I thought there may be a command for disabling save like asCmdSaveRecord is for saving.

 

[jgoodman00]

Have you placed this code in the before_update event of the form, or each of the controls. If it is in the form event it should only trigger when the record is changed, because that is when the save command is issued. Does the form contain any subforms, because tabbing in & out of these would produce the same phenomenon???

James Goodman

 

[Afterglew]

Ok, well there is a subform so that's why the message box is popping up.

I'm also having a problem when I tab out of the subform, all the data entered in the textboxes in the subform disappear AND the tab order isn't correct even though I've set it the way I wanted... it automatically jumps to the bottom to a subform ListBox rather than the first TextBox at the top. Ugh, Access is really annoying that way, doing things I do NOT want it to do.

Derek MacDonald
derekmd@hotmail.com