Code that runs when the record moves from current to next

[Sajit]

I have the following code for a parent form and 2 child sub forms.

Private Sub Form_Current()

sql1 = "SELECT Desig, Det FROM [List of standards] WHERE Desig = '" _
& Me![Desig] & "'"

Me.Parent![Child36].Form.RecordSource = sql1

Forms![List of Standards]![Child36].Form.Refresh

End Sub

[List of standards] is the parent form and [child36] one of the child form

This code results in an error when the form is first opened as the forms are
not intialised when opening the parent form. How do I avoid this. What is the
correct way of doing this.

 

[Clifford Bass]

Hi Sajit,

Really basic questions: Is "List of standards" also a table. Is it
being used as the record source of the main/parent form? Regardless of that,
why not get rid of that code and set the record source of the subform to
"List of standards"? Then link the subform to the main form using the Desig
field of both?

Clifford Bass

 

[Sajit]

I wanted to copy the report which the documenter would have given me. On
trying to get this, I get the message,

'doc_tblobjects' already exists, however I don't see any table. This
probably is a message I am getting after I changed the workgroup file for
the data base.

Yes, [List of standards] is a table also.

There are 2 tables, Typ and [List of Standards] linked through a common
text field, Typ.

The parent form [List of Standards] has a uncontrolled combo box row
sourced to query, temp5

Under the parent form there are 2 child forms,

The [query1 subform ] child form displays all records for the selected Typ
in the parent form combo box. [query1 subform ] is Record sourced to
query, query3.
query3 is made from table [List of standards]

The child36 displays the Det field for each current record selected in
[query1 subform]. The link is the desig field. desig field is only in
table, [List of standards] and not table, Typ. child36 is Record sourced to
query, qry_det.
qry_det is made from table, [List of standards]
--
Sajit
Abu Dhabi

Hi Sajit,

Really basic questions: Is "List of standards" also a table. Is it
being used as the record source of the main/parent form? Regardless of that,
why not get rid of that code and set the record source of the subform to
"List of standards"? Then link the subform to the main form using the Desig
field of both?

Clifford Bass

I have the following code for a parent form and 2 child sub forms.

Private Sub Form_Current()

sql1 = "SELECT Desig, Det FROM [List of standards] WHERE Desig = '" _
& Me![Desig] & "'"

Me.Parent![Child36].Form.RecordSource = sql1

Forms![List of Standards]![Child36].Form.Refresh

End Sub

[List of standards] is the parent form and [child36] one of the child form

This code results in an error when the form is first opened as the forms are
not intialised when opening the parent form. How do I avoid this. What is the
correct way of doing this.

 

[Sajit]

Forgot to mention that the [List of standards] is a data sheet view form
displaying multiple records. The Child36 has only a singe record
corresponding to each record in the form [List of standards]. The Child36
displays the memo field in a fairly large control.
Perhaps this explains why the code is required to display the Child36 records.
--
Sajit
Abu Dhabi

Hi Sajit,

Really basic questions: Is "List of standards" also a table. Is it
being used as the record source of the main/parent form? Regardless of that,
why not get rid of that code and set the record source of the subform to
"List of standards"? Then link the subform to the main form using the Desig
field of both?

Clifford Bass

I have the following code for a parent form and 2 child sub forms.

Private Sub Form_Current()

sql1 = "SELECT Desig, Det FROM [List of standards] WHERE Desig = '" _
& Me![Desig] & "'"

Me.Parent![Child36].Form.RecordSource = sql1

Forms![List of Standards]![Child36].Form.Refresh

End Sub

[List of standards] is the parent form and [child36] one of the child form

This code results in an error when the form is first opened as the forms are
not intialised when opening the parent form. How do I avoid this. What is the
correct way of doing this.

 

[Clifford Bass]

Hi Sajit,

So, do I understand correctly that the Form_Current() code you posted
is associated with the [query1 subform] and not the main form? And that when
you select something in [query1 subform] that you want to change the record
source for the Child36 subform? If that is the case, then what you are doing
makes sense.

