Recordset Clone FindFirst - driving me mad!

[optionone]

Hi,

Can anyone advise what is wrong with this?

Public Function newpriorapp()
    Dim rs As Object
 
    Set mdbthis = CurrentDb
    Set mrsPatients = mdbthis.OpenRecordset("temptable-priorapp-data", dbOpenDynaset)
    Set rs = [Form_form-main-menu].Recordset.Clone
 
    [Form_form-main-menu].searchhospnum.Requery
    [Form_form-main-menu].searchpatname.Requery
    [Form_form-main-menu].subform_show_pending.Requery
 
    rs.FindFirst "[HospNum] = '" & mrsPatients("HospNum") & "'"
    If Not rs.EOF Then [Form_form-main-menu].Bookmark = rs.Bookmark
 
    [Form_form-main-menu].NHSNum.SetFocus
 
End Function

Database stores funding requests and this code fires when a new funding request is added to the DB. The intention being to update the search combo boxes, the subform that shows pending requests and to jump to the most recently added request (from the new patient details that are temp stored in the temp table above)

The requeries and setfocus work as does the mrsPatients grab of the hospital number - cant work out why the rest doesnt work though. Also not sure if it is meant to be recordsetclone or recordset.clone but neither work

Thanks

 

[boblarson]

Don't requery AFTER you have set the recordset clone. If you are going to requery do it BEFORE you set the clone. Otherwise your clone is out-of-date.

 

[optionone]

commented out the requeries (also moved them higher for later) but it still fails to jump to the newly added record - any other suggestions?

 

[boblarson]

How are you calling this function? And do you do a save of the record beforehand?

 

[optionone]

How are you calling this function? And do you do a save of the record beforehand?

Calling it from a macro that is run when the user chooses to append the details of the new funding request to the db

The macro (in order): closes the form and saves, appends the data from the temp table to the main table and then the function is run with runcode

Not doing a save of the record as the new funding request entry form effectively dictates whether the user wants to close without saving changes (data just sits in the temp table until its wiped next time and form is closed) or the data is appended to the main table (and so the macro discussed above is run)

 

[boblarson]

commented out the requeries (also moved them higher for later) but it still fails to jump to the newly added record - any other suggestions?

You still have to requery in order for the new record to be included in the form's recordset. Commenting out the requeries just means that it won't ever work because the new record isn't in the form's recordset yet.

So you have to

1. Add the new record
2. Requery the form
3. Set the recordset object to the form's recordsetclone
4. Do the find first.
5. Set the form's bookmark to the recordset's bookmark

 

[optionone]

You still have to requery in order for the new record to be included in the form's recordset. Commenting out the requeries just means that it won't ever work because the new record isn't in the form's recordset yet.

So you have to

1. Add the new record
2. Requery the form
3. Set the recordset object to the form's recordsetclone
4. Do the find first.
5. Set the form's bookmark to the recordset's bookmark

Must be doing something wrong as this still isnt working:

Public Function newpriorapp()
    Dim rs As Object
    
    [Form_form-main-menu].Requery
    
    Set mdbthis = CurrentDb
    Set mrsPatients = mdbthis.OpenRecordset("temptable-priorapp-data", dbOpenDynaset)
    Set rs = [Form_form-main-menu].RecordsetClone
     
    rs.FindFirst "[HospNum] = '" & mrsPatients("HospNum") & "'"
    If Not rs.EOF Then [Form_form-main-menu].Bookmark = rs.Bookmark
    
    [Form_form-main-menu].HospNum.SetFocus
        
End Function

In terms of your instructions - the record is already added to the database it just wont switch to it so im presuming the error must still lie with this code - added the requery in and removed the others since they arent needed atm in terms of showing the specific record - steps 3-5 seem to be right as far as i can see

It will jump to the HospNum.SetFocus but it just displays the first record of the table - am i missing anything else?

Thanks again

 

[boblarson]

Is HospNum a text or numeric field?

 

[optionone]

Is HospNum a text or numeric field?

its alphanumeric - first two chars are letters then 4numbers - could use NHSNum from the same table which would be purely numeric

 

[optionone]

Changed it to the following - seems to have been due to the way the forms were referrenced - all working now:

Public Function newpriorapp()
    ' Update Form Pages to show newly added prior approval
    Dim rs As Object
    Dim strCriteria As String
    
    Forms![form-main-menu].Requery
    Forms![form-main-menu]![searchhospnum].Requery
    Forms![form-main-menu]![searchpatname].Requery
    [Form_form-main-menu].subform_show_pending.Requery
     
    Set mdbthis = CurrentDb
    Set mrsPatients = mdbthis.OpenRecordset("temptable-priorapp-data", dbOpenDynaset)
    Set rs = Forms![form-main-menu].RecordsetClone
    
    strCriteria = "[HospNum] = '" & mrsPatients("HospNum") & "'"
     
    rs.FindFirst strCriteria
    If Not rs.EOF Then Forms![form-main-menu].Bookmark = rs.Bookmark
    
    [Form_form-main-menu].NHSNum.SetFocus
        
End Function