How to handle form when it reaches EOF

[jaishu]

Hi,

Thanks buddies for all ur help for the threads that i posted earlier!!
Again i have some questions..

The form that i created is one that shows records that are not in a particular table , once its updated to the table through a submit button on the form, it does not appear on the form (meaning, the form shows only ones that are still not been processed,)
so finaaly when it reaches the end of file, the form becomes blank and "ACCESS says it had encountered an error and needs to close" and creates backup copy..i dont want this to happen when the users use it.
I tried giving a recordset.Eof condition , when true giving a message to the user that "all have been done", but as soon as it jumps into that if block when it reaches EOF condition Access Pops up that message, How to solve this???

Thanks very much in advance!!!

[MMcCarthy]

Hi,

Thanks buddies for all ur help for the threads that i posted earlier!!
Again i have some questions..

The form that i created is one that shows records that are not in a particular table , once its updated to the table through a submit button on the form, it does not appear on the form (meaning, the form shows only ones that are still not been processed,)
so finaaly when it reaches the end of file, the form becomes blank and "ACCESS says it had encountered an error and needs to close" and creates backup copy..i dont want this to happen when the users use it.
I tried giving a recordset.Eof condition , when true giving a message to the user that "all have been done", but as soon as it jumps into that if block when it reaches EOF condition Access Pops up that message, How to solve this???

Thanks very much in advance!!!

It's difficult to comment without seeing the relevant code. Have you left the form unbound or is there something in the record source of the form?

[jaishu]

Yes , the form is loaded(refreshed) everytime an id is assigned, requeried, so the code is ..Once i assign a particular ID(if insert into backend table is successful), then i requery the form as i dopnt want the assigned IDs to reappear in the form, , The form is based on query, so after succesful insertion i give the code

[code goes here].......
MsgBox "ASSIGN SUCCESSFUL"
Form_mainformname.Requery
Form_subformname_sub.Requery
Form_mainformname.Text34 = Clear
Form_mainformname.Text34.SetFocus ----------------This is where i get the problem when EOF is reached , It pops up with the message saying, "You Cant got to the specified record", bcos there is no more records, now how to overcome this?

Hope this makes you understand my problem.!

[jaishu]

First of all , the Business users will have this as an Access application to assign an ID to each member. What we do from IT perspective is get data files,do preprocessing and suggest matches for each member say for Member A ,we wud have suggested ID1, ID 4, ID 3, and for MEmber B , wud have 4 suggested matches ID1,ID2,ID4, ID5 and only the business users know what is the correct match for each member A , B and so on, so they have this front end application as a form, which takes data from all tables(Oracle tables that we load based on our Procedures..) so that they choose the best match of all choices.


So the form displays Member related Data, and all the suggested choices in a subform, where Business user picks one and puts it in a text box provided and Hits Submit, That ID is assigned to that particular member and written to output table from where We do processing again .There are more than 10000 members ..The form does all this...its based on a query which says select member from input table where member not in output table

Now if user completes assigning ID for all 10000 members , there is no record to pick from input table as the query is not satisfied, so Form becomes blank (because once a member is assigned an ID , it goes to output table,)
Hope i have explained clearly, i am also attaching the code.

Private Sub submitid_Click()
 
dim rs as new adodb.recordset
 
If MsgBox("Are you sure you want to assign " & UCase(Text34) & " to " & MEMBER_ID & " ", vbYesNo, "Confirm selection") = vbYes Then
 
query2 = "select match_id, member_id,match_id_type from output table where match_id = '" & UCase(Text34) & "' or member_id ='" & MEMBER_ID & "'"
rs.Open query2, Cnt, adOpenKeyset, adLockOptimistic
 
If rs.RecordCount = -1 Or rs.RecordCount = 0 Then  
 
query1 = "Insert into output table (member_id,match_id,match_id_type, match_level,last_modified ) " & _
"select member_id,match_id,match_id_type, match_level,'" & dtm & "' from" & _"
" input table where member_id ='" & MEMBER_ID & "' and match_id = '" & UCase(Text34) & "' and " & _
                   " match_id_type ='" & rsmatchy("match_id_type") & "' and match_level = " & rsmatchy("match_level") & " ; "
 
DoCmd.SetWarnings False
DoCmd.RunSQL query1
MsgBox "ASSIGN SUCCESSFUL"                      
 
queryref = " SELECT MEMBER_ID, MEMBER_NAME,                          MEMBER_END_DATE, FROM Input table WHERE                             input table.MEMBER_ID NOT IN ( SELECT output table.MEMBER_ID FROM output table)"                         
Form_mainform.Requery
Form_sub.Requery
Me.Text34.SetFocus
Text34 = Clear
Exit sub
Else
 
MsgBox " " & rs("MATCH_ID_TYPE") & " ID " & rs("MATCH_ID") & " has already been assigned to member " & rs("member_id") & "  ", vbCritical, "MatchID Conflict"
Text34 = Clear
Me.Text34.SetFocus
Exit Sub
End if
 

THE form is based on Queryref, a query based on which the form loads and then on its requeried everytime after insert, now when all 10000 records are done , when this is requeried, it returns nothing and form becomes blank, and says "you cant got to the specified record " when it comes to me.text34.setfocus... This is what is my problem..What is the sol?

[NeoPa]

Hi,

Thanks buddies for all ur help for the threads that i posted earlier!!
Again i have some questions..

The form that i created is one that shows records that are not in a particular table , once its updated to the table through a submit button on the form, it does not appear on the form (meaning, the form shows only ones that are still not been processed,)
so finaaly when it reaches the end of file, the form becomes blank and "ACCESS says it had encountered an error and needs to close" and creates backup copy..i dont want this to happen when the users use it.
I tried giving a recordset.Eof condition , when true giving a message to the user that "all have been done", but as soon as it jumps into that if block when it reaches EOF condition Access Pops up that message, How to solve this???

Thanks very much in advance!!!

You say (as I suspected) in one of your other posts, that this happens after a .Requery. In that case I suggest that you may like to do DCount() (or such like) check on the recordset immediately beforehand. If it shows that there are no records available, simply close the form instead of requerying.

I hope this helps you.

[jaishu]

Thanks very much! Its working now,
I tried doing a check before requerying but i didnt close the form and Just gave a message , so i had all these errors.....
Now its Fine!Thanks a lot!

[NeoPa]

Thanks very much! Its working now,
I tried doing a check before requerying but i didnt close the form and Just gave a message , so i had all these errors.....
Now its Fine!Thanks a lot!

I'm very pleased to hear it.
We're here if you have any more questions :)