check if form loaded and control on subform blank

[Maresdd]

I have written some vba code to check if a form is loaded. If the form is
loaded then it is to populate a control on another form with the control
value. The problem I'm having is that if that control is blank then I get an
error message. How do I write this correctly. What I have so far is:

Private Sub Form_Open(Cancel As Integer)
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded And Not
IsNull(Forms!frmclientenqaccs!clientenqaccssubform![Service No]) Then

Me.Service_Plan_ID = Forms!frmclientenqaccs!clientenqaccssubform![Service No]
Call Service_Plan_ID_AfterUpdate


End If
End Sub

Thank you in advance.

 

[Allen Browne]

Break the test of null into a separate line, since that test will error if
the form isn't open:

Private Sub Form_Load()
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded Then
With Forms!frmclientenqaccs!clientenqaccssubform![Service No]
If Not IsNull(.Value) Then
Me.Service_Plan_ID = .Value
Call Service_Plan_ID_AfterUpdate
End If
End With
End If
End Sub

I've suggested Form_Load rather than Form_Open, due to the timing (i.e. you
need the data loaded first.) Note that this changes the *first* record in
the form: are you absolutely certain this will always be the record you want
altered?

 

[Maresdd]

I've tried this and I get an error message:
Run-time error 2427: You entered an expression that has no value.
I don't understand what you mean when you asked the question "are you
absolutely certain this will always be the record you want
altered?"
I'm not altering anything, I'm asking it to load the form with the value of
the Service No in the new form.

Thanks for your quick response by the way.

Break the test of null into a separate line, since that test will error if
the form isn't open:

Private Sub Form_Load()
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded Then
With Forms!frmclientenqaccs!clientenqaccssubform![Service No]
If Not IsNull(.Value) Then
Me.Service_Plan_ID = .Value
Call Service_Plan_ID_AfterUpdate
End If
End With
End If
End Sub

I've suggested Form_Load rather than Form_Open, due to the timing (i.e. you
need the data loaded first.) Note that this changes the *first* record in
the form: are you absolutely certain this will always be the record you want
altered?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have written some vba code to check if a form is loaded. If the form is
loaded then it is to populate a control on another form with the control
value. The problem I'm having is that if that control is blank then I get
an
error message. How do I write this correctly. What I have so far is:

Private Sub Form_Open(Cancel As Integer)
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded And Not
IsNull(Forms!frmclientenqaccs!clientenqaccssubform![Service No]) Then

Me.Service_Plan_ID = Forms!frmclientenqaccs!clientenqaccssubform![Service
No]
Call Service_Plan_ID_AfterUpdate


End If
End Sub

Thank you in advance.

.

 

[Allen Browne]

Which line gives the error? If it's the With line, try adding the .Form
With Forms!frmclientenqaccs!clientenqaccssubform.Form![Service No]
Explanation of why:
http://allenbrowne.com/casu-04.html

This code assigns a value to the Service_Plan_ID field (text box?) on your
form. So, say the form opens with an existing record where the
Service_Plan_ID is 7. Your code changes the 7 into whatever the Service No
was on the other form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've tried this and I get an error message:
Run-time error 2427: You entered an expression that has no value.
I don't understand what you mean when you asked the question "are you
absolutely certain this will always be the record you want
altered?"
I'm not altering anything, I'm asking it to load the form with the value
of
the Service No in the new form.

Thanks for your quick response by the way.

Break the test of null into a separate line, since that test will error
if
the form isn't open:

Private Sub Form_Load()
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded Then
With Forms!frmclientenqaccs!clientenqaccssubform![Service No]
If Not IsNull(.Value) Then
Me.Service_Plan_ID = .Value
Call Service_Plan_ID_AfterUpdate
End If
End With
End If
End Sub

I've suggested Form_Load rather than Form_Open, due to the timing (i.e.
you
need the data loaded first.) Note that this changes the *first* record in
the form: are you absolutely certain this will always be the record you
want
altered?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have written some vba code to check if a form is loaded. If the form
is
loaded then it is to populate a control on another form with the
control
value. The problem I'm having is that if that control is blank then I
get
an
error message. How do I write this correctly. What I have so far is:

Private Sub Form_Open(Cancel As Integer)
If CurrentProject.AllForms("frmclientenqaccs").IsLoaded And Not
IsNull(Forms!frmclientenqaccs!clientenqaccssubform![Service No]) Then

Me.Service_Plan_ID =
Forms!frmclientenqaccs!clientenqaccssubform![Service
No]
Call Service_Plan_ID_AfterUpdate


End If
End Sub

Thank you in advance.

.

 

[Stephen Rufe]

I have a similar issue...

I have a bound form named "Improve Team Form". Its recordsource is a table named "tbl_suggestion" with the pk of "suggestion_pk". On the form there is a field named "reviewed_date" that I have code in the beforeupdate event to check to ensure it is not "empty". Here is my issue...

I want to launch a second form (from a switchboard like form) named "Improve Team Queries", but I would like to check to see if the "Improve Team Form" isloaded. If it is loaded, then I would like to check to see if "date_reviewed" is "empty" and if so, display a messagebox informing the user that he/she needs to populate the "date_reviewed" field. If the "Improve Team Form" isloaded and the "date_reviewed" field is not "empty" then I would like the "Improve Team Form" to close and then open the "Improve Team Queries" form. And lastly if the "Improve Team Form" is not loaded, then I simply want the "Improve Team Queries" form to open.

Will someone please provide me with some code by which I can achieve this?