Help. User defined type not defined. "Dim myDB as Database"

[Peter McNaughton]

Hi
I am trying to access each record in a table, detect for the presence
of a P O Box and I will then move that information into a new PO Box
field.

But before I could complete the procedure I get a compile error. I
have seen many examples in the access news groups that suggest the
code should work. Why do I get the error "User defined type not
defined?"

Thanks in advance
Peter

Private Sub Postcode()
Dim myDB As Database
Dim myRst As Recordset
Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset("tblPersonal Details")
myRst.MoveFirst
Do Until myRst.EOF
If myRst![Address Line1] = "P O Box" Then
myRst![PO Box] = myRst![Address Line1]
End If
myRst.MoveNext
Loop
End Sub

 

[Dirk Goldgar]

Hi
I am trying to access each record in a table, detect for the presence
of a P O Box and I will then move that information into a new PO Box
field.

But before I could complete the procedure I get a compile error. I
have seen many examples in the access news groups that suggest the
code should work. Why do I get the error "User defined type not
defined?"

Thanks in advance
Peter

Private Sub Postcode()
Dim myDB As Database
Dim myRst As Recordset
Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset("tblPersonal Details")
myRst.MoveFirst
Do Until myRst.EOF
If myRst![Address Line1] = "P O Box" Then
myRst![PO Box] = myRst![Address Line1]
End If
myRst.MoveNext
Loop
End Sub

You need to add a reference to the Microsoft DAO 3.6 Object Library.
You can do this by opening any code module, then clicking Tools ->
References ..., locating the reference in the list of available
references, and putting a check mark in the box next to it. While
you're there, if you don't plan to use the ADO (ActiveX Data Objects)
library, un-check it -- it defines some objects with the same names as
the DAO objects, and they can confuse Access. If, however, you want to
keep both libraries among your references, you can do so safely by
qualifying the declarations of your objects thus:

Dim myDB As DAO.Database
Dim myRst As DAO.Recordset

There is no ADO Database object, but you'd declare an ADO Recordset with

Dim myOtherRst As ADODB.Recordset

 

[Peter McNaughton]

Found the Answer. I didn't know about Microsoft DAO 3.6 Object
Library but now I do. I include the code that now works.

Peter

Private Sub Postcode()
Dim myDB As Database
Dim myRst As DAO.Recordset
Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset("tblPersonal Details")
myRst.MoveFirst
Do Until myRst.EOF
Add1 = myRst![Address Line1]
Add2 = myRst![Address Line2]
Add3 = myRst![Address Line3]
POBox = myRst![PO Box]
If Left(myRst![Address Line1], 7) = "P O Box" Then
myRst.Edit
myRst![PO Box] = myRst![Address Line1]
myRst.Update
ElseIf Left(myRst![Address Line2], 7) = "P O Box" Then
myRst.Edit
myRst![PO Box] = myRst![Address Line2]
myRst.Update
ElseIf Left(myRst![Address Line3], 7) = "P O Box" Then
myRst.Edit
myRst![PO Box] = myRst![Address Line3]
myRst.Update
End If
myRst.MoveNext
Loop
End Sub