Updating column in a form's Recordset once open

  • Thread starter Thread starter souvenir
  • Start date Start date
S

souvenir

Hi

I guess I've reached the limit of my abilities with this... I've a
form with a SQL statement which includes a dummy column I'd like to
set in the "On Open" event of the form.

Imagine the select is:
SELECT Company.CompanyName, Company.Status, ' ' AS AlreadyRun, FROM
Company

I want to programmatically change the AlreadyRun column for each row
in the Recordset. I don't need to save the column to a table or
anything - it's used to show users something.

I've been trying, in the Form's On Open event:
Private Sub Form_Open(Cancel As Integer)
Dim RAset As DAO.Recordset
Set RAset = Me.Recordset

Do Until RAset.EOF
With RAset
.Edit
!AlreadyRun = "whatever i want"
.Update
.Bookmark = .LastModified
End With
RAset.MoveNext
Loop

Set RAset = Nothing
End Sub

Thanks in advance.
 
.... and I gues what you are getting is an error saying something akin to the
fact that Access cannot set the value of this control.

The Space you have included in the SQL as 'AlreadyRun' is treated as a
constant; it's not updateable. This applies for any expression.

OK, you can include an unbound textbox in your form design but be warned
that if the form is a continuous form then all lines will show the same
value. Change the value of the unbound textbox in one line and all other
lines will also change to that value. If you want different values on
different lines then the only way is to include an extra column on the
underlying table.

Tell me which of the foregoing you want and I will suggest something.

Rod
 
Thanks for your reply.

It is a continuous form... and I want different rows to have different
values.

I want to set this AlreadyRun column to differing values depending on
the other columns in any given row. The actual sql is more like:
SELECT Company.CompanyName, Company.Status,
MyAccessFunction(Company.Status) AS FunctionResult, ' ' AS AlreadyRun,
FROM Company

And programmatically I need to update AlreadyRun on the basis of the
FunctionResult. There's some chunky logic which i could not perform in
the SQL which I needed to hive off into a VB scripted
MyAccessFunction(). Furthermore, the logic to generate the AlreadyRun
field requires the FunctionResult and another SQL call taking the
FunctionResult as an input (hope this makes sense!).

I must say it seems aweful that there's no simple way to have a not
field in a dataset not bound to a table column in which can be
changed.
 
Thanks for your reply.

It is a continuous form... and I want different rows to have different
values.

I want to set this AlreadyRun column to differing values depending on
the other columns in any given row. The actual sql is more like:
SELECT Company.CompanyName, Company.Status,
MyAccessFunction(Company.Status) AS FunctionResult, ' ' AS AlreadyRun,
FROM Company

And programmatically I need to update AlreadyRun on the basis of the
FunctionResult. There's some chunky logic which i could not perform in
the SQL which I needed to hive off into a VB scripted
MyAccessFunction(). Furthermore, the logic to generate the AlreadyRun
field requires the FunctionResult and another SQL call taking the
FunctionResult as an input (hope this makes sense!).

I must say it seems aweful that there's no simple way to have a not
field in a dataset not bound to a table column in which can be
changed.
Click to expand...

You should be able to come up with another function that returns that value.
Then you can use...

SELECT Company.CompanyName, Company.Status,
MyAccessFunction(Company.Status) AS FunctionResult,
MyOtherAccessFunction(MyAccessFunction(Company.Status)) AS AlreadyRun,
FROM Company
 
You should be able to come up with another function that returns that value.
Then you can use...

SELECT Company.CompanyName, Company.Status,
MyAccessFunction(Company.Status) AS FunctionResult,
MyOtherAccessFunction(MyAccessFunction(Company.Status)) AS AlreadyRun,
FROM Company

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks. I'll give that a try.

