Creating a module

[Niniel]

Hello,

I have never done this before, but I read that it is a good idea to put code
that is used in several places in a module.
So, I'd like to try to do that.
I'd appreciate help in deciding what needs to go into the module, as well as
coming up with a method of using it.

Thank you.


Here's the code I have; it'll be used in 4 - 5 instances. What will change
is the name of the text field [here: NoteText1] and the question id.

In my parent form:
___

Private Sub NoteText1_AfterUpdate()

'Copy text from text field to table and save record

DoCmd.RunSQL "Update tblActivityAnswers Set [Note] =
Forms!frmBrowseApplications.[NoteText1] WHERE [ActivityID] =
Forms!frmBrowseApplications.[ActivityID] And [QuestionID] = 12"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub


In my subform:
___

Private Sub Answer_AfterUpdate()

If Me.Answer = -1 And Me.QuestionID = 12 Then
Me.Parent.NoteText1.Visible = True
End If

If Me.Answer = 0 And Me.QuestionID = 12 Then
Me.Parent.NoteText1.Visible = False
End If

End Sub


Private Sub Form_current()

If DLookup("[Answer]", "tblActivityAnswers", "[ActivityID] = " &
Me.Parent.[ActivityID] & " AND [QuestionID] = 12") = True Then
Me.Parent.NoteText1.Visible = True
Me.Parent.NoteText1 = DLookup("[Note]", "tblActivityAnswers", "[ActivityID]
= " & Me.Parent.[ActivityID] & " AND [QuestionID] = 12")
Else: Me.Parent.NoteText1.Visible = False
End If

End Sub

 

[TonyT]

Hi Niniel,

replies in order;

Hello,

I have never done this before, but I read that it is a good idea to put code
that is used in several places in a module.
So, I'd like to try to do that.
I'd appreciate help in deciding what needs to go into the module, as well as
coming up with a method of using it.

Thank you.


Here's the code I have; it'll be used in 4 - 5 instances. What will change
is the name of the text field [here: NoteText1] and the question id.

In my parent form:
___

Private Sub NoteText1_AfterUpdate()

'Copy text from text field to table and save record

DoCmd.RunSQL "Update tblActivityAnswers Set [Note] =
Forms!frmBrowseApplications.[NoteText1] WHERE [ActivityID] =
Forms!frmBrowseApplications.[ActivityID] And [QuestionID] = 12"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

Because the above code is in the afterudpate event of a textbox referred to
in the save statement, I see no problems in leaving the code as it is, save
for maybe replacing Forms!frmBrowseApplication.[NoteText1] with
Me.[NoteText1].
The code created to produce you module would nullify the benefit of using
the module for just 4 occasions. (PS DoCmd.Execute ,DbFailOnError is
generally accepted to be better than DoCmd.RunSQL as you are trapping an
error and undoing the save & If Me.Dirty Then Me.Dirty = False is more
efficient than DoCmd.DoMenuItem......)

In my subform:
___

Private Sub Answer_AfterUpdate()

If Me.Answer = -1 And Me.QuestionID = 12 Then
Me.Parent.NoteText1.Visible = True
End If

If Me.Answer = 0 And Me.QuestionID = 12 Then
Me.Parent.NoteText1.Visible = False
End If

End Sub


Private Sub Form_current()

If DLookup("[Answer]", "tblActivityAnswers", "[ActivityID] = " &
Me.Parent.[ActivityID] & " AND [QuestionID] = 12") = True Then
Me.Parent.NoteText1.Visible = True
Me.Parent.NoteText1 = DLookup("[Note]", "tblActivityAnswers", "[ActivityID]
= " & Me.Parent.[ActivityID] & " AND [QuestionID] = 12")
Else: Me.Parent.NoteText1.Visible = False
End If

End Sub

Is this form bound to tblActivityAnswers? or a query based on it? If so, can
you not just include the fields you are looking up in hidden textboxes, and
reveal as required, rather than using DLookup? eg;
If Me.Answer = Me.HiddenRealAnswer Then
Me.Parent.NoteText1 = Me.HiddenText
Me.Parent.NoteText1.Visible = True
Else
Me.Parent.NoteText1.Visible = False
End If
The above assumes a NoteText for each answer, only shown if the yes/no is
guessed correctly, If I'm reading you right, then the above code could
replace both your last 2 sets of code.

hope this helps,

TonyT..

 

[Niniel]

Hello Toni,

Thank you for your reply.
Since you indicated that there would be no real benefit to using a module in
this case, I've decided to not pursue this avenue for now. Maybe I will
return to it at a later time if only try to make it work.

As for my use of DLookup, I'm doing that because my form is a continuous
subform that is indeed based on a query that pulls a subset of records from
tblActivityAnswers.
This form, and others like it, shows more than one record [2 - 15 depending
on the form], but there's only 1 question per group/subcategory that needs to
have optional text input [in a block of items/questions where all that apply
are to be checked; there's often an unspecific one called "other" where the
user is to enter information].
Since the form shows only one checkbox and one text box in design mode, I
had to identify the correct record by its position in the table, ie. parent
record ID and question ID, and dlookup seems to be the only way to do that.