Ways to put code into a subroutine?

L

LAS

I have a form that contains the following code 36 times (!!!). It varies
only in the digits. For every PB<x>_, there are six PB<y>s. I wish I could
have put it into a subroutine, probably with parameters "asPrimaryDigit,
asSecondaryDigit". I can see how to do it where the digits are in strings
(e.g., " Where code_group = .....). But I don't see how to handle the
controls (txtPB<x>_<y>), except for a 6 level case statement within a 6
level case statement. Is that the best I can do?

TIA
LAS

If Trim(isPB1_1Orig) <> txtPB1_1 Then
If isPB1_1Orig = "" Then
gs_sql = "Insert into tblCodes(Code_Group,Code,Description)" _
& "VALUES ('ProbBehave1', 'PB1-1', '" & txtPB1_1 & "')"
Else
gs_sql = "Update tblCodes set Description = '" & txtPB1_1 & "'" & _
" Where Code_Group = 'ProbBehave1' and Code = 'PB1-1'"

End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
If trim(isPB1_1Orig) <> "" Then
If ibModify Then
gs_sql = "Update tblStudentTracking Set ProbBehaveCat1_Desc = '"
& txtPB1_1 _
& "' Where ProbBehaveCat1_Desc = '" & isPB1_1Orig & "'"
Else
gs_sql = "Delete from tblStudentTracking " _
& " Where ProbBehaveCat1_Desc = '" & isPB1_1Orig & "'"
End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
End If
End If
 
J

John W. Vinson

I have a form that contains the following code 36 times (!!!). It varies
only in the digits. For every PB<x>_, there are six PB<y>s. I wish I could
have put it into a subroutine, probably with parameters "asPrimaryDigit,
asSecondaryDigit". I can see how to do it where the digits are in strings
(e.g., " Where code_group = .....). But I don't see how to handle the
controls (txtPB<x>_<y>), except for a 6 level case statement within a 6
level case statement. Is that the best I can do?

TIA
LAS

If Trim(isPB1_1Orig) <> txtPB1_1 Then
If isPB1_1Orig = "" Then
gs_sql = "Insert into tblCodes(Code_Group,Code,Description)" _
& "VALUES ('ProbBehave1', 'PB1-1', '" & txtPB1_1 & "')"
Else
gs_sql = "Update tblCodes set Description = '" & txtPB1_1 & "'" & _
" Where Code_Group = 'ProbBehave1' and Code = 'PB1-1'"

End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
If trim(isPB1_1Orig) <> "" Then
If ibModify Then
gs_sql = "Update tblStudentTracking Set ProbBehaveCat1_Desc = '"
& txtPB1_1 _
& "' Where ProbBehaveCat1_Desc = '" & isPB1_1Orig & "'"
Else
gs_sql = "Delete from tblStudentTracking " _
& " Where ProbBehaveCat1_Desc = '" & isPB1_1Orig & "'"
End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
End If
End If
Click to expand...

Well, you can certainly do better than either 36 code blocks or a CASE; you
can build up a string by concatenation. I'd be inclined to do this table
driven if possible, but if not, code like this might be better:

Dim iFirst as Integer, iLast As Integer
Dim str_isPB As String, strField As String, strTextbox as String
For iFirst = 1 to 6
For iLast = 1 to 6
str_isPB = "isPB" & iFirst & "_" & iLast & "Orig"
strField = "PB" & iFirst & "-" & iLast
strTextbox = "txtPB" & iFirst & "_" & iLast
If Me.Controls(str_isPB) <> Me.Controls(strTextbox) Then
gs_sql = "Update tblCodes set Description = '" & strTextbox "'" & _
" Where Code_Group = 'ProbBehave1' and Code = '" & strField & '"

and so on.

However, if you have 36 fields in your table with names like this, you really
need to consider normalizing more completely!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
D

Douglas J. Steele

Something like:

Dim intX As Integer
Dim intY As Integer

For intX = 1 To 6
For intY = 1 To 6

If Trim(Me.Controls("isPB" & intX & "_" & intY & "Orig") <>
Me.Controls("txtPB" & intX & "_" & intY) Then
If Len(Me.Controls("isPB" & intX & "_" & intY & "Orig")) = 0 Then
gs_sql = "Insert into tblCodes(Code_Group,Code,Description)" & _
"VALUES ('ProbBehave" & intX & "'", 'PB" & intX & "-" &
intY & "'" , "'" & _
Me.Controls("txtPB" & intX & "_" & intY & "')"
Else
gs_sql = "Update tblCodes set Description = '" &
Me.Controls("txtPB" & intX & "_" & intY & "'" & _
" Where Code_Group = 'ProbBehave" & intX & "' and Code =
'PB" & intX & "-" & intY & "'"
End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
If Len(trim(Me.Controls("isPB" & intX & "_" & intY & "Orig")) <> 0
Then
If ibModify Then
gs_sql = "Update tblStudentTracking Set ProbBehaveCat1_Desc = '"
_ &
Me.Controls("txtPB" & intX & "_" & intY) & "'" & _
" Where ProbBehaveCat1_Desc = '" & Me.Controls("isPB" & intX &
"_" & intY & "Orig") & "'"
Else
gs_sql = "Delete from tblStudentTracking " _
& " Where ProbBehaveCat1_Desc = '" & Me.Controls("isPB" &
intX & "_" & intY & "Orig") & "'"
End If
Call CurrentDb.Execute(gs_sql, dbFailOnError)
End If
End If
Next intY
Next intX

(apologies for the word-wrap that I suspect will occur: post back if you can
figure it out...)
 
D

David W. Fenton

I have a form that contains the following code 36 times (!!!).
Click to expand...

I haven't really looked at the code, but I'm concerned that you are
using SQL to edit data instead of using forms to do so. Again, you
seem to be doing everything in the hardest possible way, instead of
using the default Access approach to editing data and creating
records.
 
L

LAS

I'm adding and deleting rows in a single Codes table. These various codes
happen to be limited to 6 rows, plus an "other."
I don't think it's the hardest way possible, given the needs of the
application. I do know about normalization.
 
L

LAS

And updating those rows.

LAS said:
I'm adding and deleting rows in a single Codes table. These various codes
happen to be limited to 6 rows, plus an "other."
I don't think it's the hardest way possible, given the needs of the
application. I do know about normalization.
Click to expand...
 
D

David W. Fenton

I'm adding and deleting rows in a single Codes table. These
various codes happen to be limited to 6 rows, plus an "other."
I don't think it's the hardest way possible, given the needs of
the application. I do know about normalization.
Click to expand...

I didn't look to see what was being done specifically, but inserting
and updating records in SQL is something that should be done
relatively rarely in an Access application, in my opinion. That's
what forms are for, and a series of such SQL commands executing
based on data in a form is a red flag to me that things are just not
being done properly, or the data are not structured properly.

This is, of course, my opinion, but it is based on observation of
the convoluted code you have posted when you have problems.
 
L

LAS

Thanks so much!!! I haven't yet tried to implement this, but I see how you
solve the problem of the control name. Very happy to have a probable
solution!
 

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

Similar Threads

Leban's scrolling code 1
Call Subroutine in Excel 3
Slow Code/Query Execution 9
Docmd.Setwarning False Not Working 7
Calling a subroutine - how to? 4
Terminate main procedure from a subroutine? 4
Code always fails 6
Error on Insert Into 3

Top