Code is failing on empyt recordset

[Guest]

I have a form that prompts user for entry of primary key locator field
information, upon entry (on update) form runs macro that performs select,
then open other form in update mode.

Other form that is opened is form/sub form combo. This process dies on the
following code:

Function RecordsInTable(Tablename As String, Fieldname As String) As Long
Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset
strTableField = Tablename & "." & Fieldname
strTableYr = Tablename & ".CASE_NUM_YR"
strTableCase = Tablename & ".CASE_NUM"
strFormYear = Me.txt_CASE_NUM_YR
strFormCase = Me.txt_CASE_NUM
'If strFormYear is Null, then get Case Year from Previous Case Year field
If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_NUM_YR
End If
'If strFormCase is Null, then get Case # from Previous Case Number Field
If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If
strSQL = "SELECT Count(" & strTableField & ") AS [Count] From " &
Tablename & _
" WHERE " & strTableYr & " = " & strFormYear & _
" AND " & strTableCase & " = " & strFormCase & ";"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) '<=== dies
here
RecordsInTable = rst!Count
Set rst = Nothing

End Function

This function is called by the Sub-form during On Form Current and
Before-Update events.

Can someone assist me in placing the proper code to prevent failure on no
matching records found for the query that first runs and passes recordset to
update program.

I want to just send message back to user, incorrect infomation entered, etc,
rather than display the form and cause the form to die. I would like to
return to the first form, FR_Search_By_Case form whick prompts user for key
information.

Thanks

 

[George Nicholson]

It dies ON the OpenRecordset line or on the line after it?

If it dies on the OpenRecordset line (which is what your note indicates):
set a breakpoint on that line. When reached, in the immediate window, type:
? strSQL
See if you can spot what the problem is in the string you've constructed.

If it dies on RecordsInTable = rst!Count (which it will if rst has no
records and is what your message header indicates):
If rst.EOF then
' No records - notify user
Else
RecordsInTable = rst!Count
End If
Set rst = nothing

BTW: Count is a reserved word per
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
It is better to avoid using reserved words for your own functions,
fieldnames (even a query alias), etc.

 

[Guest]

George,

Thanks for your speedy response. The line of code that is highlighed by the
debugging capabilities of access is the line of:

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

But I, like yourself feel that it is actually failing on the RecordsInTable
= rst!Count statement. I pretty sure of it. The way things fall are this.
The user, opens form named Fr_Search_By_Case. Which prompts the user for two
fields: CASE_NUM_YR and CASE_NUM. Both of these fields, when joined build
the primary key for table TST_FR_CASE_RECORDS (the parent table) and the
primary key field for TST_FR_CASE_OTHERS. There is a one-to-many
relationship going on here between table A and table B. With the possiblity
of no matching records to be found in table B, but could always be a record
in Table A the primary/parent table. Table B is also indexed on SEQ_NUM
field that is adjacent to the joined CASE_NUM_YR and CASE_NUM Field in table
B, thus a primary key/secondary key layout for table B.

It works well I think, but, getting back to why it fails. If the user
enters a key of 2009 for primary key part 1 and 999 for primary key part 2,
thus 2009999 would be what they would be searching for, but the largest key
that exists in either table is 2004999, then the user would get an EMPTY
results. This is what I can not seem to code properly for and in the correct
place.

If the user enters a record key search, and it is located then the next step
is to open form FR_CR_U which is a tabbed form with 4 tab pages, the first 3
tab pages allow the user to update/change table A record values, while the
last tabbed page allows the user to update existing Table B records, or to
even Add new Table B records that would be pertinent to the Case. The part
of code that is failing is actually code from the sub/form sbfr_CR_U which is
the display/update for table B records. But the order of calling and natural
function should be FR-Search_By_Case form, get number/open FR_CR_U tabbed
form. It's just that the On-current event of the sub-form is dying as it
attempts to verify record that arent there when a user enters an invalid
number..

That's what I need to code for. I've attempted to stop the action in the
called form Fr_CR_U, by coding if no records then do this action, but,
because of the subform and its function, my code doesn't stop the function
call, and it fails. I can send all the code and record layout if needed.
I'm confused as to where to code what in order to stop on no found matching
data to this query. This is wordy, but I hope it helps.

