RecordCount Struggles -- IF RS0.RecordCount >1 then - not working

G

G

Hello,
I am trying to determine the number of records in a recordset (RS0) made by
querying a Microsoft SQL server datastore from an ado procedure in
access2000. I am using "msgbox RS0.RecordCount" which does show results the
proper results: 0, 1, some number other number.

The problem is that when I try to make a condition"If RS0.RecordCount > 1
then", the code will then enter the underlying code. Even when "msgbox
rs0.recordcount" show 81, the code does not go into the loop.

Any ideas?
Thanks


---------------------------------
-----START OF CODE------
--------------------------------
Private Sub SN_AfterUpdate()
On Error GoTo Err_SN_ErrHndl
'need to save the record before moving the cursor
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


'need to move the cursor the the customer Combo
'after all the information is entered
ComboCustID.SetFocus
Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl
End Sub

Private Sub SN_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_SN_ErrHndl

'Garret created on 8/16/04
Dim RS0 As Recordset
Dim strGetNFO As String
Dim strTooManySN As String

strGetNFO = "SELECT * FROM ESIDB.dbo.SOFSN SOFSN WHERE ((SERIAL_NUMBER)='"
strGetNFO = strGetNFO & Me![SN] & "' AND (PART_ID) Like '305%')"
'MsgBox strGetNFO

'INSTANTIATE THE RECORDSOURCE AND OPEN IT UP
Set RS0 = New ADODB.Recordset
RS0.Open strGetNFO,
"Provider=SQLOLEDB;server=sqlserver2;uid=g;pwd=g;DSN=esi", adOpenStatic

'NEED TO SHOW A BOX AT THIS POINT THAT WILL ALLOW A USER TO
'SELECT THE DESIRED UNIT IF THERE ARE SEVERAL UNITS WITH THE
'SAME SN IN ESI GH 9-1-04
MsgBox RS0.RecordCount

If RS0.RecordCount > 1 Then
strTooManySN = "--------------------" & vbCrLf
strTooManySN = strTooManySN & "THERE ARE TOO MANY UNITS IN ESI WITH SN "
& Me("SN") & vbCrLf
strTooManySN = strTooManySN & "CANNOT DETERMINE WHAT 305-xxxx-xxx YOU
CARE ABOUT" & vbCrLf
strTooManySN = "--------------------"
Else
'POPULATE THE FORM WITH THE DATA FROM THE ESI DATABASE
If Not RS0.EOF And Not RS0.BOF Then
Me![PN] = RTrim(RS0![Part_Id])
Me![OrigSO] = RS0![ORIG_SO_ID]
Me![WarrantyEndDate] = RS0![Off_Warranty]
Me![MN] = GetMNbyPN(RS0![Part_Id])
Me![OrigShipDate] = RS0![DATE_CREATED]
Me![ShipToCustID] = RS0![ORIG_SHIP_TO]
Me![BillToCustID] = RS0![BILL_TO_CUST]
'Need to set the In Warranty in or out check box
If RS0![Off_Warranty] > Now() Then
Me![InWarranty] = True
Else
Me![InWarranty] = False
End If
Else
MsgBox "THIS SN DOES NOT SEEM TO HAVE ANY RECORDS IN ESI"
End If
End If



Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl


'CLOSE AND CLEAN UP
RS0.Close
Set RS0 = Nothing
End Sub
 
J

Joe Fallon

Did you do a move last before trying to determine the count?
--
Joe Fallon
Access MVP



G said:
Hello,
I am trying to determine the number of records in a recordset (RS0) made
by
querying a Microsoft SQL server datastore from an ado procedure in
access2000. I am using "msgbox RS0.RecordCount" which does show results
the
proper results: 0, 1, some number other number.

The problem is that when I try to make a condition"If RS0.RecordCount > 1
then", the code will then enter the underlying code. Even when "msgbox
rs0.recordcount" show 81, the code does not go into the loop.

Any ideas?
Thanks


---------------------------------
-----START OF CODE------
--------------------------------
Private Sub SN_AfterUpdate()
On Error GoTo Err_SN_ErrHndl
'need to save the record before moving the cursor
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


'need to move the cursor the the customer Combo
'after all the information is entered
ComboCustID.SetFocus
Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl
End Sub

Private Sub SN_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_SN_ErrHndl

'Garret created on 8/16/04
Dim RS0 As Recordset
Dim strGetNFO As String
Dim strTooManySN As String

strGetNFO = "SELECT * FROM ESIDB.dbo.SOFSN SOFSN WHERE ((SERIAL_NUMBER)='"
strGetNFO = strGetNFO & Me![SN] & "' AND (PART_ID) Like '305%')"
'MsgBox strGetNFO

'INSTANTIATE THE RECORDSOURCE AND OPEN IT UP
Set RS0 = New ADODB.Recordset
RS0.Open strGetNFO,
"Provider=SQLOLEDB;server=sqlserver2;uid=g;pwd=g;DSN=esi", adOpenStatic

'NEED TO SHOW A BOX AT THIS POINT THAT WILL ALLOW A USER TO
'SELECT THE DESIRED UNIT IF THERE ARE SEVERAL UNITS WITH THE
'SAME SN IN ESI GH 9-1-04
MsgBox RS0.RecordCount

