I found some coding in another vers

[sdimaggio]

I found some coding in another version of Access. I coverted it to ms2000. when I imported the files and try to run the code, I get an error message which is:

compiled error:
user defined type not defined.

in the following code "dbs As Database" is highlighted.


Private Sub cboTable_AfterUpdate()
On Error GoTo Err_cboTable_AfterUpdate

Dim dbs As Database, tdf As TableDef
Dim fld As Field, rst As Recordset
Dim tbl As String

When I coverted the file and run the code from the converted file everything is fine. Why does it not work in my database?

I have experienced this before and could never figure it out.

I need a little help.

thanks

steve

 

[PaulBricker]

First make sure you have a reference to Microsoft DAO 3.6 Object Library selected in References. Then change the following lines.
Dim dbs as DAO.Database
Dim rst as DAO.Recordset

You may need to include the TableDefs also. A2000 provides 2 libraries to run code from. The DAO 3.6 library and the ADO 2.1 library (along with a host of others). You have to tell Access which library you want to use by adding the appropriate prefix.

Paul

 

[sdimaggio]

I'm a little new at this.

where do I select Microsoft DAO 3.6 Object in Library selected in References?

steve

 

[PaulBricker]

Open any module, on the menu bar go to Tools....References. Scroll down till you see Microsoft DAO. 3.6 Object Library. Put a check mark in the little box. That will enable that library.

Paul

 

[sdimaggio]

Thanks, I did exactly what you said. I now am getting a TYPE MISMATCH error: on the Private Sub cboTable_AfterUpdate()event

Where am I going wrong?

thanks.


Option Compare Database
Option Explicit


Private Sub cboFieldName1_AfterUpdate()
On Error GoTo Err_cboFieldName1_AfterUpdate

Dim strSQL As String
strSQL = "SELECT DISTINCT " & cboFieldName1 & " FROM " & cboTable & ";"

'MsgBox strSQL
Me!cboDescriptor1.RowSource = strSQL
Me!cboDescriptor1.Requery

Exit_cboFieldName1_AfterUpdate:
Exit Sub

Err_cboFieldName1_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboFieldName1_AfterUpdate
End Sub

Private Sub cboFieldName2_AfterUpdate()
On Error GoTo Err_cboFieldName2_AfterUpdate

Dim strSQL As String
strSQL = "SELECT DISTINCT " & cboFieldName2 & " FROM " & cboTable & ";"
'MsgBox strSQL
Me!cboDescriptor2.RowSource = strSQL
Me!cboDescriptor2.Requery

Exit_cboFieldName2_AfterUpdate:
Exit Sub

Err_cboFieldName2_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboFieldName2_AfterUpdate

End Sub

Private Sub cboFieldName3_AfterUpdate()
On Error GoTo Err_cboFieldName3_AfterUpdate

Dim strSQL As String
strSQL = "SELECT DISTINCT " & cboFieldName3 & " FROM " & cboTable & ";"
'MsgBox strSQL
Me!cboDescriptor3.RowSource = strSQL
Me!cboDescriptor3.Requery

Exit_cboFieldName3_AfterUpdate:
Exit Sub

Err_cboFieldName3_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboFieldName3_AfterUpdate

End Sub

Private Sub cboLogical_GotFocus()
On Error GoTo Err_cboLogical_GotFocus

Me!cboSecondOperator.Enabled = True
Me!cboDescriptor2.Enabled = True
Me!cboFieldName2.Enabled = True
Me!Check38.Enabled = True

Exit_cboLogical_GotFocus:
Exit Sub

Err_cboLogical_GotFocus:
MsgBox Err.Description
Resume Exit_cboLogical_GotFocus
End Sub

Private Sub cboLogical_LostFocus()
On Error GoTo Err_cboLogical_LostFocus

If IsNull(Me!cboLogical) Then
Me!cboSecondOperator.Enabled = False
Me!cboDescriptor2.Enabled = False
Me!cboFieldName2.Enabled = False
Me!Check38.Enabled = False
End If

Exit_cboLogical_LostFocus:
Exit Sub

Err_cboLogical_LostFocus:
MsgBox Err.Description
Resume Exit_cboLogical_LostFocus
End Sub

Private Sub cboLogical2_GotFocus()
On Error GoTo Err_cboLogical2_GotFocus

