How can I improve this code?

[dworst]

I have this code behind a text box in my access 2003 db. The problem
is that I have it 10 times for 10 different text boxes. I just think
there should be a better way to write this once and just call it.

Can someone take a look and let me know.

Thanks,

************************************************** *****

Private Sub LBL_Renewal_Myfile1_Click()
On Error GoTo ErrHandle_Err

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim SubLink1 As String
Dim SubLink2 As String

Dim PolicyLink1 As String
Dim PolicyLink2 As String

Dim Submission_Combo As String
Dim Policy_Combo As String
'Putting together the 3 Strings
'SubmissionLink
SubLink1 = "http://cvgdev20/myFileDemo?tab=submission&submissionId="
SubLink2 = [Forms]![Frm_Submissions]![Submission_ID]

Submission_Combo = SubLink1 + SubLink2

'PolicyLink
PolicyLink1 = "http://cvgdev20/myFileDemo?tab=policy&policyNumber="
PolicyLink2 = Nz([Forms]![Frm_Submissions]![Text427], 0)

'Policy_Combo = PolicyLink1 + PolicyLink2 & Chr(34)
Policy_Combo = PolicyLink1 + PolicyLink2
If Len([Forms]![Frm_Submissions]![Text427]) 1 Then
FollowHyperlink Policy_Combo
Else
FollowHyperlink Submission_Combo

End If
ErrHandle_Exit:
Exit Sub

ErrHandle_Err:
MsgBox Error$
Resume ErrHandle_Exit

End Sub

[keri]

Maybe I have misunderstood this question however it looks as if you
should remove the code and put it in a seperate sub called (for
example) sub textboxcode. Then the code for your text boxes should
read;

Private Sub LBL_Renewal_Myfile1_Click()
call textboxcode
end sub.

You would need to have the code above to call it from each text box,
however the actual long code you have shown would only need to appear
once, in the sub textboxcode you call.

[dworst]

On Sep 26, 7:57 am, keri <[e-mail]:

Maybe I have misunderstood this question however it looks as if you
should remove the code and put it in a seperate sub called (for
example) sub textboxcode. Then the code for your text boxes should
read;

Private Sub LBL_Renewal_Myfile1_Click()
call textboxcode
end sub.

You would need to have the code above to call it from each text box,
however the actual long code you have shown would only need to appear
once, in the sub textboxcode you call.

**********************************

So if I understand what you are saying....if I have 10 text boxes, all
with different names, ex: textbox1, textbox2, etc. I should put them
all in one piece of code and then just call it from the click on the
text box?

If so, that is what I was thinking, but I don't know exactly how to
loop through the code, or use a case statement in the code. Could you
help me out with that? I just think instead of having 10 of these for
10 separate text boxes, it would be much more efficient to have one
call that encompasses all 10 text boxes.

[keri]

Sorry I was rushing and didn't read your code properly. I'm guessing
your code submits the value from the textbox to a form, ttherefore the
code needs to know which textbox has called the code - am I correct?

[dworst]

On Sep 26, 8:51 am, keri <[e-mail]:

Sorry I was rushing and didn't read your code properly. I'm guessing
your code submits the value from the textbox to a form, ttherefore the
code needs to know which textbox has called the code - am I correct?

That's correct. The text box goes to a link and submits the values of
policy number, submission id

[dworst]

On Sep 26, 8:56 am, [e-mail] wrote:

On Sep 26, 8:51 am, keri <[e-mail]:

Sorry I was rushing and didn't read your code properly. I'm guessing
your code submits the value from the textbox to a form, ttherefore the
code needs to know which textbox has called the code - am I correct?

That's correct. The text box goes to a link and submits the values of
policy number, submission id

************************
Goes thorugh this code and picks up a submission id number:

**************************************
'SubmissionLink
SubLink1 = "http://cvgdev20/myFileDemo?tab=submission&submissionId="
SubLink2 = [Forms]![Frm_Submissions]![Submission_ID]

'Puts the above submission id number into a url link inside of a text
box
************************************
Submission_Combo = SubLink1 + SubLink2

'If there is a policy number it takes the policy number and puts that
number into the link referenced in PolicyLink1 inside of a text box
where you can link to the url
******************************

