By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,257 Members | 2,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,257 IT Pros & Developers. It's quick & easy.

Trouble Defining and Updating Recordset

100+
P: 132
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??
Mar 5 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 5 '08 #2

100+
P: 132
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
Expand|Select|Wrap|Line Numbers
  1. 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........
Mar 5 '08 #3

Expert Mod 2.5K+
P: 2,545
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??
Mar 5 '08 #4

100+
P: 132
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?
Mar 5 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. Docmd.RunSQL "DELETE * FROM yourtablename;"
You would need to turn warnings off and on before and after using
Expand|Select|Wrap|Line Numbers
  1. Docmd.Setwarnings False (or True)
or a similar method.

-Stewart
Mar 6 '08 #6

100+
P: 132
Well, living dangerously you can use
Expand|Select|Wrap|Line Numbers
  1. Docmd.RunSQL "DELETE * FROM yourtablename;"
You would need to turn warnings off and on before and after using
Expand|Select|Wrap|Line Numbers
  1. 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;"
Mar 6 '08 #7

Expert Mod 2.5K+
P: 2,545
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "DELETE * FROM [DEHP Summary Report Table];"
-Stewart
Mar 6 '08 #8

100+
P: 132
Hi. Where the name of a field or table has spaces in it you must enclose it in square brackets.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "DELETE * FROM [DEHP Summary Report Table];"
-Stewart
B-I-N-G-O!!! Thanks!
Mar 6 '08 #9

Post your reply

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