Trouble Defining and Updating Recordset

[Proaccesspro]

I'm trying to update a field in a table with the value of a text box in a report. Here is what I have:

Private Sub Report_Close()

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("OHPA Summary Report Table")

rs.AddNew
rs("Begining Balance") = [Text4]

rs.Update

End Sub


When I run the code, I keep getting a type mismatch on the line where I set rs = currentdb....etc. ANY ideas??? Is there a better way to do this??

[Stewart Ross]

I'm trying to update a field in a table with the value of a text box in a report. Here is what I have:

Private Sub Report_Close()

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("OHPA Summary Report Table")

rs.AddNew
rs("Begining Balance") = [Text4]

rs.Update

End Sub


When I run the code, I keep getting a type mismatch on the line where I set rs = currentdb....etc. ANY ideas??? Is there a better way to do this??

Hi. There is more than one kind of recordset, and unfortunately they have different methods and properties. As it is the Access DAO-type recordset you are using your problems should be resolved if you qualify your DIM as

Dim rs as DAO.Recordset

If the DAO qualifer is not recognised you will need to set a reference to the Microsoft DAO object library via Tools, references in the VB editor.

-Stewart

[Proaccesspro]

Hi. There is more than one kind of recordset, and unfortunately they have different methods and properties. As it is the Access DAO-type recordset you are using your problems should be resolved if you qualify your DIM as

Dim rs as DAO.Recordset

If the DAO qualifer is not recognised you will need to set a reference to the Microsoft DAO object library via Tools, references in the VB editor.

-Stewart

Yep, it dod not like the DOA qualifier. I tried to get to the references option but it is greyed out........

[Stewart Ross]

Yep, it dod not like the DOA qualifier. I tried to get to the references option but it is greyed out........

Perhaps you didn't stop the debugger (by pressing the little rectangular reset button) before trying to set the reference??

[Proaccesspro]

Perhaps you didn't stop the debugger (by pressing the little rectangular reset button) before trying to set the reference??

Yes, you're right...seems to work OK now....One last question, what command can I use to delete all records in a table?

[Stewart Ross]

Yes, you're right...seems to work OK now....One last question, what command can I use to delete all records in a table?

Well, living dangerously you can use

Docmd.RunSQL "DELETE * FROM yourtablename;"

You would need to turn warnings off and on before and after using

Docmd.Setwarnings False (or True)

or a similar method.

-Stewart

[Proaccesspro]

Well, living dangerously you can use

Docmd.RunSQL "DELETE * FROM yourtablename;"

You would need to turn warnings off and on before and after using

Docmd.Setwarnings False (or True)

or a similar method.

-Stewart

It does not like it. Says I have a syntax error.

DoCmd.RunSQL "DELETE * FROM DEHP Summary Report Table;"

[Stewart Ross]

It does not like it. Says I have a syntax error.

DoCmd.RunSQL "DELETE * FROM DEHP Summary Report Table;"

Hi. Where the name of a field or table has spaces in it you must enclose it in square brackets.

DoCmd.RunSQL "DELETE * FROM [DEHP Summary Report Table];"

-Stewart

[Proaccesspro]

Hi. Where the name of a field or table has spaces in it you must enclose it in square brackets.

DoCmd.RunSQL "DELETE * FROM [DEHP Summary Report Table];"

-Stewart

B-I-N-G-O!!! Thanks!