If RS0.RecordCount > 1 Then
strTooManySN = "--------------------" & vbCrLf
strTooManySN = strTooManySN & "THERE ARE TOO MANY UNITS IN ESI WITH SN
"
& Me("SN") & vbCrLf
strTooManySN = strTooManySN & "CANNOT DETERMINE WHAT 305-xxxx-xxx YOU
CARE ABOUT" & vbCrLf
strTooManySN = "--------------------"
Else
'POPULATE THE FORM WITH THE DATA FROM THE ESI DATABASE
If Not RS0.EOF And Not RS0.BOF Then
Me![PN] = RTrim(RS0![Part_Id])
Me![OrigSO] = RS0![ORIG_SO_ID]
Me![WarrantyEndDate] = RS0![Off_Warranty]
Me![MN] = GetMNbyPN(RS0![Part_Id])
Me![OrigShipDate] = RS0![DATE_CREATED]
Me![ShipToCustID] = RS0![ORIG_SHIP_TO]
Me![BillToCustID] = RS0![BILL_TO_CUST]
'Need to set the In Warranty in or out check box
If RS0![Off_Warranty] > Now() Then
Me![InWarranty] = True
Else
Me![InWarranty] = False
End If
Else
MsgBox "THIS SN DOES NOT SEEM TO HAVE ANY RECORDS IN ESI"
End If
End If



Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl


'CLOSE AND CLEAN UP
RS0.Close
Set RS0 = Nothing
End Sub
Click to expand...
 
A

Allen Browne

With ADO recordsets, the count can be -1 when not determined.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

G said:
Hello,
I am trying to determine the number of records in a recordset (RS0) made
by
querying a Microsoft SQL server datastore from an ado procedure in
access2000. I am using "msgbox RS0.RecordCount" which does show results
the
proper results: 0, 1, some number other number.

The problem is that when I try to make a condition"If RS0.RecordCount > 1
then", the code will then enter the underlying code. Even when "msgbox
rs0.recordcount" show 81, the code does not go into the loop.

Any ideas?
Thanks


---------------------------------
-----START OF CODE------
--------------------------------
Private Sub SN_AfterUpdate()
On Error GoTo Err_SN_ErrHndl
'need to save the record before moving the cursor
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


'need to move the cursor the the customer Combo
'after all the information is entered
ComboCustID.SetFocus
Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl
End Sub

Private Sub SN_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_SN_ErrHndl

'Garret created on 8/16/04
Dim RS0 As Recordset
Dim strGetNFO As String
Dim strTooManySN As String

strGetNFO = "SELECT * FROM ESIDB.dbo.SOFSN SOFSN WHERE ((SERIAL_NUMBER)='"
strGetNFO = strGetNFO & Me![SN] & "' AND (PART_ID) Like '305%')"
'MsgBox strGetNFO

'INSTANTIATE THE RECORDSOURCE AND OPEN IT UP
Set RS0 = New ADODB.Recordset
RS0.Open strGetNFO,
"Provider=SQLOLEDB;server=sqlserver2;uid=g;pwd=g;DSN=esi", adOpenStatic

'NEED TO SHOW A BOX AT THIS POINT THAT WILL ALLOW A USER TO
'SELECT THE DESIRED UNIT IF THERE ARE SEVERAL UNITS WITH THE
'SAME SN IN ESI GH 9-1-04
MsgBox RS0.RecordCount

If RS0.RecordCount > 1 Then
strTooManySN = "--------------------" & vbCrLf
strTooManySN = strTooManySN & "THERE ARE TOO MANY UNITS IN ESI WITH SN
"
& Me("SN") & vbCrLf
strTooManySN = strTooManySN & "CANNOT DETERMINE WHAT 305-xxxx-xxx YOU
CARE ABOUT" & vbCrLf
strTooManySN = "--------------------"
Else
'POPULATE THE FORM WITH THE DATA FROM THE ESI DATABASE
If Not RS0.EOF And Not RS0.BOF Then
Me![PN] = RTrim(RS0![Part_Id])
Me![OrigSO] = RS0![ORIG_SO_ID]
Me![WarrantyEndDate] = RS0![Off_Warranty]
Me![MN] = GetMNbyPN(RS0![Part_Id])
Me![OrigShipDate] = RS0![DATE_CREATED]
Me![ShipToCustID] = RS0![ORIG_SHIP_TO]
Me![BillToCustID] = RS0![BILL_TO_CUST]
'Need to set the In Warranty in or out check box
If RS0![Off_Warranty] > Now() Then
Me![InWarranty] = True
Else
Me![InWarranty] = False
End If
Else
MsgBox "THIS SN DOES NOT SEEM TO HAVE ANY RECORDS IN ESI"
End If
End If



Exit_SN_ErrHndl:
Exit Sub

Err_SN_ErrHndl:
MsgBox Err.Description
Resume Exit_SN_ErrHndl


'CLOSE AND CLEAN UP
RS0.Close
Set RS0 = Nothing
End Sub
Click to expand...
 
T

Tim Ferguson

strGetNFO = "SELECT * FROM ESIDB.dbo.SOFSN SOFSN WHERE
((SERIAL_NUMBER)='" strGetNFO = strGetNFO & Me![SN] & "' AND (PART_ID)
Like '305%')" 'MsgBox strGetNFO
Click to expand...

Just get the server to do the work:

strGetNFO = "SELECT COUNT(*) " & _
"FROM ESIDB.dbo.SOFSN " & _
"WHERE ((SERIAL_NUMBER)='" & Me![SN] & "' " & _
" AND (PART_ID) Like '305%')"

MsgBox strGetNFO


B Wishes


Tim F
 

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

Me.RecordSource = strSQLSelect troubles 1
recordcount gives back -1... 2
Event procedure not working 2
Why am I getting a recordcount of -1 2
Using If then statment 2
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
if then else 4
Erite Conflict Error - Dirty is not Working 1

Top