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

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

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

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

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 :rolleyes:
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

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

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

Your DAO reference is not set...

in a module window go to the menu:

Tools => References

Check the microsoft DAO

Regards

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??

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

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?

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.

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

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