Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

OnTime Procedure - Passing A Variable

Here's what I want to do. I'm trying to pass a variable to the Procedure. Is it even possible?

Sub StartSchedule()
Application.OnTime earliesttime:=Now + TimeValue("00:00:2"), procedure:="TimeToRunAGroup("GroupA")"

Application.OnTime earliesttime:=Now + TimeValue("00:00:5"), procedure:="TimeToRunAGroup("GroupB")"

End Sub

Public Sub TimeToRunAGroup(GroupName as String)
Debug.Print GroupName
end Sub

Results should be:


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Ontime Procedure
Having problems getting this function to work and any help would be appreciated.

Sub My()
Application.OnTime TimeValue("13:51:00"), procedure:="fill"
End Sub

Sub fill()
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
End Sub

This is just a simple macro, but i can't get the macro to work...the time will go past the designated TimeValue and nothing happens.

Any suggestions?

Have tried just about everything!

Passing A Variable BACK To A Procedure.

I'm trying my hand at coding in VB for a MS-Access DB. Its not too bad, I'm getting there, but now I'm trying the clean up operation...

I am calling a procedure in a module.
The procedure runs fine, but I would like it to return a value that can be worked on in the original code procedure (or even passed to another)...

I'm used to working with C, so was tyring the RETURN command, but this isn't happy unless its with the GOsub command - I don't think this is what I'm looking for.

Any help, many thanks--

Mr B

Stored Procedure - Choosing Column Name By Passing A Variable -Urgent !!
Hi all,

In stored procedure's Insert statement, Can I choose the column name by passing
in a variable?
Eg " if the field names in the table projmast are -
proj_1,proj_2,proj_3,proj_4 .......
I want to choose to insert field "proj_2" only, if say option 2 is chosen

