on not in list failing.... 1

[CMooreJr]

I have a combo box in a form that allows the users to put in new info if it isn't in the box. I have done this right many times with no problem, but now I get an error,...can you tell me why?? Here is the code:

the cbobox is "Heardfromus" and the lookup table is "tblHeardfromus"...the cbobox has it's "limit to list" set to YES....it's calling an error on the Dim DB as Database....

start of code---------------

Private Sub Heardfromus_NotInList(NewData As String, Response As Integer)

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordSet("tblheardfromus")
rst.AddNew
rst!Heardfromus = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End Sub

 

[mgolla]

Did you make sure your referencing Microsoft DAO 3.5/3.6 Object Library?

If you aren't then that is why you are erring out on the Dim DB

HTH
Mike

 

[RoyVidar]

Hi!

It's a bit easier if you also state the errormsg. Guessing it has to do with references, try declaring explicitly:

[tt]dim db as dao.database
dim rst as dao.recordset[/tt]

AND - Ensure you have a reference to Microsoft DAO 3.# Object Library (in any module Tools | References)

Roy-Vidar

 

[CMooreJr]

the error is
"Compile error - user-Defined type not defined"

it must be the reference...inside of any module, the "references" is greyed out...how would I go about adding this in? Not sure what happened...it worked great before....

 

[RoyVidar]

Stop the macro with the "Reset" button (or reset at the Run menu), and it should normally be available again.

(or close the db, and open it again whilst pressing shift, enter any module)

Roy-Vidar

 

[CMooreJr]

OK guys...that worked great...but now there is another one....

on the line:

Set rst = db.OpenRecordSet("tblheardfromus")

I get the error:

"Run time error '91'.Object variable or with block variable not set."

what's going on here??? Thanks for all your help!

 

[RoyVidar]

Indicates that the db variable isn't instantiatet.

Is your current code something like this?

[tt]Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordSet("tblheardfromus")
rst.AddNew
rst!Heardfromus = NewData
rst.Update
Response = acDataErrAdded
rst.Close[/tt]

Roy-Vidar

 

[CMooreJr]

that did it...thanks Roy...it was the rst as dao...I forgot to reset that...have a star and thanks for all your help!