forms/sub-forms and modules

[Awes]

Hi Everyone

Starting to tie myself up in knots here so it's time to call on the experts for help again. As usual I apologies for the length of the message but hopefully it is detailed enough to get the answer first ime round.

I am developing a database where a sub-form is used within several different forms.

The sub-form is used to enter information and create a new record. Once the User has entered the relevant information they click on a button.

A check is carried out to ensure that the boxes the client has stipulated as mandatory have been completed and then the record is saved.

I thought it would be a good idea to have a stand alone module to hold the code used to carry out the checking and saving as it is a common process.

Having written the code I am having trouble running it as I keep getting a runtime error (No 5) telling me that there is an Invalid procedure call or argument.

Having done some debug tracing the module is being called ok and the variable I am passing (the name of the form calling the module) appears to transfer to the module, however once the code starts running it is basically telling me that it can't find the form that I have specified.

I'm sure the problem lies in the way that I'm referring to the form/sub-form.


The module code is shown below:

'-------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Public Sub subSaveVehicle(strFormName As String)

'The variable strFormName is the name of the form from which
'this module was called from.
'
'-------------------------------------------------------------------------------
'Declare variables.

Dim dbeMyDb As Database
Dim rstMyset As Recordset
Dim blnErrorStatus As Boolean

'-------------------------------------------------------------------------------
'Initially sets the ErrorStatus flag to FALSE to identify any unexpected errors.

blnErrorStatus = False

'-------------------------------------------------------------------------------
'On error goes to the error handling code.

On Error GoTo ErrorProc

'-------------------------------------------------------------------------------
'Checks the mandatory boxes on the sub-form to ensure that
'there are no empty boxes.
'If one or more empty mandatory boxes are detected the
'ErrorStatus flag is 'set to TRUE to identify a user error.

'This is where the error is occurring, it is telling me it can't find
'strFormName.

If IsNull(Forms!strFormName!frmSubFormName!txtName1) _
Or IsNull(Forms!strFormName!frmSubFormName!txtName2) _
Or IsNull(Forms!strFormName!frmSubFormName!txtName3) Then
blnErrorStatus = True
GoTo ErrorProc 'On error goes to the error handling code.

End If

REST OF CODE GOES HERE

There are other references within the code to
Forms!strFormName!frmSubFormName!xxxxxxx
but the above should be enough to identify the
problem, I hope.

FINISHED:

Exit Sub

'Error handling code.
'If the error is a User error (blnErrorStatus = True) caused by information missing
'from the sub-form a custom error message is displayed.
'Any other error (blnErrorStatus = False) will display the Error Code Number and
'Description.

ErrorProc:

If blnErrorStatus = True Then
MsgBox "Check that all the mandatory boxes have been completed.", _
vbOKOnly, "Missing Information !"
Else
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, vbCritical, "Error!"
Resume FINISHED

End If

Resume FINISHED

End Sub

'-------------------------------------------------------------------------------

Any help would be greatly appreciated. Further info can be provided if necessary.

Many thanks

Awes

 

[dcx693]

You are trying to refer to a subform control here:
If IsNull(Forms!strFormName!frmSubFormName!txtName1)

The syntax is tricky. Try this:
Forms!strFormName!frmSubFormName.Form!txtName1

 

[Awes]

Hi dcx693

Thanks for coming back with a possible solution. I have tried it but it is still falling over with a run time error No 5.

It is definitely the statement:

If IsNull(Forms!strFormName!frmSubFormName!txtName1) _
Or IsNull(Forms!strFormName!frmSubFormName!txtName2) _
Or IsNull(Forms!strFormName!frmSubFormName!txtName3) Then

that is causing the problem.

I have put the following, using quick watch, into into the debug window and got the following results:

Variable - strFormName

Response - "Name of form calling the module"

Variable - Forms!strFormName!frmSubFormName.Form!txtName1

Response - "Microsoft Access can't find the form 'strFormName' referred to in a Macro expression or Visual Basic code. The form you referred to may be closed or may not exist in this database. Microsoft Access may have encountered a compile error in a Visual Basic module for the form."

This message relates to Error No 2450.

As the form is open, it must be a compiling error and presumably due to the fact that the format of the command is incorrect.

So I'm still stumped at this point.

Any ideas ????

Cheers

Awes.

 

[Awes]

Update

I have done a bit more experimenting with this problem, so thought I would add my results in case it assists the problem solving.

I replaced the variable name, strFormName, with the actual name of one of the forms that will use the module and ran the code without a problem.

So the problem is the way that the variable name is being defined / used within the code:

Forms!strFormName!sub-FormName.Form!txtName1

The variable is being set correctly at the start of the module this was checked by using the debug window.

I have tried putting the variable in ( ), [ ] and " " but none of these work either.

Anyone got any ideas ??

Cheers

Awes.

 

[Rich]

Use this function instead
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
Dim Msg, Style, Title, Response, MyString
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") > 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If

If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If
End If
End If

Next
End Function

 

[Awes]

Hi Rich

Thanks for the info.

I have used your function and it is just what I was looking for.

It works perfectly and it has opened up a whole new area for me to explore and experiment, as I haven't used the approach taken by the function before.

Thanks again.

Cheers

Awes.