In visual basic
wfield = "proj_"& windex (where windex =2)
I can give insert into projmast(" & wfield & ") values('" &
txtdesc.text & ") for inserting only into proj_2 field
How do I get the same using a stored procedure ?

Thanks and Regards

Global Variable Doesn't Change Because Of Application.OnTime?
i have a sub and a global boolean variable CallNext. if CallNext is true then the sub checkstatus is going to call itself again with Application.OnTime in a.e. 2 secs.
i have a form that calls the sub checkstatus in it's UserForm_Initialize and set CallNext into true. If the Button Cancel is clicked then CallNext is set into false.

But somehow the sub callnext still runs and calls itself. I found out later that the variable CallNext still has the value true, although i set it into false before.

Here is some of the code :
Form :

Private Sub CommandButton1_Click()
CallNext = False
Unload Me
End Sub

Private Sub UserForm_Initialize()
CallNext = True
Application.OnTime Now + TimeValue("00:00:02"), "ClientMonitor.checkstatus"
End Sub

Modul ClientMonitor :

Public CallNext As Boolean


Public Sub checkstatus()


If CallNext Then
Application.OnTime Now + TimeValue("00:00:02"), "ClientMonitor.checkstatus"
End If

End Sub

I set CallNext into true only in UserForm_Initialize and nowhere else...

any idea?


Passing A Value To A Sub Procedure
Can anyone help me achieve the following goal?

I have form where users enter data (call it frmData). When they are done they click a "save" button to write the data to the database. I would like to add a verification feature so when the user clicks "save" a new forms opens (call it frmVerify) asking for a user name and password and then passes this data back to the original form (frmData).

I have all the required forms created and working, my problem is knowing how to:
1. Pause the execution of frmData "save_click()" event while it waits for frmVerify to be completed

2. Pass the user name from frmVerify back to frmData and resume the save_click() event

I know this might seem simple to many, but I'm kind of new to programming and can't seem to figure this one out.

Thanks for any help!

Passing A Procedure
I thought I saw something once that would let you pass a procedure or procedure name to another function from within VB. maybe I was dreaming because I've never seen it since. Does anyone know if it's possible?

I'm not talking about sending the name of the procedure as a string as in:

sub SubName()
on error goto errhandler



logError Error, Err,"SubName"

End Sub

Passing Filename To Other Procedure
Hello All,

Having a little problem with the following code. The user selects a file to open. The filename is stored in TrackManXLS and then passed to the ImportTMdata procedure. The problem is I don't need the entire file path just the file name. TrackManXLS might be c:MyDocuments"DataFile.xls" All I need to pass is "DataFile.xls" Is there a way to delete everything else except the file name?

This is where the problem occurs Windows(TrackManXLS).Activate

Also, how do I post code on the forum so it looks nice and neat.



Public TrackManXLS As Variant

Public Sub GetAndSetUpTrackManData()

Dim Filter As String, Title As String
Dim FilterIndex As Integer

Filter = "Trackman Excel Files (*.xls),*.xls,All Files (*.*),*.*"

FilterIndex = 1

Title = "Select the file to Open"

With Application

TrackManXLS = .GetOpenFilename(Filter, FilterIndex, Title)

If (TrackManXLS <> False) Then
Workbooks.Open Filename:=TrackManXLS, ReadOnly:=False
End If
End With


Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlToLeft

Call ImportTMdata

End Sub

Public Sub ImportTMdata()

Dim Rounds As Integer
Dim Balls As Integer
Dim I As Integer
Dim J As Integer
Dim txtRounds As String
Dim txtBalls As String
Dim R As Integer
Dim C As Integer
Dim A As Integer
C = 3
R = 1
A = 20
Application.Cursor = Wait

Application.ScreenUpdating = False

For J = 1 To 11 'frmImportDataTrackman!txtBalls.Text

'For I = R To (frmImportDataTrackman!txtRounds.Text * frmImportDataTrackman!txtBalls.Text) Step frmImportDataTrackman!txtBalls.Text

For I = R To 110 Step 11


With Worksheets(1)

.Range(.Cells(I, 1), .Cells(I, 20)).Select


End With


With Worksheets("Data")

.Range(.Cells(A, C), .Cells(A, C)).Select

Selection.PasteSpecial Paste:=xlValues, Transpose:=True

C = C + 1
End With

Next I

R = R + 1
A = A + 39
I = 1
C = 3

Next J

Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub

Passing A FlexGrid To A Procedure
I have a designated print module that utlizes a form to paint a flexgrid for printing. The print form allows the user to see the flex grid, edit the font, color, etc., of each individual cell, then using a paint procedure (called by a command button) paints it to a window so you can view it before you print it.........I can get it to work if I load a generic flexgrid into the print module but can not get the module to load flexgrids from other forms in my program.

How to I allow the user to call any flexgrid, from any form within the program so they may print it?

I know that I have to have a command button that calls the print module on the form that I am trying to print the flexgrid from but....

How do I set up a varialbe that sets the form and flexgrid so it is passed to the print module?

Passing Values To A Procedure
I get a syntax error when I run the below.
Private Sub cmdTest_Click()
Test("Good", "Morning")
End Sub

Public Sub Test(x As String, y As String)
MsgBox x
MsgBox y
End Sub
What am I doing wrong?


Passing Array To Procedure ?
Like The Title, do anyone has any Idea How to solve this problem ?

Passing Objects On A Sub-procedure
hi guys, I am making this program where I need to pass the name of the control( or the control) to a sub-procedure, the sub procedure is made inside the another form and I am using it to set the visibility etc. on the second form ( i.e. I pressed this command1(0), on the click event i will set first the visible and locked control on the second form before showing the second form)

I am using an active x control arrayed command button, but I'm having a problem doing this. This is the code and the error says

vb Code:
Public Sub ControlSet(objCtrl As Object, strButton As String, intIndex As Integer)objControl = objCtrl  'object variable or  with blocked variable not set (error says)intControlIndex = intIndex

and this is the command button where the object,the string, and the index value of the arrayed command button

vb Code:
Private Sub cmdFields_Click(Index As Integer)frmAddItems.ControlSet Me.cmdFields, "cmdFields", IndexfrmAddItems.Show vbModalEnd Sub

I have seen a statement that passes an object/control on a sub procedure , but I am not sure how to do it in a arrayed active x ( or maybe I am just confused)

can someone give me a hand on this?..


Passing Udt To Class Procedure
Hi all,
From what I understand, it should be possible to pass a publically declared User Defined Type as a parameter of a class procedure. I have defiend a UDT in a code module with the Public keyword, and try to use it as a parameter in one of my class procedures but I get the following error message:


Only public user defined types defined in public object modules can be used as parameters or return types for public procedures or class modules or as fields of public user defined types.

Passing Controls To A Procedure
How do I pass a Visual basic control into a procedure by reference. I wanted to make a procedure that would add items to the list of the passed control(combo box or list box) from a recordset.

Edited by - prajulm on 8/28/2003 6:01:36 AM

Passing An Array To A DLL Procedure

I am creating a DLL object in which I am creating a function which does some manipulation on the array passed to it. I am calling the function from another VB EXE project. The type of the array in the calling file is Variant(). When the function is called, it is giving an error 'Type Mismatch'. How do pass an array to the function? Please help me.

Thanks in advance

Passing Arrays Into A Procedure
Suppose i have an array of values and i want to take the average of them all.

I have created a procedure

public Sub CalculateMean()

blah blah
for I = 1 to 5
temp = temp + g_Array1(I)
next I

lngMean = temp / 5

This works fine for g_Array1

How can i pass different arrays to this procedure so that i don't need to have seperate procedures to calculate the mean for
each different array. Hope ya understand.

Passing An Array To A Procedure
Newbie Question: How do I pass an array into a Procedure without using a global array? If I were to do something like this in C++, what would be its equivalent procedure in Visual Basic?


C++ Code:

void FunctionTest(int [10], int &);

int main()

void FunctionTest(int A[10], int &B)
B = A[5];

Passing An Array Of Objects To A Procedure
It possible to pass to a procedure or a function a array of objects

Private SUB Myprocedure (MyCombo as combobox)
end sub

It worked if when I call the function I pass, for example

CALL Myprocedure(Cmb.Index(0))

but i get a error if I try

CALL Myprocedure(Cmb.Index())

it this possible? I mean, I can pass a array of objects to a procedure?

Stored Procedure - Passing TableName

i have a stored procedure and im trying to pass the tablename as a variable to it.

Here is what i got so far.

This is the Store Procedure:
CREATE PROCEDURE [SunAccounts] @txtFrom int, @txtTo int, @tableName varchar(100)


EXEC ('SELECT * From ' + @tableName + ' WHERE PERIOD BETWEEN ' + @txtFrom + ' AND ' + @txtTo)

Here is how i call the stored procedure in my vb code

With objCommand
.CommandType = adCmdStoredProc
.CommandText = "SunAccounts"
.ActiveConnection = g_connAppsLog
.CommandTimeout = 0
Set Parameter = .CreateParameter("@txtFrom", adInteger, adParamInput, , txtFrom.Text)
.Parameters.Append Parameter
Set Parameter = .CreateParameter("@txtTo", adInteger, adParamInput, , txtTo.Text)
.Parameters.Append Parameter
Set Parameter = .CreateParameter("@tableName", adVarChar, adParamInput, 100, txtTableName.Text)
.Parameters.Append Parameter
End With

Set rsGetData = objCommand.Execute '<----Error occours On this line.see picture below
Im pretty new to stored procedures so it is probably something simple.
I also understand that by using the exec keyword to execute the SQL Statement, it losses some of the benifits you gain by using stored procedures.Could anyone elaborate further on this.


Passing A Parameter To A Stored Procedure
What's going on here?

I'm passing a couple dates to a stored procedure but I'm getting the following error:

"Syntax error converting datetime from character string"

Here are code snippets...
Dim statements...

Public mstrFromDate As String
Public mstrToDate As String
Execution of stored proc (at this point a msgbox verifies that mstrFromDate contains '7/27/2003' & mstrToDate contains '8/7/2003'...

If m_cnnXact.State = adStateOpen Then
gstrSel = "EXEC rgarner.spFixErrors @parm1 = 'GETSUB', @ToDate = mstrToDate, @FromDate = mstrFromDate"
Set m_rsXact = New Recordset
m_rsXact.CursorLocation = adUseClient
m_rsXact.Open gstrSel, m_cnnXact, adOpenStatic, adLockBatchOptimistic
Call cmdFirst_Click
End If

parameters defined in stored proc...

ALTER PROC spFixErrors
(@parm1 varchar(255),
@ToDate char(10)=NULL,
@FromDate char(10)=NULL OUTPUT)
stored proc code...

If (@parm1 = 'GETSUB')
FROM TransactionError
WHERE error_summary in (1, 3, 5, 7)
CONVERT(datetime, @FromDate) AND
CONVERT(datetime, @ToDate)

If I replace the 2 date variables with teh same hard coded dates, everything works fine.

Any help would be appreciated.


How To Access A Control That Is Passing To A Procedure?

i have a calss name : ClsMntLocation, and within the class i have a procedure :
public sub InitializeFlexGrid ( frm as form)

Within my form(frmLocation) there is a flexgrid control : fxgrid

how should i code so that i can use my class procedure to intialize flex grid fields by the class itself but not in form.


Passing A Control Array To A Procedure...
This one should be easy, but I keep receiving errors when I try it.

I have a control array of labels that I want to turn off and on depending upon which buttons are selected. Here's a section of the function I'm trying to pass them to:

Private Sub DisplayLabels(byref lblName() as Label, byval x as integer)
Dim z as Integer
For z = 0 to x
lblName(x).Visible = True
Next z
End Sub

Here is the procedure call:

Call DisplayLabels(Me.lblCompleteThing(), 5)

Everytime I call this I receive either a type mismatch or a "array or user-defined type expected."

Is it possible to pass control arrays like regular arrays or am I committing a flagrant assumption?



Passing A Decimal To A Stored Procedure
Does anyone know how I can pass a value through ADO to a stored procedure, the field is decimal. The precision is 6 and the scale is 3.

Passing Values To A Stored Procedure
I need to pass a value to a variable (string) in a stored procedure. Depending on what user choosed it could be 1 value (for example "2") or there could be few values ("1, 2 and 3"). Does anybody know what the syntax is?

Passing Values To Sql Stored Procedure
Hi there,

Does anyone know if it is possible to pass a date to an MS SQL Stored Procedure from visual basic e.g. one of the lines of the stored proc is "Date between '20010730' and '20010804' ". These 2 dates have to be changed constantly (weekly) and I would like to have a vb program where I can enter the start and finish dates, rather than change the dates in the stored proc every week. Any help would be greatly appreciated. Thanks.

Passing Parameters To A Stored Procedure
Hi Team

How do I take the value of a text box and send as the parameters for an Access pass-trough query?

The stored Procedure is expecting a Char(7) that represents a month and year.

The following works when the Access query is hard coded

exec ReportDataMonth "01/2001"

But I need to get the parameter from a text box on a form

Any Ideas?

Passing Parameters To Stored Procedure
I'm currently writing an application to search through a SQL database. The user should be able to enter a date in to a text box, find rows the fall after a date criteria, and then the program will load the results in to a data grid.

I can get this to work if I hard code the date in to a stored procedure, but if I try to pass parameters to a stored procedure it does not work. I am very new to this, and the documents I have found up to this point have not helped me.

Here is my error that I receive when I click on the "load" button to load the data in to the data grid....

"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in

Additional information: System error."

Here is some of the relevant code....I think.....

PHP Code:

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

        Me.SqlCommand1.CommandType = CommandType.StoredProcedure
        Me.SqlCommand1.Parameters.Add("@date", SqlDbType.VarChar, 20)
        Me.SqlCommand1.Parameters("@date").Value = "1/15/2004 8:46:52 AM"
        Me.SqlCommand1.Connection = Me.SqlConnection2

    End Sub 

PHP Code:

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.DataSet11 = New sqltest2.DataSet1
        Me.btnLoad = New System.Windows.Forms.Button
        Me.btnExit = New System.Windows.Forms.Button
        Me.txtDate = New System.Windows.Forms.TextBox
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection2 = New System.Data.SqlClient.SqlConnection
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.DataGrid1.DataMember = "up_ARCOUNT"
        Me.DataGrid1.DataSource = Me.DataSet11
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 32)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(336, 88)
        Me.DataGrid1.TabIndex = 0
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        Me.btnLoad.Location = New System.Drawing.Point(352, 64)
        Me.btnLoad.Name = "btnLoad"
        Me.btnLoad.TabIndex = 1
        Me.btnLoad.Text = "Load"
        Me.btnExit.Location = New System.Drawing.Point(352, 96)
        Me.btnExit.Name = "btnExit"
        Me.btnExit.TabIndex = 2
        Me.btnExit.Text = "Exit"
        Me.txtDate.Location = New System.Drawing.Point(352, 32)
        Me.txtDate.Name = "txtDate"
        Me.txtDate.TabIndex = 3
        Me.txtDate.Text = ""
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "up_ARCOUNT", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Site Code", "Site Code"), New System.Data.Common.DataColumnMapping("Ticket #", "Ticket #"), New System.Data.Common.DataColumnMapping("Submit Date/Time", "Submit Date/Time")})})
        Me.SqlInsertCommand1.CommandText = "[up_ARCOUNT]"
        Me.SqlInsertCommand1.CommandType = System.Data.CommandType.StoredProcedure
        Me.SqlInsertCommand1.Connection = Me.SqlConnection2
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@date", System.Data.SqlDbType.VarChar, 20, "Submit Date/Time"))
        Me.SqlConnection2.ConnectionString = "workstation id=""IND-SSC-OWS040A"";packet size=4096;integrated security=SSPI;data s" & _
        "ource=""IND-SSC-XWS001A"";persist security info=False;initial catalog=Outage"
        Me.SqlSelectCommand1.CommandText = "[up_ARCOUNT]"
        Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
        Me.SqlSelectCommand1.Connection = Me.SqlConnection2
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@date", System.Data.SqlDbType.VarChar, 20))
        Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
        Me.SqlCommand1.Connection = Me.SqlConnection2
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(512, 206)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

#End Region

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click

    End Sub 

Any help I can get on this would be appreciated, or a point in the right direction.


Passing A Parameter To A Stored Procedure.
I have a stored procedure on a server that I am trying to execute in VB6.
I want to use the following command:
I want to be able to pass a parameter @UserName to the procedure using the EXEC command.
How do I code this?
I would appreciate any help I can get.

Passing Array To Stored Procedure
hi all,
how can i pass an array to a stored procedure and a recordset

Passing Null Value To A Stored Procedure
In my code I call a stored procedure and add a parameter:

set parmDriveLetter = cmdInsertDrive.createParameter("@DriveLetter", adVarchar, adParamInput, 2)
cmdInsertDrive.Parameters.append parmDriveLetter
if text1.text = "" then
      parmDriveLetter = Null
      parmDriveLetter = text1.text
end if

the problem is that it does not pass the NULL value correctly, causing my stored procedure to fail.
How do I pass a "real" null value, something SQL server 7.0 can recognize??

Passing Parameters To SQL Stored Procedure
I am using an ADO connection to SQL Server 2000. I am using the following statement to pass a parameter to the SP:

cmd.Parameters.Append cmd.CreaterParameter ("@rfTagNumberStr", adVarChar, adParamInput, 50, rfTagNumber)

I receive the following error when I try to execute the call:

Procedure 'xxx' expects parameter '@rfTagNumberStr', which was not supplied.

Passing String/integers To From Procedure To Subroutines
Hi everyone,

I am currently doing a game of life program. Let say a user has input rows (m)and columns(n) through an inputbox. After that user will click on any cells within the range of the input. How do we pass the number of rows and columns into the subroutine(neighbours) because it is based on the user's input that the computer will check the number of neighbours.
Below is a code for neighbours that I have done according to the Conway's rules. However the arrayA seems to be empty. I can't transfer the input rows and column into arrayA. Could someone please help me.


Sub neighbours(m%, n%)
Dim neighbours As Integer, i As Integer, j As Integer
Dim endrow As Integer, endcol As Integer

endrow = Selection.Rows.count
endcol = Selection.Columns.count

Dim arrayO() As Single

Set endcell = Selection.Offset(m, n)
Dim arrayA As Variant
arrayA = Range(Selection, endcell).value

endrow = Selection.Rows.count
endcol = Selection.Columns.count

Application.ScreenUpdating = False

If arrayAliaa = 1 Then
neighbours = 0
For i = 1 To endrow 'UBound(arrayAliaa, 1)
For j = 1 To endcol 'UBound(

arrayAliaa, 2)
On Error Resume Next
If arrayA(i - 1, j - 1).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i + 1, j).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i, j + 1).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i + 1, j + 1).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i - 1, j).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i - 1, j + 1).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i, j - 1).value = 1 Then
neighbours = neighbours + 1
End If
If arrayA(i + 1, j - 1).value = 1 Then
neighbours = neighbours + 1
End If
Range(Selection, endcell).value = arrayO
neighboursupdate neighbours arrayA, arrayO
Next j
Next i
End If

