Type Mismatch

[Dan @BCBS]

I am going blind trying to find the mismatch in this code. Could someone
please help..


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

stDocName = "f_Prov"

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

 

[OfficeDev18 via AccessMonster.com]

Dan,

I see two possibilities.

1 - Are PV_NUMBER and PVNO both string type? If PVNO is numeric, SQL will
protest that you're enclosing it in quotes

2 - The way your code is written, PV_NUMBER is obviously a control on the
form. If that's the case, the last line of your code

Forms!f_Prov.PV_Number = Me.PVNO

should read

Forms!f_Prov!PV_Number = Me.PVNO (with a "bang" between the form name and the
control name). VBA would protest that you're treating a control name as a
value.

However, I see another logic error, unless I'm missing something.

In the code

Else 'Create a new record
DoCmd.OpenForm stDocName, , , stLinkCriteria

You're trying to add a record, not read an existing one. You don't need the
stLinkCriteria. All you need is the line

DoCmd.OpenForm stDocName

followed by

DoCmd.GoToRecord stDocName, acNewRec

to add a new record.

Hope this helps.

Sam

I am going blind trying to find the mismatch in this code. Could someone
please help..

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

stDocName = "f_Prov"

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

 

[Dan @BCBS]

I apologies for posting this to MSN and ACCESS MONSTER - I'm in crunch time
for a database - won't happen again.

Douglas' suggestion was to change my DIM to Dim rstemp As DAO.Recordset...
Which worked great...

Thanks

Dan,

I see two possibilities.

1 - Are PV_NUMBER and PVNO both string type? If PVNO is numeric, SQL will
protest that you're enclosing it in quotes

2 - The way your code is written, PV_NUMBER is obviously a control on the
form. If that's the case, the last line of your code

Forms!f_Prov.PV_Number = Me.PVNO

should read

Forms!f_Prov!PV_Number = Me.PVNO (with a "bang" between the form name and the
control name). VBA would protest that you're treating a control name as a
value.

However, I see another logic error, unless I'm missing something.

In the code

Else 'Create a new record
DoCmd.OpenForm stDocName, , , stLinkCriteria

You're trying to add a record, not read an existing one. You don't need the
stLinkCriteria. All you need is the line

DoCmd.OpenForm stDocName

followed by

DoCmd.GoToRecord stDocName, acNewRec

to add a new record.

Hope this helps.

Sam

I am going blind trying to find the mismatch in this code. Could someone
please help..

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

stDocName = "f_Prov"

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