EOF questions

[HLotis]

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

 

[kathryn]

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

 

[HLotis]

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&quot
'Set rstAuthorFilter = _
'dbsBooksOnLoanHES.OpenRecordset("srtAuthor-Filter&quot

'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

 

[kathryn]

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


Kathryn

 

[HLotis]

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