Re: Code is failing on empyt recordset

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

RNUSZ_at_OKDPS
Date: 03/01/05 

Date: Mon, 28 Feb 2005 18:27:01 -0800

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

"George Nicholson" wrote:

> 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.
>
>
> "RNUSZ@OKDPS" <RNUSZOKDPS@discussions.microsoft.com> wrote in message
> news:AE4D79F0-5091-4E27-A425-4A986F4AC1BF@microsoft.com...
> >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
> >
> >
> > --
> > Robert Nusz
> > Sr. Programmer Analyst II
>
>
>

Relevant Pages

  • Re: Help with exporting from form
    ... Sub Export_Delim(vSource As String, vFile As String) ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... and uncomment the commented lines it will work with hardcoded source query ...
    (microsoft.public.access.formscoding)
  • Re: What do I need?
    ... Yeah i do think you are correct in saying that i should use a array. ... Dim strFirst As String, strSecond As String, strThird As String ... Set rst = New ADODB.Recordset ... i have the query but i only want to assign certain values to variables. ...
    (microsoft.public.access.queries)
  • Re: What do I need?
    ... Dim strFirst As String, strSecond As String, strThird As String ... Set rst = New ADODB.Recordset ... who have not audited a site on the occasions of its last three audits queries ... i have the query but i only want to assign certain values to variables. ...
    (microsoft.public.access.queries)
  • Re: SQL Server dataset read from database A and write to database B
    ... private bool ImportXML(string docIn, ref string ErrorMsg) ... SqlCommand query = new SqlCommand ... I think you are trying to insert the primary key from the original table into ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: speeding up a query
    ... > I have a query with a function call that is used as the ... > return a text string with the following ... > Dim rst As DAO.Recordset ... > Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)