Thanks

It dies ON the OpenRecordset line or on the line after it?

If it dies on the OpenRecordset line (which is what your note indicates):
set a breakpoint on that line. When reached, in the immediate window, type:
? strSQL
See if you can spot what the problem is in the string you've constructed.

If it dies on RecordsInTable = rst!Count (which it will if rst has no
records and is what your message header indicates):
If rst.EOF then
' No records - notify user
Else
RecordsInTable = rst!Count
End If
Set rst = nothing

BTW: Count is a reserved word per
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
It is better to avoid using reserved words for your own functions,
fieldnames (even a query alias), etc.

--
George Nicholson

Remove 'Junk' from return address.

I have a form that prompts user for entry of primary key locator field
information, upon entry (on update) form runs macro that performs select,
then open other form in update mode.

Other form that is opened is form/sub form combo. This process dies on
the
following code:

Function RecordsInTable(Tablename As String, Fieldname As String) As Long
Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset
strTableField = Tablename & "." & Fieldname
strTableYr = Tablename & ".CASE_NUM_YR"
strTableCase = Tablename & ".CASE_NUM"
strFormYear = Me.txt_CASE_NUM_YR
strFormCase = Me.txt_CASE_NUM
'If strFormYear is Null, then get Case Year from Previous Case Year
field
If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_NUM_YR
End If
'If strFormCase is Null, then get Case # from Previous Case Number Field
If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If
strSQL = "SELECT Count(" & strTableField & ") AS [Count] From " &
Tablename & _
" WHERE " & strTableYr & " = " & strFormYear & _
" AND " & strTableCase & " = " & strFormCase & ";"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) '<=== dies
here
RecordsInTable = rst!Count
Set rst = Nothing

End Function

This function is called by the Sub-form during On Form Current and
Before-Update events.

Can someone assist me in placing the proper code to prevent failure on no
matching records found for the query that first runs and passes recordset
to
update program.

I want to just send message back to user, incorrect infomation entered,
etc,
rather than display the form and cause the form to die. I would like to
return to the first form, FR_Search_By_Case form whick prompts user for
key
information.

Thanks

 

[Dirk Goldgar]

I have a form that prompts user for entry of primary key locator field
information, upon entry (on update) form runs macro that performs
select, then open other form in update mode.

Other form that is opened is form/sub form combo. This process dies
on the following code:

Function RecordsInTable(Tablename As String, Fieldname As String) As
Long Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset
strTableField = Tablename & "." & Fieldname
strTableYr = Tablename & ".CASE_NUM_YR"
strTableCase = Tablename & ".CASE_NUM"
strFormYear = Me.txt_CASE_NUM_YR
strFormCase = Me.txt_CASE_NUM
'If strFormYear is Null, then get Case Year from Previous Case
Year field If IsNull(strFormYear) = True Then
strFormYear = Me.unbtxt_PREV_CASE_NUM_YR
End If
'If strFormCase is Null, then get Case # from Previous Case Number
Field If IsNull(strFormCase) = True Then
strFormCase = Me.unbtxt_PREV_CASE_NUM
End If
strSQL = "SELECT Count(" & strTableField & ") AS [Count] From " &
Tablename & _
" WHERE " & strTableYr & " = " & strFormYear & _
" AND " & strTableCase & " = " & strFormCase & ";"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'<=== dies here
RecordsInTable = rst!Count
Set rst = Nothing

End Function

This function is called by the Sub-form during On Form Current and
Before-Update events.

Can someone assist me in placing the proper code to prevent failure
on no matching records found for the query that first runs and passes
recordset to update program.

I want to just send message back to user, incorrect infomation
entered, etc, rather than display the form and cause the form to die.
I would like to return to the first form, FR_Search_By_Case form
whick prompts user for key information.

Thanks

What is the actual error message (and number, if that is given) that is
being displayed? If it's failing on the line you say, there's something
wrong with your query string. It's not obvious to me what's wrong, but
you might get the error "Type mismatch in criteria expression" if your
fields CASE_NUM_YR and CASE_NUM are defined as text, since you aren't
enclosing the criterion values in quotes.