Using Recordset as source of SQL Add/Update

[David]

Hello,

I have a subform that I'd like to do "wholesale" updates to records that are
in that subform. The subform is a datasheet, the source of which is a query
of two joined tables.

The main form has a user selectable filtering function which drives which
records appear in the subform. The main form also has a drop down box, which
asks the user the value they want to update a certain field to.

I'd like to use the recordset of the subform as the basis for this update.
As the datasheet records are based on a query, with primary and secondary
table, the field to be updated MAY or MAY NOT already have a record in it.

Question: How can I use the visible recordsource as the dataset to be
updated, and how can I add (where there is no record) or update (where there
is a record) the secondary table with the user selectable update process?

Thanks!

 

[Rod]

David,

How you might do this depends on the way in which the subform is related to
the main form.

Is the subform related to the main form in a child - master relationship, or
does the 'user selectable filtering function' set the filter property of the
subform, or does it change the recordsource property of the subform by
modifying the WHERE clause of the SQL statement?

While it is often possible to update fields of a query containing a join, it
is not always possible. You may be better to run a series of queries based
on the tables contained in the join which will do the job using the
condition set by you user selected filter.

Something like:

strSQL = "UPDATE table2 INNER JOIN table1 ON table2.CommonKey =
table1.CommonKey "
strSQL = strSQL & "SET MatchFieldN = " & UserValue & " WHERE
table2.FilterField = " & UserSelectedFilterValue
CurrentProject.Connection.Execute strSQL

to update the existing records, (this version assumes that the filter value
and the value to update are numeric,)
and

strSQL = "INSERT INTO table1 (Field1, Field2, ... , FieldN) SELECT
MatchField1, MatchField2, ... , """ & UserValue & """AS MatchFieldN "
strSQL = strSQL & "FROM table2 LEFT JOIN table1 ON table2.CommonKey =
table1.CommonKey "
strSQL = strSQL & "WHERE table1.CommonKey IS NULL AND table2.FilterField =
""" & UserSelectedFilterValue & """"
CurrentProject.Connection.Execute strSQL

to add the new records, (this version assumes that the filter value and the
value to update are alphanumeric hence the double " marks inside the
strings.)

This also assumes you are using a version of Access >= 2000.

I hope this makes some sense. If not let me know. In that case I'll be
able to offer more help if I have a better idea of the structure of the
tables you are dealing with, and of the query that joins them for the
subform.

Rod.

 

[David]

Rod,

Thanks for the response.

I think you anticipated much of what I was asking.

The subform is NOT at all related to the main form. The main form only
houses the "filter" function.

The subform opens using a query as the .recordsource. If the user decides
to use the "filtering" function, then the .recordsource become a predefined
SQL statement, which I pull from a table, based on the selections in the
"filtering" function.

NOTE: The filtering function is a custom function, with 2 rows of 2 combo
boxes, and an AND/OR combo box between the rows. The first combo box is a
list of fields the user can select to do the filter on, the 2nd combo box is
a list of valid values for that selected field. Second row of combo boxes is
the same.