You are right about the issue being that the Child36 subform has not
yet been created. Then answer is to have Access create it before it creates
the [query1 subform]. The way to do it is do select [query1 subform] and cut
it out of the main form. Then paste it back in.

And you can simplify your code. Change the Record Source of Child36 to
just "SELECT Desig, Det FROM [List of standards]". Then change the contents
of the Form_Current subroutine to:

Private Sub Form_Current()

With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With

End Sub

Helpful hint: Name your queries, forms, subforms, and controls
something meaningful. It will go a long way for you both in the construction
of the system and later on when you look at the database and wonder quite
what query3 is or what Child36 is.

Hope this helps,

Clifford Bass

 

[Sajit]

Thank you very much.
--
Sajit
Abu Dhabi

Hi Sajit,

So, do I understand correctly that the Form_Current() code you posted
is associated with the [query1 subform] and not the main form? And that when
you select something in [query1 subform] that you want to change the record
source for the Child36 subform? If that is the case, then what you are doing
makes sense.

You are right about the issue being that the Child36 subform has not
yet been created. Then answer is to have Access create it before it creates
the [query1 subform]. The way to do it is do select [query1 subform] and cut
it out of the main form. Then paste it back in.

And you can simplify your code. Change the Record Source of Child36 to
just "SELECT Desig, Det FROM [List of standards]". Then change the contents
of the Form_Current subroutine to:

Private Sub Form_Current()

With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With

End Sub

Helpful hint: Name your queries, forms, subforms, and controls
something meaningful. It will go a long way for you both in the construction
of the system and later on when you look at the database and wonder quite
what query3 is or what Child36 is.

Hope this helps,

Clifford Bass

I wanted to copy the report which the documenter would have given me. On
trying to get this, I get the message,

'doc_tblobjects' already exists, however I don't see any table. This
probably is a message I am getting after I changed the workgroup file for
the data base.

Yes, [List of standards] is a table also.

There are 2 tables, Typ and [List of Standards] linked through a common
text field, Typ.

The parent form [List of Standards] has a uncontrolled combo box row
sourced to query, temp5

Under the parent form there are 2 child forms,

The [query1 subform ] child form displays all records for the selected Typ
in the parent form combo box. [query1 subform ] is Record sourced to
query, query3.
query3 is made from table [List of standards]

The child36 displays the Det field for each current record selected in
[query1 subform]. The link is the desig field. desig field is only in
table, [List of standards] and not table, Typ. child36 is Record sourced to
query, qry_det.
qry_det is made from table, [List of standards]

 

[Clifford Bass]

Hi Sajit,

You are welcome. Glad to help.

Clifford Bass

 

[Sajit]

Hello Clifford,

I just noticed a couple of other things related to this,

The good thing about using a filter is that every time the form is opened,
it opened with the selection (filter set in the last run) made at the last
run.
Why is the expression,

Replace([Desig], "'", "''") & "'")

required? What is it doing?

I have another error happening, this was happening even before. If I
attempt to delete a record from the [query1 subform] then it results in a
run time error 2074, operation not supported within transaction. How do I
prevent this happening?

 

[Clifford Bass]

Hi Sajit,

The unexpected positive side-effect of using a filter--that kind of
thing is always special!

About the Replace() part of:

.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")

It is there to deal with the possibility that there may be an
apostrophe (') within the text entered into the [Desig] field on the form.
If you set the filter without the Replace() function:

.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & [Desig] &
"'")

And [Desig] contained "ABC's" the filter would get set to:

Desig = 'ABC's'

