473,288 Members | 2,725 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Looping in Module in Access Help

Hi-
I'm attempting to write a module that uses and updates info from two tables and does the following:

Opens the recordset of a table called "tblstoreinv"

If the Needed Field in the tblstoreinv table is null and is less than the DCOH field from the tbldcinv then:
The Shipped Field in the tblstoreinv table equals the Needed Field and the DCOH field from tbldcinv = DCOH - Shipped
Else If Needed from tblstoreinv is > DCOH from tbldcinv, then Shipped = 0.

The module would loop for every record.

Below is the code that I am using:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Inventory()
  3.  
  4. Set Rs = db.OpenRecordset(tblstoreinv)
  5. db.OpenRecordset (tbldcinv)
  6. Rs.MoveLast
  7. iRecCount = Rs.RecordCount
  8. Rs.MoveFirst
  9. For x = 1 To iRecCount
  10. With Rs
  11. If .Needed Is Null And .Needed < [tbldcinv]![DCOH] Then
  12. .Needed = .Shipped
  13. [tbldcinv]![DCOH] = [tbldcinv]![DCOH] - .Shipped
  14.  
  15.  
  16. ElseIf .Needed Is Null And .Needed > [tbldcinv]![DCOH] Then
  17. .Shipped = 0
  18.  
  19. End If
  20. .MoveNext 'Next record and loop
  21. End With
  22.  
  23. Next x
  24. End Sub
  25.  
  26.  
When I get to this line (Set Rs = db.OpenRecordset(tblstoreinv)), it says "Object Required." Any idea how to fix this and make it run correctly?

Thanks for any help you can give...
Oct 13 '06 #1
3 2393
MMcCarthy
14,534 Expert Mod 8TB
Sorry if this is a double post but the previous post I sent doesn't seem to be showing. To help you I need to know the relationship between the two tables. What is the field in common?
Oct 14 '06 #2
Sorry if this is a double post but the previous post I sent doesn't seem to be showing. To help you I need to know the relationship between the two tables. What is the field in common?
The field in common is a text field called ISBN.
Oct 16 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
I had problems following some of your logic but I think this is what you are trying to do. If needed is null then it can't be less than DCOH so I changed And to Or. If needed is null is checked for the first if statement you can't check it again for the second if statement.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Inventory()
  3. Dim db as Database
  4. Dim rs1 as Recordset
  5. Dim rs2 as Recordset
  6.  
  7.    Set db = CurrentDb
  8.    Set rs1 = db.OpenRecordset(tblstoreinv)
  9.    Set rs2 = db.OpenRecordset (tbldcinv)
  10.  
  11.    rs1.MoveFirst
  12.    Do until rs1.EOF
  13.       rs2.MoveFirst
  14.       Do unitl rs2.EOF
  15.          If rs1!ISBN = rs2!ISBN Then
  16.             If IsNull(rs1!Needed) Or rs1!Needed < rs2!DCOH Then
  17.                rs1.Needed = rs1.Shipped
  18.                rs2!DCOH =rs2!DCOH - rs1.Shipped
  19.             ElseIf rs1!Needed  > rs2!DCOH Then
  20.                rs1!Shipped = 0
  21.             End If
  22.          End If
  23.          rs2.MoveNext 'Next record and loop
  24.       Loop
  25.       rs1.MoveNext 'Next record and loop
  26.    Loop
  27.  
  28.    rs1.close
  29.    rs2.close
  30.    Set rs1 = Nothing
  31.    Set rs2 = Nothing
  32.    Set db = Nothing
  33.  
  34. End Sub
  35.  
  36.  
Oct 16 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Karen | last post by:
Most my experience has been with MySQL, so I write queries in terms of SQL statements. I am using access right now as it is on the machine I have. I am using the SQL window as I couldn't figure out...
1
by: Nicole | last post by:
Hello! I hope there is someone out there who can shed some light on this for me. I have a module that is supposed to look at an access table, pull out each bid record, link to another table to...
5
by: Bruce Lawrence | last post by:
I'm running Access 97 and my modules are looping if someone puts an invalid value in. The setup: 3 macros - get_clock_num, verify_clocknum, append_to_history 3 functions. each in their own...
1
by: tonyaccess | last post by:
Hi, I hope you may be able to help a coding novice. I have a fairly simple db which extracts data to a report via a query, my problem is the report is three pages per record set and a total of...
7
by: Ken | last post by:
Hi All - I have a filtered GridView. This GridView has a check box in the first column. This check box is used to identify specific rows for delete operations. On the button click event I...
6
by: Luke - eat.lemons | last post by:
Hi, Im pretty new to asp so all light on this question would be great. Basically i need to test to see what value is set (where to retrieve the data from) so ive done it like this: If...
6
by: JonathanOrlev | last post by:
Hello everyone, I have a newbe question: In Access (2003) VBA, what is the difference between a Module and a Class Module in the VBA development environment? If I remember correctly, new...
2
by: pob | last post by:
Whats the difference between using a control or a listbox when looping thru a listbox. In example 1 it dims a listbox and an example 2 it dims a control. Please explain. Thanks in advance ...
3
by: DWolff | last post by:
My application is to re-assign leads to different groups of salespeople by sequentially assigning them to each salesperson. I've got an Access 2000 front end to an MS-SQL database. Currently, I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.