'PolicyLink
PolicyLink1 = "http://cvgdev20/myFileDemo?tab=policy&policyNumber="
PolicyLink2 = Nz([Forms]![Frm_Submissions]![Text427], 0)
'Policy_Combo = PolicyLink1 + PolicyLink2 & Chr(34)
Policy_Combo = PolicyLink1 + PolicyLink2

************************

So basicly there are 10 text boxes that display a "url" link. It
really is just text but behind the scenes, when you click on the text
inside the text box it runs this code and opens IE to a web site
containing the policy/submission id info.

[keri]

So each textbox runs identical code with the exception of using the
contents of the textbox that was clicked on.

You need to pass to the code (example names textboxcode) the name of
the text box that was clicked on (that called the textboxcode sub).
I'm not certain how to do this, I think you would be able to store the
calling textbox name in a public variable for use in the code. I'd
look at access help for this.

As a guess - you'd need to declare your public variable then in the
code activated by clicking on the textbox as below;

Private Sub LBL_Renewal_Myfile1_Click()
'insert a line here to pass the name of the textbox to the public
variable
call textboxcode
end sub

then in your textboxcode sub refer to the variable instead of the
textbox name directly.

[dworst]

On Sep 26, 9:24 am, keri <[e-mail]:

So each textbox runs identical code with the exception of using the
contents of the textbox that was clicked on.

You need to pass to the code (example names textboxcode) the name of
the text box that was clicked on (that called the textboxcode sub).
I'm not certain how to do this, I think you would be able to store the
calling textbox name in a public variable for use in the code. I'd
look at access help for this.

As a guess - you'd need to declare your public variable then in the
code activated by clicking on the textbox as below;

Private Sub LBL_Renewal_Myfile1_Click()
'insert a line here to pass the name of the textbox to the public
variable
call textboxcode
end sub

then in your textboxcode sub refer to the variable instead of the
textbox name directly.

************************

I'll try that. Thanks,

[diane]

No, no, no, what you want is a function, not a sub, and you can pass
any values you want into to it when you call it.

Example:

Sub textbox_Click()

Call Do_This(PolicyLink1, SubLink1)

End Sub

Public Function Do_This(data1 as Long, data2 as String)

<< do something with the data >>

End Function

[Bob Quintal]

diane <di***********@verizon.netwrote in
news:11********************@57g2000hsv.googlegroup s.com:

No, no, no, what you want is a function, not a sub, and you can
pass any values you want into to it when you call it.

Example:

Sub textbox_Click()

Call Do_This(PolicyLink1, SubLink1)

End Sub

Public Function Do_This(data1 as Long, data2 as String)

<< do something with the data >>

End Function

You can pass parameters to a sub procedure, in exactly the same way
as to a function. You do not need to use a function for this, the
difference between the sub and the fiunction is that the function
returns a value.

If the sub or function is in the same module as the calling code,
then it can and should be declared private.

Example:

Sub textbox_Click()
Do_This PolicyLink1, SubLink1
End Sub

Private sub Do_This(data1 as Long, data2 as String)
<< do something with the data >>
End Function


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

keri <ke********@hotmail.comwrote in
news:11**********************@w3g2000hsg.googlegro ups.com:

So each textbox runs identical code with the exception of using
the contents of the textbox that was clicked on.

You need to pass to the code (example names textboxcode) the name
of the text box that was clicked on (that called the textboxcode
sub). I'm not certain how to do this, I think you would be able to
store the calling textbox name in a public variable for use in the
code. I'd look at access help for this.

As a guess - you'd need to declare your public variable then in
the code activated by clicking on the textbox as below;

Private Sub LBL_Renewal_Myfile1_Click()
'insert a line here to pass the name of the textbox to the public
variable
call textboxcode
end sub

then in your textboxcode sub refer to the variable instead of the
textbox name directly.

You do not need a public variable at all, just declare the parameter
as part of the declaration of the sub procedure.

Private Sub LBL_Renewal_Myfile1_Click()
call textboxcode LBL_Renewal_Myfile1
end sub

Private sub textboxcode(boxname as string, optional another variable
as string)
' do your thing
end sub

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com