Creating a module

N

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
 
T

TonyT

Hi Niniel,

replies in order;

Niniel said:
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..
 
N

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top