how to read multiple records using a loop?

[catcoota]

Hi how are u all?
I have a subform which has multiple records, i wanna write code which reads the records one by one, in other wards, i need a loop & an array to read it, but i donno how to read (a record) using an array in Access.

Anyhelp is highly appreciated

 

[namliam]

What is your ultimate goal? Cause your not beeing verry precise

Dim rs as DAO.recordset
set rs = me.recordsetclone
do while NOT rs.eof
rs.movenext
loop
set rs = nothing

That will loop through your recordsset but dont know how or what you want to do...

Regards

 

[catcoota]

Nice information i didn't know how to read at all & now i got it. & i would also to know how to delete a record which has a specific value, for instance, I wanna delete a record where the primary key (ProductID = 10) , and what is the command which adds a new record. Thanks again 4 the help

 

[WayneRyan]

cat,

Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

If rst.EOF and rst.BOF Then
   MsgBox("There's nothing in the table.")
   Exit Sub
End If

While Not rst.EOF and Not rst.BOF
   If rst!ProductID = 10 Then
      rst.Edit
      rst.Delete
      rst.Update
   ElseIf rst!ProductID = 20 Then
      rst.AddNew
      rst.ProductID = 99
      rst.OtherField = "abcde"
      rst.Update
   End If
   rst.MoveNext
   Wend

Wayne

 

[catcoota]

Hey thanks very much WayneRyan

I am just a newbie in Access VBA, but I am enjoying it
I'll study ur codeand If I faced a problem, i might come back to ask u
By the way, do u recommend me to visit any sites that gives me some detailed lesson about VBA loops and other coding stuff in Access?

Thanks again

 

[namliam]

If you 'only' want to delete a group of specific records/ or just 1 record from a table then a Delete query is much more efficient.

Regards

 

[catcoota]

Thanks namiliam 4 the note

I have used WayneRyan way, but I am facing a problem

When I write Dim dbs as Database , the (databae) word does not get coloured so that it doesn't look like to be recognized by the program! in addition to that when I run the program the debugger runs and tells me that (dbs as database) user-defined type not defined !

Do I need to isntall anything to define it?

And by the way, the same way when I write DAO. , I think the programm is supposed to autmatically show me a list of drop down menu with some functions but I don't get it as if the database does not recognize this DAO thing.

Any help will be highly apppreciated

 

[namliam]

Your DAO reference is not set...

in a module window go to the menu:

Tools => References

Check the microsoft DAO

Regards

 

[catcoota]

namliam


Thanks for the help, I added the DAO & it recognizes it now

But, um facing another problem

I made a test to see if it works or not, I made a form, which has a button, when I click on the button the following code run, so I see the record set where OrderID = 3

--------------------------

Private Sub Command16_Click()
Dim sql As String
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb() ' I also tried CurrentDB without ()


sql = "select * from ProductOrder where OrderID = 3"
Set rst = dbs.OpenRecordset(sql)

If rst.EOF And rst.BOF Then
MsgBox ("There is nothing in the table")
Exit Sub
End If


-----------------------------

However, when I run the form and click on the button I get the following debugging error (run time error '13' : type mismatch)

& when I debug it , it points to this line:

Set rst = dbs.OpenRecordset(sql)

Did I do something wrong??

 

[WayneRyan]

cat,

sql = "select * from ProductOrder where OrderID = 3"

Do you have a table called ProductOrder and Is the OrderID
field really numeric?

Type 13 error is when you mix datatypes.

Wayne

 

[catcoota]

Hello

Actually ProductOrder is a query, I used this query coz my form is based on 2 tables.

By the way, OrderID is AutoNumber

Any suggestions?

 

[Pat Hartman]

You probably wouldn't need any code if you defined the table relationships properly and specified cascade delete. Then when you delete a "parent" record, the "child" records are automatically deleted.

 

[namliam]

Dim dbs As Database
Dim rst As Recordset

You are not declaring properly.... You also have ADO which knows Database

Try:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Regards

 

[catcoota]

Pat Hartman

Hi, I really don't want to delete anything, I am doing all of this just to test how it works, and how to run sql code, coz I am a newbie in VBA and really want to learn it but I can't get it to work properly, BTW: the relationship & cascade delete features are set correctly


namliam

Thanks, I wrote ur code but I still get the same error...is it because I am using a query instead of a table?? and how can tell the program that I want to run a query & not a table?

Thanks