Subroutine question

  • Thread starter Thread starter Ed Dror
  • Start date Start date
E

Ed Dror

Hi there,

I'm using Access 2007 and my question is

Where I can find samples of how to call Subroutine or how you incorporate a
module with your application
I saw a lot of codes but not how to leverage it with your forms for example
What to do with class or function

I'm not looking of how to write a subroutine
I'm looking what to do with subroutine

P.S (ADO - sample will be helpful)

Thanks,
Ed Dror
 
I use them for two purposes

1. To avoid repeating the same code f you use the same lines of code
in several places You only have to change it in once place if you
need to change it.

2. To make the code easier to understand. Sometimes its better to
give the subroutine a descriptive name and then call it from your main
sub routine.

here's an excerpt from one of my databases. Each subroutine that I
call is somewhat long and this makes it easier read. Also you don't
have to use the word "Call" but if find that it makes it clear that
you are calling a subroutine.

Another note: if you place you cursor in the name of the subroutine
you are calling and press SHIFT+F2 it will take you directly to that
sub which is handy during development and debugging and then you can
just close the window to go back to where you were.


Me.txtStatus.Text = "Running....." & vbCrLf & "Demographics &
Volume"
Call SP1_CorpDemo(MyCorpNums, MyCorpDates)


Me.txtStatus.Text = "Running....." & vbCrLf & "Equipment"
Call SP1_CorpEquipment(MyCorpNums)


Me.txtStatus.Text = "Running....." & vbCrLf & "InvoiceMast"
Call SP1_CorpInvoiceMast(MyCorpNums, MyCorpDates)


Me.txtStatus.Text = "Running....." & vbCrLf & "InvoiceMastQA"
Call SP1_CorpInvoiceMastQA(MyCorpNums, MyCorpDates)

Here's one of the subroutines that i call (note I've alreaddy
connected in code earlier from the calling procedure)

Sub SP1_CorpDemo(MyCorpNums, MyCorpDates)

Dim Row As Integer

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset


Dim fld As ADODB.Field
Dim cmd As New ADODB.Command


Set cmd.ActiveConnection = cnn


cmd.CommandText = "[GetCorpDemographics&Volume&CB]"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Parameters.Refresh
cmd.Parameters("@OrderList").Value = MyCorpNums
cmd.Parameters("@MonthYearList").Value = MyCorpDates


rst1.Open cmd

Column = 1
Row = 1

For Each fld In rst1.Fields

xlApp.Workbooks(1).Worksheets(1).Cells(Row, Column).Value =
fld.Name
Column = Column + 1

Next fld

xlApp.Workbooks(1).Worksheets(1).Cells(2, 1).CopyFromRecordset rst1

Set cmd = Nothing

Set rst1 = Nothing
FieldNames = ""
End Sub


I mostly use modules if I want to use the same code in more than one
form and for API call declarations which can't be in a form ...they
have to be in a module.

I don't use Class modules and there aren't many books that give
examples of them for Access. Class modules can be used to create
your own objects with their own properties and methods. Probably the
best book is John Smileys's "Visual Basic Objects". It's written for
VB6 but most of it can be used in Access. I think *most* Access
programmers are "task oriented" and don't really have the time or
interest in creating their own objects and don't find it
necessary.......
 
Billy,

Thank you very mutch for your help

Ed Dror

I use them for two purposes

1. To avoid repeating the same code f you use the same lines of code
in several places You only have to change it in once place if you
need to change it.

2. To make the code easier to understand. Sometimes its better to
give the subroutine a descriptive name and then call it from your main
sub routine.

here's an excerpt from one of my databases. Each subroutine that I
call is somewhat long and this makes it easier read. Also you don't
have to use the word "Call" but if find that it makes it clear that
you are calling a subroutine.

Another note: if you place you cursor in the name of the subroutine
you are calling and press SHIFT+F2 it will take you directly to that
sub which is handy during development and debugging and then you can
just close the window to go back to where you were.


Me.txtStatus.Text = "Running....." & vbCrLf & "Demographics &
Volume"
Call SP1_CorpDemo(MyCorpNums, MyCorpDates)


Me.txtStatus.Text = "Running....." & vbCrLf & "Equipment"
Call SP1_CorpEquipment(MyCorpNums)


Me.txtStatus.Text = "Running....." & vbCrLf & "InvoiceMast"
Call SP1_CorpInvoiceMast(MyCorpNums, MyCorpDates)


Me.txtStatus.Text = "Running....." & vbCrLf & "InvoiceMastQA"
Call SP1_CorpInvoiceMastQA(MyCorpNums, MyCorpDates)

Here's one of the subroutines that i call (note I've alreaddy
connected in code earlier from the calling procedure)

Sub SP1_CorpDemo(MyCorpNums, MyCorpDates)

Dim Row As Integer

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset


Dim fld As ADODB.Field
Dim cmd As New ADODB.Command


Set cmd.ActiveConnection = cnn


cmd.CommandText = "[GetCorpDemographics&Volume&CB]"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Parameters.Refresh
cmd.Parameters("@OrderList").Value = MyCorpNums
cmd.Parameters("@MonthYearList").Value = MyCorpDates


rst1.Open cmd

Column = 1
Row = 1

For Each fld In rst1.Fields

xlApp.Workbooks(1).Worksheets(1).Cells(Row, Column).Value =
fld.Name
Column = Column + 1

Next fld

xlApp.Workbooks(1).Worksheets(1).Cells(2, 1).CopyFromRecordset rst1

Set cmd = Nothing

Set rst1 = Nothing
FieldNames = ""
End Sub


I mostly use modules if I want to use the same code in more than one
form and for API call declarations which can't be in a form ...they
have to be in a module.

I don't use Class modules and there aren't many books that give
examples of them for Access. Class modules can be used to create
your own objects with their own properties and methods. Probably the
best book is John Smileys's "Visual Basic Objects". It's written for
VB6 but most of it can be used in Access. I think *most* Access
programmers are "task oriented" and don't really have the time or
interest in creating their own objects and don't find it
necessary.......
Click to expand...
 
Back
Top