calling a function from a sub

  • Thread starter Thread starter Bob Wickham
  • Start date Start date
B

Bob Wickham

Hi,
I have a Private sub activated by the OnClick event of a forms command
button.
The code within this sub adds records to a table.

I have borrowed some Function code from Allen Browne MVP that deletes
all records from a table and resets the AutoNumber field to "1".

They work perfectly as separate entities.

I now wish to combine the 2 by calling the Function that deletes the
records at the beginning of the sub that will add the records.

Can someone please explain the correct way to, firstly,
declare and then call the function from the sub.

Thankyou,
Bob
 
Bob said:
Hi,
I have a Private sub activated by the OnClick event of a forms command
button.
The code within this sub adds records to a table.

I have borrowed some Function code from Allen Browne MVP that deletes
all records from a table and resets the AutoNumber field to "1".

They work perfectly as separate entities.

I now wish to combine the 2 by calling the Function that deletes the
records at the beginning of the sub that will add the records.

Can someone please explain the correct way to, firstly,
declare and then call the function from the sub.

Thankyou,
Bob
Click to expand...

Just use the name of the function and pass any required arguments.

Sub ExampleSub

Function arg1, arg2

some other code

End Sub
 
Rick said:
Just use the name of the function and pass any required arguments.

Sub ExampleSub

Function arg1, arg2

some other code

End Sub
Click to expand...
I tried that already which is the way I expected it to work.

The function is
Public Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function

I wish to call it from a sub.

which is

Private Sub Command2_Click()

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String
Set dbs = CurrentDb


Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")
Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < Now()" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Do Until rstMissed.EOF
strSql = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
dbs.Execute strSql, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing
End Sub

Do they both have to be in a Module or a Class Module or should it work
if they are both in the Form_Form1 class object.

Bob
 
Bob said:
I tried that already which is the way I expected it to work.
[snip code]
Do they both have to be in a Module or a Class Module or should it
work if they are both in the Form_Form1 class object.
Click to expand...


Since the function is declared "public" then it could be in a standard module or
it could be in the same form module as the sub.

Once properly declared, calling a custom function is no different than calling
Date() or any other built in function.
 
Rick said:
Since the function is declared "public" then it could be in a standard module or
it could be in the same form module as the sub.

Once properly declared, calling a custom function is no different than calling
Date() or any other built in function.
Click to expand...

Thanks Rick,

You say "Once properly declared".

How is that done?

In C++, for instance, I would declare it at the beginning of the code by
stating the function name preceded by its return type.
eg. void myFunction();

and then where I wish to call the function I would type
void myFunction()

I'm trying something similar in vba but its not working.

Any clues?

Bob
 
Bob said:
Thanks Rick,

You say "Once properly declared".

How is that done?
Click to expand...

I just meant that it was properly constructed and scoped. If you can call the
function separately then you are already there.

Please explain "not working". Do you get an error?
 
Rick said:
I just meant that it was properly constructed and scoped. If you can call the
function separately then you are already there.

Please explain "not working". Do you get an error?
Click to expand...

Hi Rick

The name of my function is
Public Function DeleteAllAndResetAutoNum(strTable As String) As Boolean

If I call it from my sub like this

Private Sub Command2_Click()

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

DeleteAllAndResetAutoNum

Set dbs = CurrentDb


Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")

some more code
some more code

I get a Compile Error - Argument not optional

If I call it like this

Private Sub Command2_Click()

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

Call DeleteAllAndResetAutoNum(strTable As String)

Set dbs = CurrentDb


Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")

some more code
some more code

I get a Compile Error - Syntax Error

Do I need to announce its existence along with the other Dim statements
(in other words "declare")

Some sample code of how it SHOULD be done would be the most helpful to me.

Thanks,
Bob
 
Bob Wickham wrote in message said:
Hi Rick

The name of my function is
Public Function DeleteAllAndResetAutoNum(strTable As String) As Boolean

If I call it from my sub like this

Private Sub Command2_Click()

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

DeleteAllAndResetAutoNum

Set dbs = CurrentDb


Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")

some more code
some more code

I get a Compile Error - Argument not optional

If I call it like this

Private Sub Command2_Click()

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

Call DeleteAllAndResetAutoNum(strTable As String)

Set dbs = CurrentDb


Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")

some more code
some more code

I get a Compile Error - Syntax Error

Do I need to announce its existence along with the other Dim statements (in
other words "declare")

Some sample code of how it SHOULD be done would be the most helpful to me.

Thanks,
Bob
Click to expand...

Hi,

you are calling a function which returns a boolean. The function
expects one parameter. Try calling like this

Call DeleteAllAndResetAutoNum("NameOfYourTable")

to not "use or return" the return value or

dim fMyBoolean as boolean
fMyBoolean = DeleteAllAndResetAutoNum("NameOfYourTable")

then you can for instance test the boolean before allowing the rest
of the code to run.
 
RoyVidar said:
Hi,

you are calling a function which returns a boolean. The function
expects one parameter. Try calling like this

Call DeleteAllAndResetAutoNum("NameOfYourTable")

to not "use or return" the return value or

dim fMyBoolean as boolean
fMyBoolean = DeleteAllAndResetAutoNum("NameOfYourTable")

then you can for instance test the boolean before allowing the rest
of the code to run.
Click to expand...
Thanks Roy and Rick,

I just figured it out.
Its amazing how explaining it to someone else can make one see what is
really, really obvious.

Thanks again,
Bob
 
Back
Top