when using VBA use an & to append stings
1) when calling a SP with no parms the " goes right after the ST name
StoreProcudureName"
2) when calling a sp with parms a space between the SP and "
StoreProcudureName "
3) when using a string Parm incapusulate it with single quotes with in a
double quote " ' string stuff' ". note don't put space betweek double and
single quotes. did that for clarity.
Here is an VBA code for calling a many parms SP
'Takes the current Address and inputs it into the Datbase
Public Function SP_insertAddress() As Integer
Dim RS As New ADODB.Recordset
Dim validate As Integer
'Stop
If Len(LocalAddress.Address1) = 0 Then LocalAddress.Address1 = " "
If Len(LocalAddress.EmailAddress) = 0 Then LocalAddress.EmailAddress =
" "
If Len(LocalAddress.HomePhone) = 0 Then LocalAddress.HomePhone = " "
If Len(LocalAddress.Country) = 0 Then LocalAddress.Country = "USA"
If Len(LocalAddress.RecordTypeCode) = 0 Then LocalAddress.RecordTypeCode
= "U"
validate = 0
If LocalAddress.Validated = True Then validate = 1
Set RS = CurrentProject.Connection.Execute("SP_InsrtAddress " & _
"""" & Me.Address1 & """" & "," & _
"""" & Trim(Me.Address2) & """" & "," & _
"""" & Trim(Me.City) & """" & "," & _
"""" & Trim(LocalAddress.State) & """" & "," & _
"""" & Trim(LocalAddress.PostalCode) & """" & "," & _
"""" & LocalAddress.EmailAddress & """" & "," & _
"""" & LocalAddress.HomePhone & """" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"""" & LocalAddress.Country & """" & "," & _
"Default" & "," & _
"""" & LocalAddress.RecordTypeCode & """" & ",'" & _
validate & "'," & _
"Default")
LocalAddress.AddressID = RS![AddressID]
RS.Close
SP_insertAddress = LocalAddress.AddressID
End Function
and here is the SP that it callse
/*
****************************************************************************
*******
File: SP_InsrtAddress.sql
Name: SP_InsrtAddress
Desc:
this is the address validate routine.
Notes:
1. Error messages used
sysRaiseError 1001 -- Generic import error
Processing Steps:
1. Verify that this address is USA--only address validated.
2. get the zipcode ID for USA addresses
3. set the region ID for non US addresses
Restart:
Restart from the calling procedure. No code modifications required.
Tables Used:
None Call SP in the Address Database.
Parameters:
None
Return values:
= 0 Success
<> 0 Failure
Called By:
Calls:
address..SPLkupCountryCode
address..SP
Change History:
Date Author Description
---------- --------------- -------------------------------------------
---------
01/07/02 BJ Freeman Imported from Address DB
****************************************************************************
*********
*/
Alter Procedure SP_InsrtAddress
@Address1 varchar(255) = Null,
@Address2 varchar(255) = Null,
@City varchar(100) = Null,
@state varchar(2) = Null,
@PostalCode nvarchar(20) = Null,
@EmailAddress nvarchar(50) = Null,
@HomePhone nvarchar(30) = Null,
@WorkPhone nvarchar(30) = Null,
@WorkExtension nvarchar(20) = Null,
@MobilePhone nvarchar(30) = Null,
@FaxNumber nvarchar(30) = Null,
@TaxRate real = 0.0,
@CarrierRoute char(4) = Null,
@Country nvarchar(50) = Null,
@DeliveryPoint int= Null,
@Addresstype char(1)= Null,
@Validated bit =0,
@CheckDigit int= Null,
@AddressID int =1 OutPut
As
SET Nocount on
----------------------------------------------------------------------------
------
-- Declarations
----------------------------------------------------------------------------
------
DECLARE
@Rows INT
,@Err INT
,@ExitStatus INT
,@Msg VARCHAR(255)
,@ErrReturn INT
,@TRUE INT
,@FALSE INT
,@ZipcodeID int
,@RegionID int
,@ProvinceID int
,@AreaCodeID int
----------------------------------------------------------------------------
------
-- Initializations
----------------------------------------------------------------------------
------
SELECT
@ErrReturn = 1001 -- Error ID
,@ExitStatus = 0
,@FALSE = 0
,@TRUE = 1
,@ZipcodeID =1
,@RegionID =1
,@ProvinceID =1
,@AreaCodeID =1
IF EXISTS (SELECT AddressID
FROM dbo.Addresses
WHERE PostalCode = @PostalCode
AND Address2= @Address2)
BEGIN
SELECT AddressID
FROM dbo.Addresses
WHERE PostalCode = @PostalCode
AND Address2= @Address2
RETURN 0
END
/*
if @new = 1
Begin
EXEC SP_LkUPZipCodeID
@PostalCode,
@ZipcodeID OUTPUT
-----------------------------
-- Is this a USA address
--
-----------------------------
IF @ZipcodeID > 0
begin
SELECT @Country = 'Default' --set for usa
end
EXEC SP_LkUpCountries
@Country,
@RegionID OUTPUT
*/
insert into dbo.Addresses
(
Address1,
Address2,
City ,
ProvinceID,
state,
PostalCode,
ZipcodeID,
RegionID,
EmailAddress,
AreaCodeID,
HomePhone,
WorkPhone,
WorkExtension ,
MobilePhone ,
FaxNumber ,
TaxRate ,
[Carrier Route],
Country,
[Delivery Point],
[Address type],
Validated,
[Check Digit],
Activated,
Updated
)
values
(
@Address1,
@Address2,
@City ,
@ProvinceID,
@state,
@PostalCode,
@ZipcodeID,
@RegionID,
@EmailAddress,
@AreaCodeID,
@HomePhone,
@WorkPhone,
@WorkExtension ,
@MobilePhone ,
@FaxNumber ,
@TaxRate ,
@CarrierRoute,
@Country,
@DeliveryPoint,
@Addresstype,
@Validated,
@CheckDigit,
getdATE(),
getdATE()
)
SELECT @AddressId = @@IDENTITY
Select AddressId=@AddressId
RETURN 0