This will give an error because there is the literal string ABC and
them something else (s') after the string. So in order to specify that the
string should include the second apostrophe up through the letter s, you need
to double up the apostrophe:

Desig = 'ABC''s'

This would be true if you were using quote symbols as the string
delimiter and you wanted to include one or more quote symbols inside the
string:

Desig = "He said: ""Go home!"" to her."

The Replace() function takes the first parameter and makes a new string
from it, only with all instances of the second parameter replaced with the
third parameter. So in the above code ABC's would get converted to ABC''s.

Now you might say, "There is no reason for someone to enter an
apostrophe into Desig--it will never contain anything with an apostrophe.".
The second part may be true, but is is pretty easy for someone's finger to
slip on the keyboard and end up typing an apostrophe. So it is always good
to deal with that possibility, regardless of the likelihood of its use or not.

Regarding the delete error. How is the record being deleted? If there
is code involved, what is the code?

Clifford Bass

 

[Sajit]

Hello Clifford,

Ok, that is good. thats an useful one to remember.

The deletion is not by code. But manually by selecting a row selector in the
[query1 subform] form and pressing delete key. If I end the exceution of the
code when the error happens, the form continues to work. Will the solution
for this be by suppressing the error?

--
Sajit
Abu Dhabi

Hi Sajit,

The unexpected positive side-effect of using a filter--that kind of
thing is always special!

About the Replace() part of:

.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")

It is there to deal with the possibility that there may be an
apostrophe (') within the text entered into the [Desig] field on the form.
If you set the filter without the Replace() function:

.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & [Desig] &
"'")

And [Desig] contained "ABC's" the filter would get set to:

Desig = 'ABC's'

This will give an error because there is the literal string ABC and
them something else (s') after the string. So in order to specify that the
string should include the second apostrophe up through the letter s, you need
to double up the apostrophe:

Desig = 'ABC''s'

This would be true if you were using quote symbols as the string
delimiter and you wanted to include one or more quote symbols inside the
string:

Desig = "He said: ""Go home!"" to her."

The Replace() function takes the first parameter and makes a new string
from it, only with all instances of the second parameter replaced with the
third parameter. So in the above code ABC's would get converted to ABC''s.

Now you might say, "There is no reason for someone to enter an
apostrophe into Desig--it will never contain anything with an apostrophe.".
The second part may be true, but is is pretty easy for someone's finger to
slip on the keyboard and end up typing an apostrophe. So it is always good
to deal with that possibility, regardless of the likelihood of its use or not.

Regarding the delete error. How is the record being deleted? If there
is code involved, what is the code?

Clifford Bass

Hello Clifford,

I just noticed a couple of other things related to this,

The good thing about using a filter is that every time the form is opened,
it opened with the selection (filter set in the last run) made at the last
run.
Why is the expression,

Replace([Desig], "'", "''") & "'")

required? What is it doing?

I have another error happening, this was happening even before. If I
attempt to delete a record from the [query1 subform] then it results in a
run time error 2074, operation not supported within transaction. How do I
prevent this happening?

 

[Clifford Bass]

Hi Sajit,

It would be better to figure out what is causing the error and why.
Then take care of the situation so an error does not get generated. I have a
suspicion that the error is happening in the Form_Current event procedure.
Modify it to include error handling:

Private Sub Form_Current()

On Error GoTo Handle_Error

With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With

Exit_Sub:
Exit Sub

Handle_Error:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form_Current"
Resume Exit_Sub

End Sub

Test deleting a record. Do you get a message box with "Form_Current"
in the title bar, giving the error information?

If you do, it is because when you delete a record, the Form_Current
gets invoked before you get prompted to confirm the delete. This is part way
through a transaction. And what you are doing in the Form_Current may not be
allowed while a transaction is in progress. If this is indeed the case, do
this:

Add this line at the top your your subform's module below any Option
.... statements, but before any subroutines/functions:

Private m_boolDeleteStarted As Boolean

Create Event Procedures for all three of On Open, On Delete and After
Del Confirm. Place the following line in Form_Open() so as to make sure it
starts out as False.

m_boolDeleteStarted = False

Place this line in Form_Delete() so as to indicate that a deletion has
started.

m_boolDeleteStarted = True

In your Form_Current add in checking to see if a record is being deleted:

If m_boolDeleteStarted Then
' Delete started, just toggle the flag to false
m_boolDeleteStarted = False
Else
' No delete started, do the normal stuff
With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With
End If

Finally, in the Form_AfterDelConfirm() add these lines:

If Status = acDeleteOK Then
Form_Current
End If

This final stuff is needed because you disabled the stuff in
Form_Current, so it did not do it yet. If the user cancels the confirmation,
then a new Form_Current is automatically performed and you do not need to do
anything. But when the user accepts the confirmation, no new Form_Current is
performed, so you have to do it manually.

Hope that helps,

Clifford Bass

Hello Clifford,

Ok, that is good. thats an useful one to remember.

The deletion is not by code. But manually by selecting a row selector in the
[query1 subform] form and pressing delete key. If I end the exceution of the
code when the error happens, the form continues to work. Will the solution
for this be by suppressing the error?

 

[Sajit]

Hello Clifford,

Yes, it is the code that sets the filter under the Form_current for [query1
subform] that is causing the error message. I should have mentioned that
earlier.

I did all the changes you have suggested.


A new error occurs on selecting a new row with the Desig as blank. The
error occurs from the form_current statement,

.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")

The error is invalid use of Null.

How is that it did not occur before the last changes (the ones to care of
the manual deletion of records) were done?
--
Sajit
Abu Dhabi

Hi Sajit,

It would be better to figure out what is causing the error and why.
Then take care of the situation so an error does not get generated. I have a
suspicion that the error is happening in the Form_Current event procedure.
Modify it to include error handling:

Private Sub Form_Current()

On Error GoTo Handle_Error

With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With

Exit_Sub:
Exit Sub

Handle_Error:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form_Current"
Resume Exit_Sub

End Sub

Test deleting a record. Do you get a message box with "Form_Current"
in the title bar, giving the error information?

If you do, it is because when you delete a record, the Form_Current
gets invoked before you get prompted to confirm the delete. This is part way
through a transaction. And what you are doing in the Form_Current may not be
allowed while a transaction is in progress. If this is indeed the case, do
this:

Add this line at the top your your subform's module below any Option
... statements, but before any subroutines/functions:

Private m_boolDeleteStarted As Boolean

Create Event Procedures for all three of On Open, On Delete and After
Del Confirm. Place the following line in Form_Open() so as to make sure it
starts out as False.

m_boolDeleteStarted = False

Place this line in Form_Delete() so as to indicate that a deletion has
started.

m_boolDeleteStarted = True

In your Form_Current add in checking to see if a record is being deleted:

If m_boolDeleteStarted Then
' Delete started, just toggle the flag to false
m_boolDeleteStarted = False
Else
' No delete started, do the normal stuff
With Parent.Child36.Form
.Filter = "Desig " & IIf(IsNull([Desig]), "is null", "= '" & _
Replace([Desig], "'", "''") & "'")
.FilterOn = True
End With
End If

Finally, in the Form_AfterDelConfirm() add these lines:

If Status = acDeleteOK Then
Form_Current
End If

This final stuff is needed because you disabled the stuff in
Form_Current, so it did not do it yet. If the user cancels the confirmation,
then a new Form_Current is automatically performed and you do not need to do
anything. But when the user accepts the confirmation, no new Form_Current is
performed, so you have to do it manually.

Hope that helps,

Clifford Bass

Hello Clifford,

Ok, that is good. thats an useful one to remember.

The deletion is not by code. But manually by selecting a row selector in the
[query1 subform] form and pressing delete key. If I end the exceution of the
code when the error happens, the form continues to work. Will the solution
for this be by suppressing the error?

 

[Clifford Bass]

Hi Sajit,

With the change we made the circumstances under which the filter is set
changed slightly. I am thinking that the problem is the second part of the
IIf() function. There are times when Access is smart enough not to evaluate
the stuff in the second or third part when it does not apply. Rather it
evaluates both. And other times, in queries I think, when it is smart
enough. I think this is the too-dumb case. And it is trying to evaluate the
Replace() when [Desig] is null, which Replace() does not like. So, do away
with the IIf() part and use If ... Then ... Else ... End If instead:

If IsNull([Desig]) Then
.Filter = "Desig is null"
Else
.Filter = "Desig = '" & Replace([Desig], "'", "''") & "'"
End If

Hopefully that will solve what I hope is the last of the issues!

Clifford Bass