Recordsource - Input Parameters 1

[RichM1]

This should be easy I know, but it is frustrating me. I can write all sorts of stored procedures but can't figure this simple sp out. All I want to do is to open a form and have a SP set as the recordsource. Here's the problem: when the form is opened there are two fields that are hidden; initial and lastvalue. All that needs to be done is to pull all records between initial and lastvalue through the SP. I can't get the SP to look at the form for values and for the life of me I can't pass parameters through the forms Input Parameters.

I want the SP to filter on the server side not client side due to the large number of records. Any help is appreciated!

 

[cmmrfrds]

What is your syntax for entering the parameters in the input parameter property. What is the name of the parameters in the SP.

 

[RichM1]

The input parameter is:
@Initial = forms![create barcodes pg3]![initial], @Last=forms![create barcodes pg3]![last]

I've also tried:
@Initial = forms.[create barcodes pg3].[initial], @Last=forms.[create barcodes pg3].[last]

The SP is this:
ALTER PROCEDURE dbo.CreateBarcodeLabelsPG3
(@InitialValue float,
@LastValue float)
AS SELECT [Serial Number], Description, [P/N], Customer, [Customer P/N], [Claim Number], [Date Received]
FROM dbo.Inventory
WHERE ([Serial Number] BETWEEN @Initial AND @Last)


There are two text boxes that are populated on the form before it is opened - these are called Initial and Last. When the form opens all I get is a blank form with none of my text boxes, buttons, etc. on it.

 

[RichM1]

Oops the SP is this - sorry:

ALTER PROCEDURE dbo.CreateBarcodeLabelsPG3
(@Initial float,
@Last float)
AS SELECT [Serial Number], Description, [P/N], Customer, [Customer P/N], [Claim Number], [Date Received]
FROM dbo.Inventory
WHERE ([Serial Number] BETWEEN @Initial AND @Last)

 

[cmmrfrds]

Is the serial number really a floating point number? Why?

First, I would make the parameter variables public and return then through a function. Next, have a separate Form to input the parameter values and then open the report/form from that Form.

In Standard Module.
Public pubinitial as double
Public publastone as double

Public Function ReturnInitial() as double
ReturnInitial = pubinitial
End Function

Public Function ReturnLastone() as double
ReturnLastone = publastone
End Function

In one of the text box events for initial, perhaps beforeupdate put.
pubinitial = textboxinitial
Same for last one.

Input Parameter
@initial=ReturnInitial(),@last=ReturnLastone()

 

[RichM1]

cmmrfrds, Serial Number should've been an integer not floating. Anyhow, I tried what you said and no matter what I try it won't work. When the form is opened it asks for the parameters to be input. Here is what I have in a round about way:

Prior Form (prior to opening form with problem):
dummy = ReturnInitial()
dummy = ReturnLastOne()

Functions:
Public Function ReturnInitial() As Double
pubInitial = IV 'IV is first S/N
ReturnInitial = pubInitial
End Function

Public Function ReturnLastOne() As Double
pubLastOne = LASTVALUE 'Lastvalue is last S/N
ReturnLastOne = pubLastOne
End Function

Declarations:
Public pubInitial As Double
Public pubLastOne As Double

Stored Procedure:
ALTER PROCEDURE dbo.CreateBarcodeLabelsPG3
(@Initial INTEGER,
@Last INTEGER)
AS SELECT [Serial Number], Description, [Bosch P/N], Customer, [Customer P/N], [Claim Number], [Date Received]
FROM dbo.Inventory
WHERE ([Serial Number] BETWEEN @Initial AND @Last)


Form Input Parameters:
@Initial=ReturnInitial(), @Last=ReturnLastOne()

 

[cmmrfrds]

Lets snyc up the data types. Where I had double which is a floating point number change to Long which is long integer.

IN the SP change integer to int.
(@Initial int,
@Last int)

These declaration are after the option in the front of the module not inside a function otherwise they are scoped inside the function. Do you have option explicit so that access requires the variable to be defined before using.

Option Explicit
Public pubInitial As Double
Public pubLastOne As Double

After making these change paste in exactly what you have defined.

 

[RichM1]

Still doesn't work for some reason. I tried establishing a filter when form opens which yielded the same results as when I tried using a SP, just a blank form with no buttons, boxes, etc. Long story short, max records wasn't set high enough. Is there something similar I'm missing on the server side that is simlar to this because the results were the same until I set max records to 0. Still can't get the SP to work. Here is what I have now:

DECLARATIONS:
Option Compare Database
Option Explicit
Public pubInitial As Long
Public pubLastOne As Long


Prior Form (prior to opening form with problem):
dummy = ReturnInitial()
dummy = ReturnLastOne()

FUNCTIONS:
Public Function ReturnInitial() As Long
ReturnInitial = pubInitial
End Function

Public Function ReturnLastOne() As Long
ReturnLastOne = pubLastOne
End Function


Form Input Parameters:
@Initial=ReturnInitial(), @Last=ReturnLastOne()


One last question: Is it all that bad with ADO to filter at the form instead of at the server? I can make this work by using the form filter - still makes me mad that something this simple won't work.

 

[cmmrfrds]

Is this what you have in the record source for the Form.
dbo.CreateBarcodeLabelsPG3

What you have should work.
The reason that the Form is coming up blank is more than likely because a recordset is not being returned. Lets make sure of the variables.

In the OnOpen event of the Form put.
Debug.Print "initial = "; ReturnInitial()
Debug.Print "last = "; ReturnLastone()

Then do a Control G to bring up the debug window. You should see the starting and ending values. Yes?

 

[RichM1]

Yes, the recordsource is set as CreateBarcodeLabelsPG3. I did the watch and the input parameters are being set correctly with the correct values. For some reason the input parameters are not receiving the results.

I tried changing the input parameters to this:
@Initial=forms![create barcodes pg3]![initial], @Last=forms![create barcodes pg3]![last]

Strangely enough it worked. I had to include a .requery for the recordsource but it works. Not sure why the other didn't. Anyhow, thanks for all your help.