User Defined

[Dan @BCBS]

The following code check a table for an existing record and display it or if
none exists then display the Me.[PVNO]..

But I get a Compile Error: "User-defined type not defined" at "Dim
dbcurrent As Database"..

Am I missing a Reference in the Library>?? or do I need to add something
else???






Private Sub cmdProv_Click()
On Error GoTo Err_cmdProv_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

If IsNull([PVNO]) Then
MsgBox "Please enter the Provider # before proceeding."
Exit Sub
Else
'Check to see if a record already exists
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [TBLPROVIDER]
where [PROVNO] = " & "'" & Me.[PVNO] & "'" & ";")
stDocName = "f_PROV"

If rstemp.RecordCount > 0 Then 'Open Record
stLinkCriteria = "[PROVNO]=" & "'" & Me.[PVNO] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Create a new record
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!f_PROV.DataEntry = True
End If
Forms!f_ProvDetails.ProvNo = Me.PVNO
End If

Exit_cmdProv_Click:
Exit Sub

Err_cmdProv_Click:
MsgBox Err.Description
Resume Exit_cmdProv_Click

End Sub

 

[Dennis]

Am I missing a Reference in the Library>??

Yes, you are.

 

[fredg]

The following code check a table for an existing record and display it or if
none exists then display the Me.[PVNO]..

But I get a Compile Error: "User-defined type not defined" at "Dim
dbcurrent As Database"..

Am I missing a Reference in the Library>?? or do I need to add something
else???

Private Sub cmdProv_Click()
On Error GoTo Err_cmdProv_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

If IsNull([PVNO]) Then
MsgBox "Please enter the Provider # before proceeding."
Exit Sub
Else
'Check to see if a record already exists
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [TBLPROVIDER]
where [PROVNO] = " & "'" & Me.[PVNO] & "'" & ";")
stDocName = "f_PROV"

If rstemp.RecordCount > 0 Then 'Open Record
stLinkCriteria = "[PROVNO]=" & "'" & Me.[PVNO] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Create a new record
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!f_PROV.DataEntry = True
End If
Forms!f_ProvDetails.ProvNo = Me.PVNO
End If

Exit_cmdProv_Click:
Exit Sub

Err_cmdProv_Click:
MsgBox Err.Description
Resume Exit_cmdProv_Click

End Sub

Your computer probably has the ADO library set.
Make sure you first set a reference to the latest DAO library in your
computer, i.e. DAO 3.6.

Then use

Dim dbcurrent As DAO.Database
Dim rstemp As DAO.Recordset
etc....

 

[Dan @BCBS]

Perfect
Thank you

The following code check a table for an existing record and display it or if
none exists then display the Me.[PVNO]..

But I get a Compile Error: "User-defined type not defined" at "Dim
dbcurrent As Database"..

Am I missing a Reference in the Library>?? or do I need to add something
else???

Private Sub cmdProv_Click()
On Error GoTo Err_cmdProv_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbcurrent As Database
Dim rstemp As Recordset

If IsNull([PVNO]) Then
MsgBox "Please enter the Provider # before proceeding."
Exit Sub
Else
'Check to see if a record already exists
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from [TBLPROVIDER]
where [PROVNO] = " & "'" & Me.[PVNO] & "'" & ";")
stDocName = "f_PROV"

If rstemp.RecordCount > 0 Then 'Open Record
stLinkCriteria = "[PROVNO]=" & "'" & Me.[PVNO] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Create a new record
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!f_PROV.DataEntry = True
End If
Forms!f_ProvDetails.ProvNo = Me.PVNO
End If

Exit_cmdProv_Click:
Exit Sub

Err_cmdProv_Click:
MsgBox Err.Description
Resume Exit_cmdProv_Click

End Sub

Your computer probably has the ADO library set.
Make sure you first set a reference to the latest DAO library in your
computer, i.e. DAO 3.6.

Then use

Dim dbcurrent As DAO.Database
Dim rstemp As DAO.Recordset
etc....