Currently I am trying to pull records from a stored procedure using
parameters (I've tested simple tables trying to create updatable
form/recordsets as well with no success however) down to a continuous
form in an access 2000 project. I've succeeded in pulling the records
down however they are not updatable (from the form). If I use a
Server Side cursor, then the method "Set Me.Recordset = objRst" Causes
the error "The Object You entered is not a valid Recordset Property".
If I set the cursor to Client side then set my form to
me.Recordset=ObjRst, the records are displayed in continuous fashion
as they should, however if I type into the form nothing happens except
a message that says "The Recordset is not Updatable".
I decided to try to make a work around by using the client side
cursor, then adding additional text boxes to the continuous form,
while hiding the actual recordset text boxes. Then I set Each text
box value equal to a recordset text box value. The result: the first
record values are shown in the non-recordset text boxes, (in the
detail section) of the continuous form and I can actually type in the
box (which would allow me to execute an update to the database as soon
as the text box looses focus). But, the rest of the record detail
sections on the form also show the first record data , even though the
text boxes that are bound to the recordset are showing the correct
record data. I was hoping that the two sets of text boxes would be in
My goal is to pull the recordset down from a parameterized (multiple
table joined) stored procedure (Using the Contains keyword in the
where clause because the records will be searched using keywords typed
into a form field) into an .adp form so a person may tab between
records and record fields while making changes. The changes need to
be saved automatically as the textbox looses focus.
The code I am using:
Private Sub Form_Open(Cancel As Integer)
Dim vStartdate, vStartTime, vSearchString As String
vStartdate = "12/22/1999"
vStartTime = "12:00:00"
vSearchString = "Crystal"
Set con = New ADODB.Connection
.Provider = "MSDataShape"
.ConnectionString = "Provider=SQLOLEDB.1;Integrated
.CursorLocation = adUseServer
' .CursorLocation = adUseClient
Set cmd = New ADODB.Command
.ActiveConnection = con
.CommandText = "STP_Search"
.CommandType = adCmdStoredProc
.Parameters(1).Value = vSearchString
.Parameters(2).Value = vStartdate
.Parameters(3).Value = vStartTime
Set objRst = New ADODB.Recordset
' .CursorLocation = adUseServer
.CursorLocation = adUseClient
.ActiveConnection = con
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
Set Me.Recordset = objRst
Me.UniqueTable = "tbl_TableToEdit"
I've managed to pull down and edit in a form a SqlServer View, however
can you use the CONTAINS and other keywords in a View (I am using this
to search for keywords and keyword variations such as "cry*"? Can you
pass parameters to a view?
I've been working on this for 3 days straight. Some Expert advice
will be GREATLY appreciated.
BTW, I am using ADO 2.7, Access 2000 SP-3, Sql Server 2000, Windows
Server 2000 SP-3