Me.RecordSource = strSQLSelect troubles

[Garret]

I have converted an Access97 *.mdb to Access2K *.mdb. I am starting to got
thru and covert to ADO.
I am having a bit a trouble setting the recordsource of a simple form. I
have two unbound fields (SerialNumber & PartNumber) that are used to collect
data to check for exisiting records. I have a third field hidden field
(DRnum) that is eventually used as to link a subform. For trouble shooting,
I have deleted the subform, as I cannot seem to set the recordsource of the
form. I get a "you cancelled the previous operation" error.

My basic idea is that I use the AfterUpdate event to go and select the
DRnum and PartNumber for a particular SerialNumber. I know I am returning
records, as the msgbox RS0("DRnum") give me the correct result. I get the
"you cancelled the previos operation" error when Me.RecordSource =
strCheckPnSn is interpreted. Any Ideas??

Here is the code:

Private Sub SerialNumber_AfterUpdate()
On Error GoTo Err_ERRORFR
Dim strCheckPnSn As String
Dim RS0 As ADODB.Recordset
Set RS0 = New ADODB.Recordset

'Build string to check for pn and sn record
strCheckPnSn = "Select DRnum,PartNumber,SerialNumber from tblParts Where
SerialNumber Like '%" & Me("SerialNumber") & "%';"
MsgBox strCheckPnSn

If IsNull(Me("SerialNumber")) Then
' Why bother opening the recordset if you don't have
' the necessary data?
MsgBox "please enter the part number"
Me("PartNumber").SetFocus
Else
RS0.Open strCheckPnSn, CurrentProject.Connection, adOpenDynamic
'RS0.Open strCheckPnSn, CurrentProject.Connection, adOpenDynamic,
adLockReadOnly
MsgBox RS0("DRnum")
If Not RS0.BOF And Not RS0.EOF Then
Me.RecordSource = "Select DRnum,PartNumber,SerialNumber
from tblParts Where drNum = '27';"
End If
End If

Set RS0 = Nothing


Exit_ERRORFR:
Exit Sub

Err_ERRORFR:
MsgBox Err.Description
Resume Exit_ERRORFR

 

[Dirk Goldgar]

I have converted an Access97 *.mdb to Access2K *.mdb. I am starting
to got thru and covert to ADO.

Any special reason you're converting to ADO? You don't have to, you
know, and DAO is more efficient and powerful if you're working with an
..mdb file. Of course, if you're planning to upsize this application to
an .adp, you may as well get a jump on it now by converting your DAO
code to ADO.

(more comments following your quoted code)

I am having a bit a trouble setting the recordsource of a simple
form. I have two unbound fields (SerialNumber & PartNumber) that are
used to collect data to check for exisiting records. I have a third
field hidden field (DRnum) that is eventually used as to link a
subform. For trouble shooting, I have deleted the subform, as I
cannot seem to set the recordsource of the form. I get a "you
cancelled the previous operation" error.

My basic idea is that I use the AfterUpdate event to go and select
the DRnum and PartNumber for a particular SerialNumber. I know I am
returning records, as the msgbox RS0("DRnum") give me the correct
result. I get the "you cancelled the previos operation" error when
Me.RecordSource = strCheckPnSn is interpreted. Any Ideas??

Here is the code:

Private Sub SerialNumber_AfterUpdate()
On Error GoTo Err_ERRORFR
Dim strCheckPnSn As String
Dim RS0 As ADODB.Recordset
Set RS0 = New ADODB.Recordset

'Build string to check for pn and sn record
strCheckPnSn = "Select DRnum,PartNumber,SerialNumber from tblParts
Where SerialNumber Like '%" & Me("SerialNumber") & "%';"
MsgBox strCheckPnSn

If IsNull(Me("SerialNumber")) Then
' Why bother opening the recordset if you don't have
' the necessary data?
MsgBox "please enter the part number"
Me("PartNumber").SetFocus
Else
RS0.Open strCheckPnSn, CurrentProject.Connection,
adOpenDynamic 'RS0.Open strCheckPnSn,
CurrentProject.Connection, adOpenDynamic, adLockReadOnly
MsgBox RS0("DRnum")
If Not RS0.BOF And Not RS0.EOF Then
Me.RecordSource = "Select
DRnum,PartNumber,SerialNumber from tblParts Where drNum = '27';"
End If
End If

Set RS0 = Nothing


Exit_ERRORFR:
Exit Sub

Err_ERRORFR:
MsgBox Err.Description
Resume Exit_ERRORFR

In this code, you never even say "Me.RecordSource = strCheckPnSn". I
assume you swapped in the literal line,

Me.RecordSource = "Select
DRnum,PartNumber,SerialNumber from tblParts Where drNum = '27';"

as a test. Did it work when you did that, or not?

I'm not sure what's going on, but I have two observations:

1. You've created an ADO recordset from a SELECT statement that uses the
ADO/SQL Server wild-card character '%'. However, your form's recordset
is still a DAO recordset, and the SQL statement you assign as its
RecordSource should use the DAO/Jet wild-card character '*' instead.

2. If this form is bound, and if the form is dirty but can't be saved
due to a table constraint, validation rule, or cancelling of the
BeforeUpdate event, then I think trying to assign its RecordSource
property would give you the error you're getting.

Maybe those ideas will give you something to look into.