Some questions about Recordset?

[Ed from AZ]

I just discovered Recordsets last night as I was working through
learning Access (on my own, not a course). I ran a query against my
main data table using a SQL statement, created a Recordset of the
results, and then wrote that into a second table using a second
Recordset and connection. The second table is displayed as a subform.

I would like to edit the data in the second table and write that back
into the main data table. The first Recordset should have the key
field (Autonumber) of the records included. I'm assuming editing the
subform table doesn't affect the actual data in the Recordset?
Especially since it's built from the second connection?

What should I look at to edit the data and write it back to the proper
records in the main data table?

Ed

 

[tina]

as a learning exercise, that's fine. but for practical purposes, you can do
the same thing more efficiently by simply turning the original SELECT query
into an Append query, and append the queried records directly into the other
table. also, again for practical purposes, i wouldn't recommend writing data
to a temporary table, editing it, and then writing it back into the main
table, overwriting the original data (an Update query would do that, btw, no
need to use a recordset); in a multi-user environment, that could quickly
get very ugly - one user editing data in the main table, while another user
edits data in the same record in the temp table and then overwrites the
maintable data - with no way for Access to warn you about it. if you want to
show the same records in a mainform *and* its' subform, and be able to edit
the records in both, you can do that - and in fact i've used such a setup
more than once. the advantage of using the same table in both mainform and
subform RecordSources is that a user won't have a conflict between his/her
own changes - when s/he moves to the subform, changes to the mainform record
are automatically written to the table first, and the same happens on moving
from the subform to the mainform.

hth

 

[Albert D. Kallal]

You could as a idea just stuff that sql used to select those records into
the forms data source..


eg:


dim strSql as string

strSql = "some sql goes here"

me.MySubForm.Form.RecordSource = strSql

That all you need...I count 3 lines of code. And, really, we could go:

me.MySubForm.Form.RecordSource = "some sql goes here"

So, now we down to one line of cod here. Save all that time to go out have a
great dinner, or spend more time with the kids ;-)

You could certainly write a bunch of code to try and shuffle the data back
from that 2nd table, but you have to figure out what records been changed,
and which ones have not. Or, perhaps just write back all of the records as
that would be simpler. Furth, you could even use that reocrdset as the data
source for the sub-form, but at the end of the day, if you already have the
sql that selects the records, then why not just base that form (or sub-form)
on that sql in the first place?

To start writing a bunch of looping + recordset code does not really make
sense here. You could also I suppose set up an additional column in that 2nd
table to "figure out" or "keep track" of which records have been modified.
However, all this work means your much re-inventing the wheel as to what is
already built in access to save having to write all that code.

And, in place of using recordsets to "copy" the data to the 2nd table, you
should use an append query.

eg: to send all records of an given city to a "new" table, you would go:

dim strSql as string

strSql = "SELECT * INTO newtable " & _
"FROM tblCustomers WHERE City = 'Edmonton'"

currentdb.Execute strSql

The above would create a new table called "newtable" and append all records
from city = 'Edmonton'

And, if you wanted to send this data into a existing table, then

strSql "INSERT INTO ExistingTable " & _
"SELECT * FROM tblColors WHERE City = 'Edmonton'"


So, we don't have to use, nor get involved with all those reocrdsets, as it
only takes about 1 or 2 lines of code to copy those reocrds using the above
approach....

Also, keep in mind that you would have to "empty" that 2nd table each time
you use it Deleting reocrds means your data base will bloat as access does
NOT reclaim deleted reocrds space until you do a compact and repair. Hence,
avoid designs that constantly delete reocrds as that causes a LOT of bloat
(growth) in your database size.

It just really don't make sense to have to write a whole bunch of code to
send data to a 2nd table, and then shuffle it back to the original table.
For sure this approach can give you some additional control over when data
is returned, but the amount of work this involves becomes rather large, and
worse ms-access was not designed to work this way...

 

[Ed from AZ]

