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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 48

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 48

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 48

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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