Hi,

Step 1
I have a set of 4 queries(author, title, subject, media)any one of which underlies a form/subform. The user selects which underlying query he wants to use by selecting one of the 4 from a list in a combo box.

Upon selection, the subform's SourceObject is set to the name of the chosen query. The subform is then automatically refreshed based on the chosen query.

Step 2
Next, the user can select specific records from the chosen query. This is based on the user responding to a prompt and filling in a parameter value from the user such as(specific author, part of a title,subject,etc)
Suppose the user responds with a value that is NOT contained in the table/query, like xyz.
The result is that Access presents a blank record which is beyond the last record.

I want to give a message to the user that the value was not found and move to the the last/first record.

How do I check for eof ? I cannot open it as rst ahead of time,  nor do I know what filenumber it is after the user picks one.

How do I determine what the filenumber is if I did not assign it one ?

Harry.Lotis@mms.gov

OK, how are you returning the records in step two?  are you running another query or filtering?

Have you looked at the RecordsetClone Property?  It returns a reference to the form's underlying recordset, and it has a recordcount property, I believe.

I don't know what "filenumber" refers to?  Is that the value of the file chosen in step one?  Can you use the value of the recordsource of the form?

Hope I can be of some help.

Kathryn
 
 
 

The underlying recordsource can be any of 4 parameterized queries (title/author/subject/media).
When a record is not found on any of the filters, an eof occurs and a null record is presented to the user. So, instead of EOF, I decided to try a manual approach using a test for isnull(fldname).

The code in the subform looks like this
 'open query which prompts for a selection criteria
 Me.RecordSource = "srtAuthor-Filter"  
    If IsNull(Me![BookID]) Then        
      MsgBox _
           "No data found." & vbCr & _
           "Go to step 1 above." & vbCr & _
           "Make a selection and try again."
    End If

NOTE: using docmd.showallrecords did not work either

Nor did this:
 'Set dbsBooksOnLoanHES = OpenDatabase("BooksOnLoanHES.mdb")
    'Set rstAuthorFilter = _
        'dbsBooksOnLoanHES.OpenRecordset("srtAuthor-Filter")
        
    'With rstAuthorFilter
        '.MoveNext
        'If .EOF Then
        '  MsgBox _
        '  "End of the file!" & vbCr & _
        '  "Pointer being moved to first record."
        'DoCmd.GoToRecord acDataForm, "frmSearchByAuthor",  acFirst
        ' End If
     'End With
    
 

Just to clarify: Is "srtAuthor-Filter" a query??  Is this where the prompt for specific records comes from?


Kathryn
 
 
 

In step 1, the user selects either author,title,subject or type. All records are shown sorted by one of the above.

In step 2, the user can choose a specific subset by filling in a prompt coming from a parameterized query. (First letter of Author's Last name).
The Subform SourceObject is reset to the appropriate query to handle it.

Often the user asks incorrectly for a records that is not there and the comes back with a blank row. What happened asked the user. I have to tell him that no matching record was found and to somehow show all records again.

I ended up checking for isnul(fld) and telling the user to start at step 1 again....

Harry