Inserting data into Access form from SQL Stored Procedure

[Josh Strickland]

I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDetail (SQL Table that contains the information in the
form)
frmAccountingEntry (Access form used to enter data)
spGetCustomerInformation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER_AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connection
gcn.CursorLocation = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.CommandText = "spGetCustomerInformation"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@CUSTOMER_NUMBER", adVarChar,
adParamInput, 6, Forms!frmAccountingEntry!CUSTOMER_NUMBER.Value)
Set .ActiveConnection = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustomerInformation
(@CUSTOMER_NUMBER varchar(6))
AS
SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUST
WHERE (CUSTOMER_NUMBER = @CUSTOMER_NUMBER)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBER'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh

[MGFoster]

Josh Strickland wrote:

I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDetail (SQL Table that contains the information in the
form)
frmAccountingEntry (Access form used to enter data)
spGetCustomerInformation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER_AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connection
gcn.CursorLocation = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.CommandText = "spGetCustomerInformation"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@CUSTOMER_NUMBER", adVarChar,
adParamInput, 6, Forms!frmAccountingEntry!CUSTOMER_NUMBER.Value)
Set .ActiveConnection = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustomerInformation
(@CUSTOMER_NUMBER varchar(6))
AS
SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUST
WHERE (CUSTOMER_NUMBER = @CUSTOMER_NUMBER)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBER'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I don't work w/ ADO - mainly 'cuz I can get the info I need in DAO in
about 5 lines of code where ADO requires 10-15 lines. Anyway, . . .
I'll assume you're using Access 2000, or Access XP; can't you set the
form's Recordset to the recordset returned by the stored procedure?
E.g.:

Set Me.Recordset = rs

Here's some code from Acc XP Help on "Recordset Property."

Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

' See - this line sets the form's recordset...:::mgf
Set Forms("Suppliers").Recordset = rstSuppliers

' Not sure why this line is needed, but it works...:::mgf
Forms("Suppliers").UniqueTable = "Suppliers"

End Sub

HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4dfQYechKqOuFEgEQKQGwCdHA2DZvZUlq7C82CXfTdxry snsNwAn2vd
AQO2Wudr+5FPVuV37Fangt8/
=Rb4i
-----END PGP SIGNATURE-----

[Fletcher Arnold]

"Josh Strickland" <js*********@btcelectronics.com> wrote in message
news:ad**************************@posting.google.c om...

I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDetail (SQL Table that contains the information in the
form)
frmAccountingEntry (Access form used to enter data)
spGetCustomerInformation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER_AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connection
gcn.CursorLocation = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.CommandText = "spGetCustomerInformation"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@CUSTOMER_NUMBER", adVarChar,
adParamInput, 6, Forms!frmAccountingEntry!CUSTOMER_NUMBER.Value)
Set .ActiveConnection = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustomerInformation
(@CUSTOMER_NUMBER varchar(6))
AS
SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUST
WHERE (CUSTOMER_NUMBER = @CUSTOMER_NUMBER)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBER'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh
It's a bit hard to see exactly where it's going wrong but I would check here
first:
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute

You've already set rs, then you open it, then you re-set it? Why?

As you step through your code, can you check this is giving the value you
expect, before passing it to the stored procedure:

Forms!frmAccountingEntry!CUSTOMER_NUMBER.Value

Also what about some more general error handling for the sub and why is
there no dim statement for MaybeMatch and why does it not follow a naming
convention.
Just some ideas

Fletcher