Access - Recordset Select record by Criteria

Posted on 2013-02-06
Medium Priority
Last Modified: 2013-02-07
Hello all

OK, this is a followup to some questions earlier today and late last night to my tabbed form (which each Tab has SubForm Control and some of those Subforms have multiple subform controls themselves).  The problem I had was that the Subforms weren't updating fast enough before a computation field on the Single View form was needed via code to be used on another form.  It was producing an error.  When I stepped through the entire code it works but if I let it just run it errors.  Mbiz gave me the DoEvents and loop possibility to cure that.  However Fyed suggested relooking at using all the subforms.  Thus this is my attempt (again.)

Now then, I am going to give a simple db of 6 fields.


ID is of course an autonumber field.


FK1 & FK2 will be foreign keys to other table id's  that will be used as criteria to select certain records


F1, F2 and F3 are numeric fields
Sample data (a {space + * + space} between each so it can be copied to Excel)
ID       *       FK1       *       FK2       *       F1       *       F2       *       F3
1       *       1       *       1       *       100       *       3       *       4.5
2       *       1       *       3       *       200       *       10       *       2.5
3       *       3       *       3       *       300       *       6       *       3.5
4       *       3       *       1       *       400       *       9       *       6.5
5       *       2       *       2       *       500       *       21       *       6.5
6       *       2       *       2       *       600       *       15       *       1.5
7       *       2       *       1       *       100       *       15       *       1.5
8       *       1       *       1       *       150       *       21       *       4
9       *       4       *       3       *       250       *       22       *       3
10       *       5       *       3       *       350       *       17       *       3
11       *       4       *       3       *       400       *       19       *       2.75

Now then If I wanted to select this table and then filter my selected records to Fk=1 & FK2 = 3 then use those records in a computation (say int((F1*F2)/F3)*50 and take that result to a different table to field [Fa1] how would I do that???????????

My assumption would be:

Private Sub x()
Dim rst1 as Recordset, rst2 as Recordset
Dim lngResult as long

SET rst1=CurrentDb.OpenRecordset(SELECT * FROM dta WHERE [FK1] = 1 AND [FK2] = 3)

lngResult = int((F1*F2)/F3)*50

SET rst2 =CurrentDB.OpenRecordset(SELECT * FROM dta2)

SET rst2 = Nothing
SET rst1 = Nothing

End Sub

HOWEVER, How do you cycle through all the records execute the computation and then save??????
Question by:wlwebb
  • 3
  • 2
LVL 50

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 38862204
My first thought in response to " and take that result to a different table to field [Fa1] " is why do you feel the need to take that result and store it in another table.  If you can compute it at any time, why store it (there are situations where this is a valid technique, I'm just trying to determine if this is one of them).

But assuming that you needed to append that result to your table, I would do it with a query like:

INSERT INTO dta2 (Fa1)
SELECT IIF(ISNULL([F3]), NULL, INT(NZ(F1, 0) * NZ([F2], 0)/ [F3]) * 50)
FROM dta
WHERE [FK1] = 1 AND [FK2] = 3

HOWEVER, How do you cycle through all the records execute the computation and then save??????
You could drop the WHERE clause from the above query, but you have multiple records where the FK1 and FK2 values are the same, so I'm not sure what you would do with those values, or if you need to store the FK1 and FK2 values in your dta2 table or not.

Author Comment

ID: 38862220

Thanks for Answering....

Ok I get the INSERT INTO......  Am I correct in needing to SET the rst2 for that to work or is it needed?

As far as the cycling....  Just trying to understand how it should be done fundementally.  The computed number in my example actually is part of a 3 step computation that does need to be stored.  So, I will end up saving the FK1 and FK2 and the result into this other table......
LVL 50

Accepted Solution

Dale Fye earned 2000 total points
ID: 38862277
no, you don't need to set the recordset.  Use the database.Execute method, similar to:

strSQL = "INSERT INTO dta2 (FK1, FK2, Fa1) " _
            & "SELECT IIF(ISNULL([F3]), NULL, INT(NZ(F1, 0) * NZ([F2], 0)/ [F3]) * 50) " _
            & "FROM dta"
currentdb.execute strsql, dbFailOnError

Author Closing Comment

ID: 38864261
Thank you........  That will get me going.... I'm sure I'll be back with more questions.......  hopefully with decreasingly stupid questions ;-)
LVL 50

Expert Comment

by:Dale Fye
ID: 38864356
Glad I could help.

We all have to start somewhere, and most of the experts you find here will tell you that they learned through trial and error, and forums similar to this.  

There used to be several good Access specific magazines and periodicals too, but most of those have gone the way of the dinosaur.  You might also want to check out the articles section of EE, this is where many of the experts get a chance to address some of their pet issues and you can learn a lot from them.  To do this, just create a new search, and just check the Articles checkbox, then indicate the Access topic area.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Implementing simple internal controls in the Microsoft Access application.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question