If Me!cboSecondOperator.Enabled = True Then
Me!cboThirdOperator.Enabled = True
Me!cboDescriptor3.Enabled = True
Me!cboFieldName3.Enabled = True
Me!Check40.Enabled = True
Else
Me!cboThirdOperator.Enabled = False
Me!cboDescriptor3.Enabled = False
Me!cboFieldName3.Enabled = False
Me!Check40.Enabled = False
End If

Exit_cboLogical2_GotFocus:
Exit Sub

Err_cboLogical2_GotFocus:
MsgBox Err.Description
Resume Exit_cboLogical2_GotFocus
End Sub

Private Sub cboLogical2_LostFocus()
If IsNull(Me!cboLogical2) Then
Me!cboThirdOperator.Enabled = False
Me!cboDescriptor3.Enabled = False
Me!cboFieldName3.Enabled = False
End If

End Sub

Private Sub cboTable_AfterUpdate()
On Error GoTo Err_cboTable_AfterUpdate

Dim dbs As DAO.Database, tdf As TableDef
Dim fld As Field, rst As DAO.Recordset
Dim tbl As String

tbl = Forms!CreateQuery!cboTable
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs(tbl)
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * from TableFields&quot
DoCmd.SetWarnings True
Set rst = dbs.OpenRecordset("TableFields", dbOpenDynaset)
' Enumerate all fields in Fields collection of TableDef object.
For Each fld In tdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
rst.AddNew
rst!FieldName = fld.Name
rst!FieldType = fld.Type
rst.Update
End If
Next fld
Set dbs = Nothing
cboFieldName1.Requery
cboFieldName2.Requery
cboFieldName3.Requery
Reset

Exit_cboTable_AfterUpdate:
Exit Sub

Err_cboTable_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboTable_AfterUpdate
End Sub

Private Sub Check36_Click()
On Error GoTo Err_Check36_Click

If cboFirstOperator.Enabled = True And Left(cboDescriptor1, 1) <> &quot;*&quot; Then
If cboFirstOperator Like &quot;*like*&quot; And Check36 Then
cboDescriptor1 = &quot;*&quot; & cboDescriptor1 & &quot;*&quot;
End If
Else
If Left(cboDescriptor1, 1) = &quot;*&quot; And Not Check36 Then
cboDescriptor1 = Mid(cboDescriptor1, 2, Len(cboDescriptor1) - 2)
End If
End If

Exit_Check36_Click:
Exit Sub

Err_Check36_Click:
MsgBox Err.Description
Resume Exit_Check36_Click
End Sub

Private Sub Check38_Click()
On Error GoTo Err_Check38_Click

If cboFirstOperator.Enabled = True And Left(cboDescriptor2, 1) <> &quot;*&quot; Then
If cboSecondOperator Like &quot;*like*&quot; And Check38 Then
cboDescriptor2 = &quot;*&quot; & cboDescriptor2 & &quot;*&quot;
End If
Else
If Left(cboDescriptor2, 1) = &quot;*&quot; And Not Check38 Then
cboDescriptor2 = Mid(cboDescriptor2, 2, Len(cboDescriptor2) - 2)
End If
End If

Exit_Check38_Click:
Exit Sub

Err_Check38_Click:
MsgBox Err.Description
Resume Exit_Check38_Click
End Sub

Private Sub Check40_Click()

On Error GoTo Err_Check40_Click
If cboFirstOperator.Enabled = True And Left(cboDescriptor3, 1) <> &quot;*&quot; Then
If cboThirdOperator Like &quot;*like*&quot; And Check40 Then
cboDescriptor3 = &quot;*&quot; & cboDescriptor3 & &quot;*&quot;
End If
Else
If Left(cboDescriptor3, 1) = &quot;*&quot; And Not Check40 Then
cboDescriptor3 = Mid(cboDescriptor3, 2, Len(cboDescriptor3) - 2)
End If
End If


Exit_Check40_Click:
Exit Sub

Err_Check40_Click:
MsgBox Err.Description
Resume Exit_Check40_Click
End Sub

Private Sub cmdReset_Click()
Reset
Me!cboTable = &quot;&quot;
End Sub

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

CreateQuery
'*** open the query
DoCmd.OpenQuery &quot;qryMyQuery&quot;, acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:

MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End Sub

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub Reset()
On Error GoTo Err_Reset

Me!cboFirstOperator = &quot;Like&quot;
Me!cboSecondOperator = &quot;Like&quot;
Me!cboThirdOperator = &quot;Like&quot;
Me!cboDescriptor1 = &quot;&quot;
Me!cboDescriptor2 = &quot;&quot;
Me!cboDescriptor3 = &quot;&quot;
Me!cboLogical = &quot;&quot;
Me!cboLogical2 = &quot;&quot;
Me!Check36 = False
Me!Check38 = False
Me!Check40 = False

Me!cboFieldName1 = &quot;&quot;
Me!cboFieldName2 = &quot;&quot;
Me!cboFieldName3 = &quot;&quot;

Me!cboSecondOperator.Enabled = False
Me!cboDescriptor2.Enabled = False
Me!cboThirdOperator.Enabled = False
Me!cboDescriptor3.Enabled = False
Me!cboFieldName2.Enabled = False
Me!cboFieldName3.Enabled = False
Me!Check38.Enabled = False
Me!Check40.Enabled = False

Exit_Reset:
Exit Sub

Err_Reset:
MsgBox Err.Description
Resume Exit_Reset
End Sub



Sub CreateQuery()
On Error GoTo Err_CreateQuery

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = &quot;SELECT * FROM &quot; & Me!cboTable & &quot; WHERE &quot;

If cboFieldName1.Column(1) >= 1 And cboFieldName1.Column(1) <= 7 Then
strSQL = strSQL & Me!cboFieldName1 & &quot; &quot; & Me![cboFirstOperator] & &quot; &quot; & Me![cboDescriptor1] & &quot; &quot;
ElseIf cboFieldName1.Column(1) = 8 Then
strSQL = strSQL & Me!cboFieldName1 & &quot; &quot; & Me![cboFirstOperator] & &quot; #&quot; & Me![cboDescriptor1] & &quot;# &quot;
ElseIf cboFieldName1.Column(1) = 10 Then
strSQL = strSQL & Me!cboFieldName1 & &quot; &quot; & Me![cboFirstOperator] & &quot; &quot;&quot;&quot; & Me![cboDescriptor1] & &quot;&quot;&quot; &quot;
End If

If Me!cboSecondOperator.Enabled = True Then
strSQL = strSQL & Me![cboLogical]
If cboFieldName2.Column(1) >= 1 And cboFieldName2.Column(1) <= 7 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName2 & &quot; &quot; & Me![cboSecondOperator] & &quot; &quot; & Me![cboDescriptor2] & &quot; &quot;
ElseIf cboFieldName2.Column(1) = 8 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName2 & &quot; &quot; & Me![cboSecondOperator] & &quot; #&quot; & Me![cboDescriptor2] & &quot;# &quot;
ElseIf cboFieldName2.Column(1) = 10 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName2 & &quot; &quot; & Me![cboSecondOperator] & &quot; &quot;&quot;&quot; & Me![cboDescriptor2] & &quot;&quot;&quot; &quot;
End If
End If

If Me!cboThirdOperator.Enabled = True Then
strSQL = strSQL & Me![cboLogical2]
If cboFieldName3.Column(1) >= 1 And cboFieldName3.Column(1) <= 7 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName3 & &quot; &quot; & Me![cboThirdOperator] & &quot; &quot; & Me![cboDescriptor3] & &quot; &quot;
ElseIf cboFieldName3.Column(1) = 8 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName3 & &quot; &quot; & Me![cboThirdOperator] & &quot; #&quot; & Me![cboDescriptor3] & &quot;# &quot;
ElseIf cboFieldName3.Column(1) = 10 Then
strSQL = strSQL & &quot; &quot; & Me!cboFieldName3 & &quot; &quot; & Me![cboThirdOperator] & &quot; &quot;&quot;&quot; & Me![cboDescriptor3] & &quot;&quot;&quot; &quot;
End If
End If


'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete &quot;qryMyQuery&quot;
Set qdf = db.CreateQueryDef(&quot;qryMyQuery&quot;, strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If

End Sub
Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = &quot;Explanation&quot;
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

 

[sdimaggio]

Paul,

I got it.

thanks a million.

steve

 

[PaulBricker]

That's great.

Paul