Solved

Access - Recordset Select record by Criteria

Posted on 2013-02-06
5
511 Views
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.

1.

ID is of course an autonumber field.

2.

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

3.

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)
rst2.AddNew
rst2![Fa1]=lngResult
rst2.update
rst2.Close

rst1.close
SET rst2 = Nothing
SET rst1 = Nothing

End Sub

HOWEVER, How do you cycle through all the records execute the computation and then save??????
0
Comment
Question by:wlwebb
  • 3
  • 2
5 Comments
 
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.
0
 

Author Comment

by:wlwebb
ID: 38862220
Fyed

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......
0
 
LVL 47

Accepted Solution

by:
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
0
 

Author Closing Comment

by:wlwebb
ID: 38864261
Fyed
Thank you........  That will get me going.... I'm sure I'll be back with more questions.......  hopefully with decreasingly stupid questions ;-)
0
 
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now