Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

647 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