Conflicting requirements between popup forms and main form's fields (1 Viewer)

[Banana]

I have set certain fields on my main form to be required to protect from accidnetal skipping which has occured in past. However, in middle of form, there are a group of checkboxes, which opens their correndsponding popup forms for more details.

Right now, I get an error if it try to pop up a form because not all required fields are filled in.

How do I make Access suspend the requirments whenever the checkboxes are checked and additional info are being inputed?

Edit= tried setting it as dialog boxes, but to no avail.

 

[Banana]

Hmm... No response?

Could anyone at least tell me if it's possible to create a event that fires whenever current record is changed to check if all required fields are satisfied before moving onto new record but allows for pop up forms?

I looked at the form properties, event tab but didn't see any events that would be limited to current record only.

 

[MartijnR]

I once read about a similar problem, have you tried before_update ?

I guess you have trouble because access tries to save the record when opening the popups

 

[Banana]

Yes- it is coded to save records before going to popup forms- that was to ensure that there won't be a orphan as they're related.

 

[carash77ash]

Change the required field to no. Then on form close and change_record events add a validation rule
eg
Private Sub form_Close()
On Error GoTo Err_Form_Close


Dim strTblName As String, strValidRule As String
Dim strValidText As String
Dim intX As Integer


strTblName = "tablename"
strfldname = "fieldname"
strValidRule = "Not IsNull"

strValidText = "Type message text here"
intX = SetTableValidation(strTblName, strValidRule, strValidText)
Else
End If

Err_Form_Close:
GoTo Exit_Form_Close

Exit_Form_Close:
Exit Sub

End Sub


Function SetTableValidation(strTblName As String, _
strValidRule As String, strValidText As String) _
As Integer

Dim dbs As Database, tdf As TableDef, field As field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTblName)
Set fld = tdf.Fields(strfldname)

fld.ValidationRule = strValidRule
fld.ValidationText = strValidText
End Function


This makes the field required.

Ash

 

[Banana]

Great! Will code that in-

I did a quick check and while I see a field for close event, I don't see any record change. Did you mean Data Change or Data Change Set event?

 

[carash77ash]

sorry i actually tested code and there is a problem. will get back to you. on form before update is record change.

Ash

 

[carash77ash]

found easier way.

Private Sub Form_BeforeUpdate()
on error goto err_form_beforeupdate

If IsNull(fieldname) Then
x = MsgBox("fieldname is required")
cancel = True
Me.fieldname.SetFocus
Else
End If

Err_form_BeforeUpdate:
goto exit_form_beforeupdate

exit_form_BeforeUpdate:
Exit Sub


End Sub

Private Sub form_Close()
On Error GoTo Err_Form_Close

If IsNull(fieldname) Then
x = MsgBox("fieldname is required")
cancel = True
Me.fieldname.SetFocus
Else
End If

Err_Form_Close:
GoTo Exit_Form_Close

Exit_Form_Close:
Exit Sub

End Sub

 

[Banana]

Carash,

Thanks so much for you help.

I loaded the code, but it didn't do anything to solve the problem because (I presume) before_update events interrupts the open_form event and it then fail. After_update was ineffective in enforcing the rules as well.

I think I may be better off with a different layout, unless there's any other suggestions.

Again, thanks.

 

[Laurentech]

It looks to me like carash's code should work as long as you have the field's Required property set to no on the table. I use the same sort of thing, but I only use the code in the Form_Close event and don't do anything in beforeupdate.

Larry