Subform Validation

[jhazard]

Hi there

I have a form with a sub form embedded. All I want to do is when the user adds a new record and clicks the navigation bar to validate several fields on the main form and several on the sub form. Can I do it?! Grrrr....

I put all the code in the beforeupdate event of the main form, however for some reason i can't set the focus to the sub form so can only validate the mainform fields. Surely there is a bog standard way to do this? Can anybody help?

Jenny

 

[casey]

Have you tried putting the VBA in the Form's OnCurrent Event instead? If not, give it a try and let me know.

 

[Lamb2087]

Validation

Have you tried the validation code in just th subform?

 

[jhazard]

I have put this code in my subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CustomerName]) Then
MsgBox ("You must fill out a customer name!")
Me!CustomerName.SetFocus
End If
End Sub

the validation code in the main form is being fired but this seems to get ignored and lets me move onto the next record without fillingin a customer name.

 

[Lamb2087]

Validation - Name

Try this and change to meet your text box names.


Private Sub txtITEM_DESC_KeyPress(KeyAscii As Integer)
On Error GoTo Err_tbItemDesc_KeyPress

'Convert keyed characters to uppercase and reject keyed numbers
If KeyAscii >= Asc("0") And KeyAscii <= Asc("9") Then
KeyAscii = 0
Beep
MsgBox "''Invalid Entry'' You can not enter a number.", vbInformation, "Invalid Item Description"
ElseIf KeyAscii = Asc("/") Or KeyAscii = Asc("\") Or KeyAscii = Asc("'") Or KeyAscii = Asc(".") _
Or KeyAscii = Asc("@") Or KeyAscii = Asc("#") Or KeyAscii = Asc("$") Or KeyAscii = Asc("\") _
Or KeyAscii = Asc("(") Or KeyAscii = Asc(")") Or KeyAscii = Asc("*") Or KeyAscii = Asc("-") Or KeyAscii = Asc("_") Then
KeyAscii = 0
Beep
MsgBox "''Item Description'' can not contain any non alpha characters.", vbInformation, "Invalid Item Description"
Else
KeyAscii = Asc(LCase(Chr(KeyAscii)))
End If

Exit_tbItemDesc_KeyPress:
Exit Sub

Err_tbItemDesc_KeyPress:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_tbItemDesc_KeyPress

End Sub

 

[jhazard]

Not sure that helps me much as if I'm correct it relies on the user having typed in a particular box. I need my text boxes to be validated when a record is about to be added. Have I misunderstood? Any other suggestions anyone, this is driving me crazy, I refuse to think it can be this hard!

 

[Rich]

What validation do you need?

 

[jhazard]

I just need to check that a couple of fields on my main form are filled out and a couple on my subform are filled out. Thats it.

 

[Rich]

It can be done to a point, but it requires quite a lot of code, might you find it easier to use a query when opening the db to look for missing child records

 

[jhazard]

No, I don't want to do that. Oh well back to the drawing board

 

[Rich]

Option Compare Database
Option Explicit

Dim CanClose As Boolean, CanOpen As Boolean

Sub Form_Current()
On Error GoTo Form_Current_Err

Me.txtCurrRec = Form.CurrentRecord
Me.txtTotalRecs = Form.RecordsetClone.RecordCount + IIf(Form.NewRecord, 1, 0) & " " & "(filtered)"



If NewRecord = True Then

Me.ToggleLink.Visible = False
Me.Command85.Visible = False
Me.Command78.Visible = False
Me.Command82.Visible = False
Me.Command88.Visible = False
txtPMID.SetFocus
Else:

Me.ToggleLink.Visible = True
Me.Command85.Visible = True
Me.Command82.Visible = True
Me.Command78.Visible = True
Me.Command88.Visible = True
End If


Form_Current_Exit:
Exit Sub

Form_Current_Err:

MsgBox Error$
Resume Form_Current_Exit


End Sub

'Hides various buttons on the main form


Private Sub Form_Unload(Cancel As Integer)
If CanClose = False Then
Cancel = True

End If
End Sub


Public Sub Val()
On Error GoTo Err_Val
If Not IsNull(Me.CustomerID) And IsNull(Forms![Customers]![Properties owned subform].Form.Address) Then
DoCmd.CancelEvent
MsgBox "You have not entered the relevant address(es)"
PropertiesOwnedsubform.SetFocus
CanClose = False
CanOpen = False
Else
CanClose = True
CanOpen = True
End If
Exit_Val:
Exit Sub

Err_Val:
MsgBox Err.Description
Resume Exit_Val
End Sub


On my form I have custom navigation buttons which test for CanClose/Open, you may find it easier to Hide/Display the in built nav buttons



Private Sub Command91_Click()
On Error GoTo Err_Command91_Click
Call Val
If CanClose Then

DoCmd.GoToRecord , , acFirst
End If
Exit_Command91_Click:
Exit Sub

Err_Command91_Click:
MsgBox Err.Description
Resume Exit_Command91_Click

End Sub

 

[jhazard]

Pat, I realise you said that before and it sounds perfect but i don't know how to execute it. Do you have a code example? If I put the code in the before update event of the subform it is ignored and a new record is created after the mainform validation is performed. I'm a bit of a novice at access.