VBA and macros for Microsoft Excel, questions on Page 410

[PATSYS]

Hi,

At the bottom of page 410, under ADDIN A RECORD TO THE DATABASE, it says:

"...this way, the userform code can simply call AddTransfer(Style, FromStore, ToStore, Qty)."

And on page 411, this is the code it pertains to:

Sub AddTransfer(Style As Variant, FromStore As Variant, ToStore As Variant, Qty As Long)

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

MyConn = "C:\Documents and Settings\JLI003\Desktop\Invoice number\transfer.mdb"

'Open the connection

Set cnn = New ADODB.Connection

With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open MyConn
End With

'Define the recordset

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer

'Open the table
rst.Open Source:="tblTransfer", ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable

'Add a record

rst.AddNew
'Set up the values for the fields. The first four fields
'are passed from the calling userform. The date field
'is filled with current date

rst("Style") = Style
rst("FromStore") = FromStore
rst("(QTY") = Qty
rst("tDate") = Date
rst("Sent") = False
rst("Receive") = False

'Write the values to this record
rst.Update

'Close
rst.Close
cnn.Close

End Sub

My questions:

1. Do I need to create a userform first in order to call the above SUB?
2. If #1 is yes, do I need to e.g. create textboxes in the userforms, commanbuttons, etc?
3. To call the above code, do I just include a line in the userform event that says

call AddTransfer(Style, FromStore, ToStore, Qty)

The reason for me asking #3 is that when I (as a quick test) ran a SUB that calls AddTransfer(Style, FromStore, ToStore, Qty), it gave me error that says:

Compile Error:
ByRef argument type mismatch

and it is pointing to the Qty

Please advise.

Thanks

 

[MrExcel]

It sounds like the Quantity field is coming in as text.

In the program that calls the transfer program, define Qty as long. Or...if you did not declare, you could use Qty = Qty + 0 to force the text to change to a number.

Bill