NotInList

[surfside1]

I know you all have had questions about the "not in list" code. I've tried to read them all and I've had this work before. The code I copied from a previous db was the following. It worked. Now I have a new db and I get an error that stops in the VBA code "User defined type not defined" and it is on the "Dim dbs As Database". Below is the code I used previously, I modified the various fields to fit my DB. See anything I'm missing in a new DB?

Thanks, surfside1

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

Dim intAnswer As Integer
Dim dbs As Database, rst As Recordset
intAnswer = MsgBox("Add " & NewData & " to the list of AUDITORS?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
' Add AUDITOR stored in NewData argument to the tbl_Auditor table.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_Auditor")
rst.AddNew
' rst!Auditor = NewData
rst!Auditor = UCase(NewData)

rst.Update
Response = acDataErrAdded
' Requery the combo box list.
Else
Response = acDataErrDisplay
' Require the user to select an existing AUDITOR.
End If
rst.Close
End Sub

 

[snyperx3]

Dim dbs As Database, rst As Recordset

needs to be:
Dim dbs As Database
Dim rst As Recordset

You might go as far as to use:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

make sure you have the microsoft dao object library referenced in your references.

-Pete

 

[LRHatBSSS]

Just a guess, but you might be missing the reference to the DAO library.

LRH

L.R. Humberto

If you don't know where you are going to, doesn't matter how fast you are going, you'll never get there.

 

[surfside1]

Ok, I tried the DAO from a similar response from PH to someone. Tell me about the dao object library. If I had copied an existing DB, and now I am creating a new DB on a different machine, is there something I need to do somewhere? Thanks!

 

[snyperx3]

? is that not what i said LRH?

anyway, surfside1, if youre using access on a different machine the reference wont be there. you need to add it.

While viewing the vba go to
Tools > References

Then check next to Microsoft DAO 3.6 Object Library

when you dim the variables be sure to prefix the database related ones with DAO (DAO.Database, DAO.Recordset, DAO.Field, ...) If you dont the application may not know which type of database variable you are using as there is ADODB as well.

-Pete

 

[surfside1]

Thanks, that got me to the line "Set rst = dbs.OpenRecordset("tbl_Auditor")
Is it ok to put the table name in quotes after the OpenRecordset?

 

[snyperx3]

yes

try
Set rst = dbs.OpenRecordset("tbl_Auditor",dbOpenDynaset)

-Pete

 

[PHV]

As already suggested:
Dim rst As [!]DAO.[/!]Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfside1]

Thanks everyone. Besides the DAO's and the object library reference, I have another issue that I'm still confused about, but it works so I'm good to go.

When building a dropdown using the wizard and you tell it to "remember the value for later use", it doesn't put a "control source" in the properties. But if you tell it to "store that value in this field" and that becomes the control source. When in Access would you use that value?

That's what was also messing up my code because I was accessing that value instead of the table field name.

Just curious, thanks, Surfside1