End Su

Passing A Structured Array(Type) To A Procedure

I defined a type:

Private Type A
number as integer
value as integer
End Type

Dim B() as A

The size of B is determined dynamically later.
I have a procedure filling B with data.
Now I want to send B to a sorting procedure.

Call Sort(B())
How do I pass such an array to a procedure?

I've tried - Private Sub Sort(Tmp() as A)
didn't work.


Problem Passing Parameters To Stored Procedure
Can anyone help,

I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.

I am getting the following message:
Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
This happens on the objCom.Execute line.

This is my code:

Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database

Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")

Dim objCom As adodb.Command
Set objCom = New adodb.Command
Dim prm_one As adodb.Parameter
Dim prm_two As adodb.Parameter
Dim prm_three As adodb.Parameter
Dim fdate, tdate As Date

Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
Set CRXDatabase = CRXReport.Database

fdate = cboFrom.Value
tdate = cboTo.Value
With objCom
.CommandText = "TestReport"
.CommandType = adCmdStoredProc
.ActiveConnection = cn.ConnectionString
Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
.Parameters.Append prm_one
Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
.Parameters.Append prm_two
Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
.Parameters.Append prm_three

End With

Set SQLRecs = objCom.Execute
CRXDatabase.SetDataSource SQLRecs, 3, 1
This is my Stored Procedure:

CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS

SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1,
COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode,
dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
Thanks in advance


Passing Datetime With Milliseconds To Stored Procedure
Hi, I have a problem of passing a full datetime value from VB6 to SQL Server 2000 using ADO 2.8. It seems the milliseconds are left out.

Here's example code for the stored procedure:

CREATE PROCEDURE dbo.usp_ReturnDatetimeInput (
@Input datetime,
@Output datetime output
--Show input value
print '@Input = ' + convert(varchar(20), @Input, 14)
set @Output = @Input

And my VB6 test function:

Private Function TestDatetimeOutputValue(dInput as Date) As Date
Dim cmd As New ADODB.Command
With cmd
.Parameters.Append .CreateParameter("@Input", adDBTimeStamp, , 8, dInput)
.Parameters.Append .CreateParameter("@Output", adDBTimeStamp, adParamOutput, 8, dInput)

If Not IsEmpty(.Parameters("@Output").Value) Then
TestDatetimeOutputValue = .Parameters("@Output").Value
End If
End With
End Function

Printing the value before and after yields:
2008-04-22 09:46:44.180
2008-04-22 09:46:44.000

Calling the procedure from Query Analyzer:
2008-04-22 09:46:44.180
2008-04-22 09:46:44.180

So how do I make sure the stored procedure gets the full date with milliseconds included?

Passing Variables Into Store Procedure From Combo Box
Hi guys,
I have several questions hoping for someone to answer them as I am having difficulties.
I have a form that currently has two combo boxes; one has a distributor field, and date field as shown below. What I require is the values from both combo boxes (Public distr As String, Public Period As Date, respectively) to be passed in to my two different functions that is Call Dealer_Totals and Call Dealer_Product_Group once I click on the OK button. These two require require arguments of dteMonth as Date, nmeDistributor as String and dteMonth as Date. I usually run these functions in access by using the command: ?Dealer_Totals(#07/01/06#) for the 1st function and ?Dealer_Product_Group("MAZ00",#07/01/06#) as an example.
The 1st combo box distributor is fine in that the data in the combo boxes can be passed straight into Dealer_Product_Group, but the Date combo box (already populated in the format dd/mm/yyyy), needs to be changed to mm/dd/yyyy and then passed into Dealer_Totals.
In conclusion...(sorry about the lengthy question), is
a) how to pass these values from the combo boxes (once the user presses on the OK button) into the other functions that require those arguments, and secondly how to manipulate the date field from the combo box such that it is passed in correctly into the mm/dd/yyyy format.

Here is some of my code that may/may not be useful, also a screen shot of my form_EnterDetails:

Private Sub command2_Click()
DoCmd.OpenForm ("EnterDetails") 'Get Distr variable from other form and then pass into function below
'Call Dealer_Totals(distr)
End Sub

Private Sub command3_Click()
DoCmd.OpenForm ("EnterDetails") 'Get Distr and Period variable from other form and pass into function below (also change date format to mm/dd/yyyy)
'Call Dealer_Product_Group(distr,Period)
End Sub

Passing An Array Value From A Procedure [code Included]
I have a slight problem that I've been bashing my head over for the past 3 hours that I can't seem for the life of me to get a solution for. What I'm doing is quite simple. I have 3 sections setup (Listbox, Label and Command button; listbox, lbl1, cmd1 respectfully.). Now I have a procedure setup so I can open a file, read it's contents and send the file contents to a list box (listbox). I'll explain more after the code is printed :

VB Code:
Public Sub form_load() End Sub  Private Sub testarray(miscarray() As String) Dim number As Integernumber = 0If listbox.ListCount = 0 Then Open ("first.txt") For Input As 1Do Until (EOF(1) = True)    Input #1, miscarray(number)    listbox.AddItem miscarray(number)    number = number + 1LoopClose #1End If End Sub  Public Sub cmd_click()Dim z(10) As StringCall testarray(z)lbl1.Caption = miscarray(4)   End Sub

Now I know this looks like a mess (sorry) but the coding is easy to read. I want the procedure testarray to run when the button is pressed (hence the call function). However, in it, I also want to be able to read the arrays in another procedure (Hence me testing it out with lbl1.caption = miscarray(4) )

But it seems that I get a compiling error when I try this : "Sub or function not defined" and it highlights miscarray(4).

How could I achieve the passing of the variable so I can use the procedure's contents? If anyone could point me in the right direction? Thanks so much in advance!

Passing Multilple Or Statements To Stored Procedure
Is it possible to pass Or statements in one paramenter to a stored procedure.

This is how I created my stored procedure.

VB Code:
CREATE PROC dbo.CigUnits    @StoreNum nchar(3) asIf @StoreNum is nullBegin    Raiserror('Null values are not allowed',14,1)    ReturnEnd    Select Location, [Date], rootitem, ProductCode, SaleItems From Item_Sales     Where  (Location =  @StoreNum)GO

Now in my vb code, I would like to pass more than one vale to this store procedure.

VB Code:
rs.Open "EXEC CigUnits '002 Or Location = 003'"

When I run this, it only brings back store 2's data. I increased the nchar(3) value to be long enough to include the text of my entire Or statement and in my code there is no error, but the file is EOF.

Anyone have any ideas?


Passing A Procedure As A Parameter In A Custom Function?
I know in C++ you can you pass a function pointer as a parameter in a function. Is there anyway to do this in a custom function in VB?

I don't want to use the CallByName method because it requires the method name to be part of an object and because I'm creating a control I do not want my end user to have to store a function in an Class File or a Form just to pass a function as a parameter.

I thought about having the user use AddressOf and then I could call it using the WindowProc API, but I'm not sure if that would work properly.

Example of what I want to do:
Say my control has a custom button such as a help button. I want the user to be able to assign a CallBack function that my control calls whenever a user clicks that help button at runtime.

Just like a CallBack parameter in a API call.

Any help or direction would be greatly appreciated! Thanks.

**Resolved ** - Passing Values From Recordset To A Procedure

I use a function to validate a user id as below

VB Code:
'SQL returns information about the userid passed to it Function validate_op_id() As String Dim objRSuser As ADODB.RecordsetDim UserID As String strsql = "SELECT surname,works_number from dbo.employee where (Works_number = '" & txtinformation.Text _    & "')"Set objRSuser = New Recordset    With objRSuser        .Open strsql, objoCn, adLockOptimistic, adLockPessimistic       '.Requery       End With If objRSuser.BOF = True Or objRSuser.EOF = True Then    strPrompt$ = "Clock Number is Invalid, Please try again,"   strReply$ = MsgBox(strPrompt$, vbRetryCancel, "Invalid Operators_ID")       If strReply$ = vbRetry Then    txtinformation = vbNullString    Load frmscan    Me.Show      End If         If strReply$ = vbCancel Then            Unload Me         End If    txtinformation.SetFocus    SendKeys "{Home}+{End}" End IfEnd Function

Then using the results obtained from the recordset I want to
print them out using a pic control, as so

VB Code:
Private Sub cmdenter_Click() 'Return values from Validate op SQL statementDim objRSuser As ADODB.Recordset     On Error GoTo cmdenter_Click_Error             WndProgress = WndProgress + 1        Progresslogin.Appearance = ccFlat        Progresslogin.Visible = True        Progresslogin.Enabled = True        progressbar_loginvalidate_op_id                        If Not objRSuser.EOF Then                PicResults.Print "Packers_ID"; vbCrLf; objRSuser.GetString(, , vbCrLf)                validate_shift            End If                txtinformation = ""    txtinformation.SetFocus    On Error GoTo 0   Exit Sub cmdenter_Click_Error:     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdenter_Click of Form frmscan"  End Sub

but i receive an error 'object variable not set' I know what the
error means but if i reset the objrsuser object it obviously starts a
fresh and does not keep the results from the function in its memory.. Can anyone help...

Hope I have mad myself clear enough

Passing Stored Procedure To Crystal Report In VB
Hello all

How do I pass a stored procedure to crystal report in VB correctly? I have a
stored procedure named "spTesitng" and I want to execute it and pass the
result to crystal report at run time. But I can't get it to work ...

Following is part of my code:

Set rptReport = Appl.NewReport
Set adoCommand = New ADODB.Command
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "[spTesting]"
adoCommand.CommandType = adCmdStoredProc

rptReport.Database.AddADOCommand adoConnection, adoCommand

Where do I need to make changes?

Thanks for all advise

KUEK Joe Sern

Passing Dates To SQL Server Stored Procedure
Can anyone please help with passing dates from VB6 via ADO to M SQL Server 2000 stored procedures?

The stored procedure works perfectly when run from the Quer Analyzer but fails when run from VB.

In VB I am have.

Set adodbCmd = New ADODB.Command
With adodbCmd
.ActiveConnection = DbCn
'Nominate the command
.CommandText = "SP_dtrSelectINV"
.CommandType = adCmdStoredProc
'Create input parameters
.Parameters.Append .CreateParameter("DebtorCode" adVarChar, adParamInput, 20, DebtorCode)
.Parameters.Append .CreateParameter("PeriodStartDate" adDate, adParamInput, , Format(PeriodStartDate, "mm/dd/yyyy"))
.Parameters.Append .CreateParameter("PeriodEndDate" adDate, adParamInput, , Format(PeriodEndDate, "mm/dd/yyyy"))
End With
Set adodbRs = New ADODB.Recordset
With adodbRs
.CursorLocation = adUseClient
.Open adodbCmd, , adOpenDynamic, adLockOptimistic
If Not (.BOF And .EOF) Then
SelectINVforEnquiry = .RecordCount
SelectINVforEnquiry = 0
End If
End With

The stored procedure looks like this.

@DebtorCode varchar(20), @PeriodStartDate datetime, PeriodEndDat datetime
Select dtrDebtorTrans.dtrTransDate, dtrDebtorTrans.dtrTransAm from dtrDebtorTrans where (dtrDebtorTrans.dtrDebtorCode =3 @DebtorCode) and (dtrDebtorTrans.dtrType = "INV") an (dtrDebtorTrans.dtrTransDate >= @PeriodStartDate) an (dtrDebtorTrans.dtrTransDate <= (@PeriodEndDate)) order b dtrDebtorTrans.dtrTransDate

All my stored prodcures work its only those with dates in the that cause me a problem.

Passing Recordset Back From A Stored Procedure To A VB COM
I have a VB com which must take a user input ( ref number )selected on an ASP page .

A Stored Procedure is then executed on an Oracle DB with the ref number as a passed param. The Stored Procedure will then pass back many records back to the VB component.

I am having great trouble with in accessing the returned recordset in VB, any tips ?

So far I have defined all the returning fields in the Stored Procedure as Tables indexed by binary integers and then used Resultset in VB to access them into a recordset, this is working to a point, but for some reason I get an unspecified error when I attempt to pass anymore than 13 parameters ????

Any help or pointers will be much appreciated.

Passing Null Value In Stored Procedure Call
Hello, I've read the FAQ but couldn't see the answer there. Not much luck googling either.

The question is simple. How do I pass a NULL value to a stored procedure that's expecting a datetime value?

CODEcmd.ActiveConnection = myConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_StoredProc"

cmd.Parameters.Append cmd.CreateParameter("@Number", adInteger, , , nIntValue)
cmd.Parameters.Append cmd.CreateParameter("@Date", adDate, ...

Passing Value To Stored Procedure In SQL Server 2000
i m tryin to enter the value stored in the array sparam() passed by the from to this sub procedure.

Code:Public Sub Add_Client(sparam() As String)
    With cmd
          Set .ActiveConnection = Conn
            .CommandText = "Add_Client"
            .CommandType = adCmdStoredProc
    End With

    For Each prm In cmd.Parameters
            i = 0
            If (prm.Direction = adParamInput Or prm.Direction = adParamInputOutput) Then
                prm.Value = sparam(i)
             'prm.Value = InputBox(prm.Name, "Enter the value")
            End If
            i = i + 1
        Next prm
End Sub

now the line "prm.Value = InputBox(prm.Name, "Enter the value")" woks fine , it enters the data into the database (storedprocedure has an insert statement). but the line" prm.Value = sparam(i)" generates and error "object type mis match"
the code of the stored procedure is given below


@client_id    varchar(8),
@client2_id    varchar(8),
@client_type    varchar(10)

    insert into Client(client_id,party2_id,client_type) Values(@client_id,@client2_id,@client_type)

Edited by - Pyash on 2/9/2005 4:49:50 AM

Passing A Numeric Type From VB To A Store Procedure
I have a SQL 7 server and I created a store procedure which insert a new record into a table.
This table has a field declared as numeric and the SP has a corresponding numeric variable.
From my VB application I have to call this SP; I tried with the ADODB.command way and CreateParameter method.
CreateParameter("myVar", adNumeric, adParamInput, 9, myValue)

When I call Execute I receive this error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Why? Where is the mistake?
The ADO version is 2.6 and my OS is Windows 2000.


Urgent: Passing Controls Reference To A Procedure
How do I pass a control as a refernce to a procedure

I have a class "clsWrapper" Which has a method "Init" that takes one parameter
"ctrl As Control" or "ctrl As Object"

Now In the main form there is a DbGrid Control "DBGrid1"

In the code of the main form I am creating an Object of this class

Dim grdWrap as clsWrapper
Set grdWrap=New clsWrapper

If I now write


This doesn't work

How do I achieve My goal

Please reply ASAP

Thanx and Regards
Vijay Venkataraman

Procedure Name A Variable
Is it possible to make the name of a procedure variable.

For instance, lets say I have several macros in a module. Cell A1 in worksheet 1 contains the name of the macro that I would like to run.

Can I then 'call' the procedure that is named in cell A1.

I have tried the following but it did not work.


Sub Test ()

User_Selection = Worksheets ("Sheet1").Range("A1").Text

Call User_Selection.

End Sub

Any help would be greatly appreciated


Passing All The Contents Of The Listbox As Paramter To The Stored Procedure In Sql Se
i have a list box containing names of some suppliers.

what i am doing in this is that from one list box i am selecting some names on the double_click event and the names selected go into another list box. the contents of this new list box have to passed as parameter to the stored procedure. how can i pass the list of names as parameter to the command object's CreateParameter method.

i have to actually select all the records from a table for all the names selected and passed as a parameter into the stored procedure.

Passing Recordset To Crystal Report In VB Using Stored Procedure
Hello all,

I would like to re-post my question regarding generating crystal reportat run time. I tried to execute the Stored Procedure and then pass therecordset to the crystal report using the following code:

Copyright 2005-08, All rights reserved