Type Mismatch

D

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
 
O

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
 
D

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



OfficeDev18 via AccessMonster.com said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch 2
User Defined 3
Type Mismatch 3
Object dosent support this property or method 2
User Defined Type 2
Type Mismatch 2
Need form to display related record not just add new 16
erratic OpenArgs behavior 2

Top