Access - Recordset Select record by Criteria

Posted on 2013-02-06
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 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 47

Accepted Solution

Dale Fye (Access MVP) earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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