Thank you, Albert and Tina, for chiming in here. Please bear with me
as I am not at all (obviously!!) familiar with Access. I have been
trying to create a way for my users to view and edit exisiting data
(see this thread: http://tinyurl.com/ahjadf ). I am used to using VBA
to beat Excel into submission, so I'm probably tending to treat Access
the same way. I'm not familiar with th euilt-in capabilities, so when
I see code and I can make it work I feel right at home!

So as not to duplicate the other thread, I'll try to stick with
learning about Recordsets here. The practical application is pulling
out a set of records for my users to edit and then updating the main
data table with those changes. In Excel, I'd create and array, write
the values to a range of cells, read the revised cell values back into
the array, and then write the array back into the original cells.

In Access, the Recordset seems to be an integrated array of the
specified records. That's wonderful! But it's still like the array
values - until I put them out there for the user to edit, and then
capture the edits and write them back into the table, all I have is a
collection of values in an array in memory. So I thought I would dive
into manipulating Recordsets. Even if that is not my current
solution, it probably couldn't hurt to understand them better.

Here's how I'm creating the Recordsets:

Private Sub btnRunSQL_Click()

'Recordset connection to query
Dim myCon As ADODB.Connection
Dim myRS As New ADODB.Recordset
Set myCon = CurrentProject.Connection
myRS.ActiveConnection = myCon

'Recordset connection to Table1
Dim tblCon As ADODB.Connection
Dim tblRS As New ADODB.Recordset
Set tblCon = CurrentProject.Connection
tblRS.ActiveConnection = tblCon

Dim strSQL As String
Dim strType As String
Dim dtDate As Date

strType = "His"
dtDate = #2/1/2009#

'Query data table
strSQL = " SELECT [MyData].[ItemType], [MyData].[ItemID], [MyData].
[WkDate], [MyData].[ThisField], [MyData].[ThatField] " & _
" FROM MyData WHERE ((([MyData].[ItemType])='" & strType &
"') And (([MyData].[WkDate])=#" & dtDate & "#))"
'And (([MyData].[ItemID])='Pants')

myRS.Open strSQL, , adOpenDynamic

'Open subform table
Set tblRS = New ADODB.Recordset
tblRS.CursorType = adOpenKeyset
tblRS.LockType = adLockOptimistic
tblRS.Open "Table1", tblCon, , , adCmdTable

'Iterate through query results to
'populate subform table
myRS.MoveFirst
While Not myRS.EOF
tblRS.AddNew
tblRS!Field1 = myRS.Fields(3).Value
tblRS!Field2 = myRS.Fields(4).Value
tblRS.Update
myRS.MoveNext
Wend

'Refresh form to show filled subform
Me.Refresh

'Clean up
myRS.Close
tblRS.Close
myCon.Close
tblCon.Close

End Sub

Ed

 

[Albert D. Kallal]

Thank you, Albert and Tina, for chiming in here. Please bear with me

as I am not at all (obviously!!) familiar with Access. I have been
trying to create a way for my users to view and edit exisiting data

Right, and in reading that previous thread, I once again am at a loss as to
why ANY reocrdsets are being used here?

From reading the previous, it looks likes you want to display a "list"
of records, perhaps the result of a search/criteria the user
enters. You then want the user to select ONE record and load up a
form that allows the user to edit that record, and then when done
the user saves...and is back to that "list" ready to do battle
with the next record.

Typical seach forms in access look like:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

and

http://www.members.shaw.ca/AlbertKallal/Search/index.html

So, take quick look at the above two web pages.

None of the above screen examples that allow you to bring up "one" reocrd
into a form form the "resulting list" of reocrds requites to you to write or
use reordset code. The recordset idea is just way too much work and worse
makes MORE problems for you to solve. As Tina in the other thread pointed
out, your apporach will also be VERY problematic in a multi-user envoment.

There is just ZERO, I repeat ZERO advangate to using this WHACK of code and
recordsets and a temp table.

I mean, **IF** we were going to copy that table data, then we would go:


strSQL = "INSERT INTO table1 " & _
"SELECT ItemType, ItemID, WkDate, ThisField, ThatField " & _
"FROM MyData WHERE ItemType = '" & strType &"' And " & _
"WkDate = #" & dtDate & "# And ItemID ='Pants'"

currentdb.Execute strSQL

That is it!!! The above replaces the two recodsets you have. I
mean,..ok..great we have spent some time with reocrdsets..and there not
really anything more to learn then what you done so far.

The simply solution here is to fill a form with a "list" of results from
your search, and then have ea button in the detail section of that form that
when pressed launches a whole new form to that ONE record....

docmd.OpenForm "frmEditDetals",,,"id = " & me!id

The above code will open a form to the ONE record that is currently selected
in the our continues form. The user will edit, and then close that
form...and be right back to the continues list form....

I have a "sample" continues form with a button on it in my super easy merge
example here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[David W. Fenton]

m:

So as not to duplicate the other thread, I'll try to stick with
learning about Recordsets here. The practical application is
pulling out a set of records for my users to edit and then
updating the main data table with those changes.

You don't need even one line of code to do this. Nor do you need
recordsets.

You need to create a form to display the data, then decide how you
want to filter it to a particular set of records (which may require
code).

But first, create the form.

Once you've described what you want filtered and on what basis, we
can suggest ways to implement that.

You're making this *entirely* too difficult.

 

[Ed from AZ]

David - welcome to the fun!

You're making this *entirely* too difficult.

That seems to be one of my trademarks! 8>\

Albert said:

Right, and in reading that previous thread, I once again am at a loss as to
why ANY reocrdsets are being used here?

Well, I went poking and prodding through Access Help and my For
Dummies book, and found this thing called "Recordset" that would
contain the data I told it to go get. Since that's what I was after,
that's what I played with. And it worked!! I got my data. Then it
was just a matter of deciding what to do with it. All of this, you
understand, is within the context of what I can comprehend about
Access, namely forms and code, which I've used before with great fun
in VB6 and Excel.

From reading the previous, it looks likes you want to display a "list"
of records, perhaps the result of a search/criteria the user
enters. You then want the user to select ONE record and load up a
form that allows the user to edit that record, and then when done
the user saves...and is back to that "list" ready to do battle
with the next record.

Almost it exactly!

Once you've described what you want filtered and on what basis, we
can suggest ways to implement that.

Okay, David - here goes!
(copied from the other referenced thread)

The data table is set up with five data fields (other than the
Autonumber): ItemType, ItemID, WkDate, WkType, and QtyWk. For any
unique combination of ItemType, ItemID, and WkDate, there may be
several records, each with a different WkType.

This is my play-data table:
ID ItemType ItemID WkDate WkType QtyWk
1 His Shirt 01-Feb-09 SM 2
2 His Shirt 02-Feb-09 MED 3
3 His Shirt 03-Feb-09 XL 4
4 His Pants 01-Feb-09 SM 2
5 His Pants 02-Feb-09 MED 3
6 His Pants 03-Feb-09 XL 4
7 Hers Shirt 01-Feb-09 SM 2
8 Hers Shirt 02-Feb-09 MED 3
9 Hers Shirt 03-Feb-09 XL 4
10 Hers Pants 01-Feb-09 SM 2
11 Hers Pants 02-Feb-09 MED 3
12 Hers Pants 03-Feb-09 XL 4

I have a Data Input form - two dependant listboxes and a textbox on
the top half, and multiple pairs of listboxes and textboxes. The
user
selects ItemType and ItemID and enters a date. Then the user enters
pairs of WkType and QtyWrk data. When the Enter Data buton is
clicked, the code captures the ItemType, ItemID, and WkDate. This
combination should be unique, so I use DCount to check the main data
table for duplicates. If none are found, then the code iterates
through the pairs of list/text boxes to build INSERT statements and
write the data into the data table.


This View/Edit data form would be in case there was an existing
entry;
the user could open this form and verify the exisiting data and
change
it to remove the conflicts. Perhaps the existing data was enetered
against the wrong day. Or maybe they know the day's data is
incorrect
and need to adjust it.

I already have the ItemType, ItemID, and WkDate captured - all that
remains is to query the data table against those values and display
the results in such a way that the user can easily edit the data and
click a button to overwrite the existing data records.

So there it is in a nutshell. Over the weekend, when I didn't have an
Internet connection and couldn't get back here, I played some more and
found the SQL for UPDATE queries. So I thought all I would need to do
is find a way to display the data in my recordset so the user can make
changes, iterate through the data to pick up the changes, and create
UPDATE SQL statements to write all that back.

I must admit, Albert, your examples with the continuous form look
great - no code and editable back to the main table. Where do I sign
up??

Ed

 

[David W. Fenton]

m:

I already have the ItemType, ItemID, and WkDate captured - all
that remains is to query the data table against those values and
display the results in such a way that the user can easily edit
the data and click a button to overwrite the existing data
records.

You need to present a subset of data based on those three fields.

First, create a form that displays all the data from the returned
records, using "SELECT MyTable.* FROM MyTable" as your Recordsource.

Then you have a choice:

1. use the form's filter property to limit to the selected values,
OR

2. change the form's Recordsource.

I would tend to do the latter, and make the form's saved
Recordsource return one uneditable record. Here's the SQL to do
that:

SELECT TOP 1 Null As Field1, Null As Field2, Null As Field3
FROM MyTable

In that case, the field aliases (Field1, Field2, etc.) would be
replaced with the names of the fields in your actual table, so that
the bound controls will be displaying the Null from your default
recordsource.

What this accomplishes is that when you open the form, it displays
nothing but an empty, non-editable record (and you can't add
records, either, as it's bound to a Recordsource that to which you
can add no records).

To filter your records, you need 3 unbound controls for ItemType,
ItemID and WkDate. I would suggest that the first two should be
combo boxes, since there are clearly a limited number of choices for
that. You might also make WkDate a dropdown list, given that you are
only going to return data for dates that are in that field.

Then have a command button labeled FIND, and the event behind it
would be something like this:

Private Sub cmdFind_Click()
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT MyTable.* FROM MyTable "
strWhere = "MyTable.ItemType=" & Me!cmbItemType
strWhere = strWhere & "MyTable.ItemID=" & Me!cmbItemID
strWhere = "MyTable.WkDate=#" & Me!cmbWkDate & "#;"
Me.Recordsource = strSQL & strWhere
End Sub

Now, a few points:

1. You likely don't want the user to be able to click the FIND
button until all three fields have been filled out. So, you'd make
cmbFind disabled by default, and write a little Function:

Private Function EnableFind() As Boolean
EnableFind = (Not IsNull(Me!cmbItemType) _
And Not IsNull(Me!cmbItemID) _
And Not IsNull(Me!cmbWkDate))
Me!cmbFind = EnableFind
End Function

Then you can have the AfterUpdate event of all three criteria
controls be this function. To do that, in design view click on
cmbItemType, then SHIFT-click cmbItemID and cmbWkDate in turn. This
will select all three controls. Then on the Events tab of the
property sheet, type =EnableFind(). This will fire the check to see
if the three fields are filled out for the AfterUpdate of each of
the, and when all three are filled out, the FIND button will be
enabled.

2. You probably don't want to set the Recordsource to something that
will return no records. You have two choices for how to accomplish
this:

a. filter each combo box in sucession. In other words, when you
choose a value for cmbItemType, filter cmbItemID and cmbWkDate to
return only those values that match cmbItemType. When you've
chosen the value for the second dropdown lise, filter the dates to
limit to those that are value for the first two criteria.

b. in cmdFind_Click, add a check for the filter criteria. That
would be something like this:

If DCount("MyTable", "*", strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", _
vbExclamation,"Note"
Else
Me.Recordsource = strSQL & strWhere
End If

Depending on the data, I would probably choose the first, as I don't
like to give users the opportunity to put in search criteria that
return nothing (and disappoint the user), unless the cost of
checking ahead is high.

One last consideration:

ItemType and ItemID raise my suspicions about what you're liking
for. I don't know why a user would ever need to know the ItemID of
something -- that ItemID represents some real-world thing, right?
So, in that case, while I might filter for the underlying ItemID, I
would never ask the user to put that in. Instead, I'd give the user
the human-friendly item name from the combo box and have the ItemID
be the bound colum of the combo box (which is what you'd filter on).

 

[Ed from AZ]

David:

Thanks so much for helping me on this!!

A few notes -

You need to present a subset of data based on those three fields.

First, create a form that displays all the data from the returned
records, using "SELECT MyTable.* FROM MyTable" as your Recordsource.

Did that, and put it into DataSheet view.

Then you have a choice:

2. change the form's Recordsource.

That looked good to me, too.

I would tend to do the latter, and make the form's saved
Recordsource return one uneditable record. Here's the SQL to do
that:

  SELECT TOP 1 Null As Field1, Null As Field2, Null As Field3
  FROM MyTable

I didn't do this, because I'm going to call the form from a button on
the first form, and (try to!!) pass the three values to the second
form. So there will not need to be an empty record at the beginning.

  Private Sub cmdFind_Click()
    Dim strSQL As String
    Dim strWhere As String

    strSQL = "SELECT MyTable.* FROM MyTable "
    strWhere = "MyTable.ItemType=" & Me!cmbItemType
    strWhere = strWhere & "MyTable.ItemID=" & Me!cmbItemID
    strWhere = "MyTable.WkDate=#" & Me!cmbWkDate & "#;"
    Me.Recordsource = strSQL & strWhere
  End Sub

I took this and put it into the Form_Load event.

There seemed to be a few minor details in the syntax, but I put it in
with my variables hard-wired for a test and it worked great!!!

Now, a few points:

1. You likely don't want the user to be able to click the FIND
button until all three fields have been filled out.

That won't be a problem because the three fields will already be
filled on the first form.

2. You probably don't want to set the Recordsource to something that
will return no records. You have two choices for how to accomplish
this:

I already have the Dcount code in the first form. Thanks for pointing
this out though - I likely would have not thought of it.

One last consideration:

ItemType and ItemID raise my suspicions

Ah, you'd have to know our data!! Trust me - it's a very important
field and is the ONE thing everyone MUST get right.

Again, my huge bucket of thanks and praise!
Ed

 

[David W. Fenton]

m:

Did that, and put it into DataSheet view.

That's not going to make it possible to do your filtering on the
same form, but I see you're using a separate form to collect the
criteria. I'd tend to do all of it on one form, and if I wanted to
display a datasheet, I'd embed it as a subform in an unbound parent
form with the filter fields in the form's header.

I didn't do this, because I'm going to call the form from a button
on the first form, and (try to!!) pass the three values to the
second form. So there will not need to be an empty record at the
beginning.

In that case, you could use the filter argument of the
DoCmd.OpenForm command.

I took this and put it into the Form_Load event.

There's no need to do it that way. You can filter the form via
DoCmd.OpenForm without needing to write any filtering code behind
your form.

 

[Ed from AZ]

Okay - I took out the code from the frmShowData form's Load event. I
then went to the button on frmDataEntry and set up the DoCmd:

DoCmd.OpenForm "frmShowData", acFormDS, , strWhere, acFormEdit

It works, but throws an error in the strWhere. If I comment out
everything after acFormDS, it works great.

The error is 3075:
Syntax error in query expression ' tblData.ItemType = 'His' and
tblData.ItemID= 'Shirt' and tblData.WkDate = #2/2/2009#;'.

This is Access2007.

Ed

 

[Ed from AZ]

Just got it!!

Syntax error in query expression ' tblData.ItemType = 'His' and
tblData.ItemID= 'Shirt' and tblData.WkDate = #2/2/2009#;'.

I forgot to remove the ; at the end of the line!! Took that out and
it's now a beautiful thing!

David, I owe you much gratitude. Thank you!!

Ed

 

[David W. Fenton]

m:

Just got it!!


I forgot to remove the ; at the end of the line!! Took that out
and it's now a beautiful thing!

David, I owe you much gratitude. Thank you!!

You owe the gratitude to the programmers of Access. This is the
basic functionality that has been built into Access since version 1.
It's how you should be using Access to begin with -- don't write a
line of DAO code until the built-in Access commands don't do what
you need.