BeforeInsert v BeforeUpdate

[RuhailRana]

Hi Experts

MS Access is not my forte but have used it to complete a couple of projects in the past (Access 97 and 2003). I am developing this one using MS Access 2007.

I am using the BeforeInsert Event on my Form to automatically increment the primary key field as the user clicks into the form. I have not used autonumber as was advised against it. This works well.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.CoachID = Nz(DMax("CoachID", "Coach table"), 0) + 1
If Me.Dirty Then Me.Dirty = False
End Sub

However, I have now come to the point where I need to validate data entries in the form. I have 2 problems.

1. Every time I place the following code in the BeforeUpdate event the form automatically asks the user to complete the missing fields however the user has'nt even completed any part of the form (the fields are samples to test the code). Is the BeforeInsert Event causing this?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Email & "" = "" Then
MsgBox "Please fill in Email", vbOKOnly
Cancel = True
Me.Email.SetFocus
Exit Sub
End If
If Me.Gender & "" = "" Then
MsgBox "Please fill in Gender", vbOKOnly
Cancel = True
Me.Gender.SetFocus
Exit Sub
End If
End Sub

2. I also need to validate a subform. After reading around I have experimented with this

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.spec.Form!Subject & "" = "" Then
MsgBox "Please fill in Subjecr", vbOKOnly
Cancel = True
Me.spec.Form!Subject.SetFocus
Exit Sub
End If

I am trying to point to the sub form. Will this work? Is the order important?The ones I practised with would not let me make changes to field.

Any help on this will be greatly apprecaited
Kind Regards
R

 

[rodmc]

if you wish to do that then force SetFocus to the box you want populated and the put some code under on LostFocus like

If Me.txtYourcontrol = "" then
msgbox "Yourcontrol must be populated", vbOKOnly, "Warning!"
me.txtYourcontrol.SetFocus

 

[vbaInet]

1. For increment use the AutoNumber data type or put the DMax() code in the Default Value property of the textbox.

2. Keep the code in the Before Update event and you can follow this syntax:

Me.spec.[COLOR=Red]SubformControlName[/COLOR].[COLOR=Blue]TextboxName[/COLOR].SetFocus

 

[RuhailRana]

Hi VbaInet

Thanks.

1. I have put DMax() code in the Default Value property of the textbox. (fyi - The textbox is a primary key). This seems to be working well.

2. I have kept the code in the form before update event

However, I cannot seem to edit the information on the subform (just 1 drop down menu linked by FK). It comes up with the message "Please fill in the field ..." but access does not let me make the changes. It won't let me click into the drop down menu in the subform.

AND, if I take this part of the code out, access forces me to complete the validated fields on the main form before I can attempt to complete the subform....(I am using form tabs too)

I have checked the Link Master/Child fields. They are linked using a primary key which also happens to be the increment field I am using for part 1 of this problem.

What am I doing wrong? How can I validate the subform?

(also looking at the setfocus/lost focus suggestion but not getting far with this too)

Thanks in advance
Regards
R

(p.s I have 2 other subforms to validate in this form too)

Below is the code I am using

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CoachtoSubjectTable2.Form.Combo11 & "" = "" Then
MsgBox "Please fill in Subject", vbOKOnly
Cancel = True
Me.CoachtoSubjectTable2.Form.Combo11.SetFocus
Exit Sub
End If
If Me.Email & "" = "" Then
MsgBox "Please fill in Email", vbOKOnly
Cancel = True
Me.Email.SetFocus
Exit Sub
End If
If Me.Gender & "" = "" Then
MsgBox "Please fill in Gender", vbOKOnly
Cancel = True
Me.Gender.SetFocus
Exit Sub
End If
End Sub

 

[vbaInet]

Upload a stripped down version of your db and explain the steps to reproduce the problem.