Note that the MyAccessFunction() parses through a different Recordset
of business rules to generate a string for the users (a 'suggested
next action', actually). The MyOtherAccessFunction() will then need to
perform a SELECT against an Audit table to ensure that this 'suggested
next action' hasn't already been performed, via a WHERE which limits
to the CompanyID and the 'suggested next action'.

Soooo... is there any concern that the performance of the system will
slow down as the number of Audit rows and the number of companies
increases over time?
 
Hi,

First I will endorse your plea for some mechanism to add one or more
updateable columns to a recordset that are unbound and can hold different
values for each row. The nearest I come to a solution is the selectable list
box where at least you can check one or more rows, analagous to having an
unbound check box on each row. This does solve many of the requirements.

I have been 'playing around' with ADO disconnected recordsets and adding new
fields to the recordsets in the hope of finding a more flexible solution, so
far without success - I can't get the form to recognise the added fields.
(Anyone?)

Rick's suggestion has promise. Also consider nested SQL and if all else
fails resort to an Update query. In the last case I think you will have to
build a temporary table with the extra columns.

As for performance, use of functions will slow down the query. However if
you are using a stand-alone Access mdb then don't worry too much. With an
upper limit of 2Gb (is this still the same in v2007?) there will never be
anything big enough to cause serious problems. If you are using a
client/server implementation then cursor locations become a consideration.
Finally if you are linking to an external database then performance can be an
issue if the external database is large.

Regards,

Rod
 
I have been 'playing around' withADOdisconnected recordsets and adding new
fields to the recordsets in the hope of finding a more flexible solution, so
far without success - I can't get the form to recognise the added fields.
(Anyone?)
Click to expand...

You can fabricate a disconnected recordset, with new Fields as
required, and 'manually' read the data from the 'fetched' recordset.
This is a bit 'long-winded', so if you just need extra columns in a
disconnected recordset to which data can be written, you can use the
MSDataShape provider and the APPEND NEW syntax. Whether this will "get
the form to recognise the added fields" I do not know.

Here's a quick VBA example:

Sub ShapeAppendField()
' Kill Environ$("temp") & "\DropMe1.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
Set .ActiveConnection = Nothing
End With

Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
.CursorLocation = 3
.Open

.Execute _
"CREATE TABLE Test (" & _
"existing_col INTEGER NOT NULL);"
.Execute _
"INSERT INTO Test (existing_col)" & _
" VALUES (1);"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
.CursorType = 2
.LockType = 4
.Source = _
"SHAPE {" & _
" SELECT existing_col" & _
" FROM Test" & _
"} APPEND NEW adInteger AS new_col"

Set .ActiveConnection = con
.Open
Set .ActiveConnection = Nothing

.Fields("new_col").value = 55
MsgBox .GetString
.Close

End With
End With
End Sub

Jamie.

--
 
Jamie,

Thanks for that. I had never thought of using data shaping and APPEND NEW to
solve the additional column requirement. I've just performed a makeshift test
of the concept and there's good news and even more good news.

The good news for Souvenir is that the form will bind to the appended column
and display different data for each row. It does not seem to matter whether
the recordset is disconnected or not. Before instantiating the form,
populate the appended column and issue a recordset.update. Then set the
recordset property of the form to be the shaped recordset.

(Souvenir I will explain with examples how I did this if you want.)

Now ... If the recordset is still connected then the form field bound to the
appended column behaves somewhat like a disabled field; it's not updateable,
in fact you can't type anything in it. However if the recordset is
disconnected then it is updateable.

OK now I need the last piece for my particular jigsaw which is how to build
a recodset from scratch and make it behave the same way. The shaped recordset
used here was in part based on a table and 'fooled' the form into binding to
it. I suppose as a last resort I could always use data shaping and select one
column from a table and then append as many columns as I need to display my
collection data.

Regards,

Rod
 
The good news for Souvenir is that the form will bind to the appended column
and display different data for each row. It does not seem to matter whether
the recordset is disconnected or not. Before instantiating the form,
populate the appended column and issue a recordset.update. Then set the
recordset property of the form to be the shaped recordset.

the last piece for my particular jigsaw which is how to build
a recodset from scratch and make it behave the same way. The shaped recordset
used here was in part based on a table and 'fooled' the form into binding to
it.
Click to expand...

You can of course completely fabricate recordsets, SHAPE recordsets if
required (e.g. see http://support.microsoft.com/default.aspx?scid=kb;en-us;196029),
but as regards bound forms I can only wish you good luck :)

Jamie.

--
 
Hi again Jamie,

I was aware of the MSDataShape ability to fabricate recordsets from scratch
but had never used it. Just completed another test doing just that and my
Access form successfully bound to the recordset. So forget my drivel about
having to have a table column to 'fool' an Access a form to bind to the
recordset. In fact using a connection string of: "Provider = MSDataShape;
Data Provider = NONE;" is all that's needed.

Only one glitch though: I can't add new rows to the recordset through the
form. For me this is inconsequential as the logic of the scenarios when I
want to use this technique dictate that adding new rows is an illogical
requirement.

So for future reference if you want to bind a form to a recordset that is
not based on tables, use MSDataShape. Don't as I did spend hours trying to
get a form to bind to a recordset built with the recordset.fields.add method
et al.

Thanks again,

Rod
 
Back
Top