Remember the Selected Record - Requery - Then Go Back To Selected Record...

[HairyArse]

Hi guys,

Bit of a problem I'm hoping someone can help with. I have a telephone call logging system. The system is a tabbed form with each tab representing a person's Telephone Call Inbox. As new calls are entered into the database, they appear in each person's inbox until they are marked as dealt with.

This all works fine and there are no problems with the basics of it, but the problem I do have is that after I have split the database into a Back end and a Front end and distributed the Front ends to each user, when a new call is entered it doesn't appear in the persons inbox until they do a requery.

At first I sent the form to requery using the ontimer event, but the problem I have is that after every reqery the form goes back to the first record. A user on the office forum suggested I use the following code:

vCurrent = Me.Bookmark
Me.Requery
Me.Bookmark = vCurrent

But this gives me invalid bookmark errors, so I was then told to add

On Error Resume Next

But this stops the bookmark function working and instead the first record is loaded every 1 minute (as set by my timer).

Can anyone recommend a better way that actually works without invalid bookmark errors?

 

[ansentry]

Try this code, just replace CustomerID with your Unique Identifier.

You can take out the Msgbox etc. I have it there for testing.

Dim rst As DAO.Recordset
    Dim lngID As Long

    lngID = CustomerID



    MsgBox "This form will now be requeried" & Chr(10) & Chr(13) & _
    "You will be returned to Record Number:" & " " & lngID

    Me.Requery

    Set rst = Me.RecordsetClone


    rst.FindFirst "[CustomerID] = " & lngID
    If Not rst.NoMatch Then
     Me.Bookmark = rst.Bookmark
    End If

    rst.Close

    Set rst = Nothing

 

[HairyArse]

John that's superb and works an absolute treat! Thank you ever so much.

 

[HairyArse]

One other thing though - if the user is working on the record that's being bookmarked, I notice that after the requery the cursor goes back to the very first field in the record, is there any way to make the cursor remain in the field where data was being entered?

 

[ansentry]

Try this in the OnTimer Event, I tried it and it seems to work fine.

Dim rst As DAO.Recordset
    Dim lngID As Long
        Dim CurCtl As String
    
    
    
        CurCtl = Me.ActiveControl.Name
        lngID = CUSTOMERID

  

    Me.Requery

    Set rst = Me.RecordsetClone

    'i had me.requery here and it worked of but the code on the internet
    'that I found had it where it is now. Both Work


    rst.FindFirst "[CustomerID] = " & lngID
    
    If Not rst.NoMatch Then
     Me.Bookmark = rst.Bookmark
     
     
     
      DoCmd.GoToControl CurCtl
            Me.ActiveControl.SetFocus
     
     
    End If

    rst.Close

    Set rst = Nothing

 

[HairyArse]

John that did the job. Thank you very much matey. I would never have been able to write that piece of code.

Did you write it yourself or find it on the internet? If you found it, which website did you use as I only use this site and the official Access forum, and any other useful sites would most definitely be added to my favourites.

 

[ansentry]

The first bit of code that I first posted I got from a forum (don't know which one).

The second bit that you requested about going back to the same control after the requery I worked that out and wrote it myself. (Pretty simple - just like me).


Glad that it worked for you.

 

[HairyArse]

John - thanks again for your help on this problem last week, however things aren't quite working 100%. Every now and then and seemingly at random we get the following error message:

"Run time error 2109.

There is no field named 'Tel ID' in the current record.

I'm not sure exactly what causes this but it could be something to do with the persons telephone call inbox being empty and thus having no records.

Any suggestions?

 

[HairyArse]

That error could actually be because of the extra line below I inserted into your code John:

If Me.NewRecord Or Me.Dirty Then Exit Sub

The reason I put this in is because sometimes there will be no record in the system, so everytime the refresh occurs I get the error message:

Invalid use of null.

 

[ansentry]

Hairy,

What are you using for your primary key for the table, if it is "Tel ID" how can it be blank?

Without knowing you database setup, I would suggest that you put and autonumber field in the table and let that be your field to requery on.

In the code I send you CustomerID is an autonumber field.


Hope this helps.

 

[HairyArse]

John - Tel ID is an Autonumber field and I changed your Customer ID to Tel ID to reflect this.

I don't understand what is causing the error message, it appears at random times and for no real reason. Sometimes the database can work fine for 2 hours but then the error will just appear.

I don't know if this anything to do with it but the pages that use this code are all sub forms on a tabbed menu within a tabbed menu.

So you have a main menu broken up into the different parts of our system, i.e. telephone log, quotations, purchase orders, orders, then within the telephone log there is another tabbed menu with a page for each person - 10 people.

Then each of those 10 pages has a sub form which is where your code is running.

Could the error be because I'm using tabbed sub forms, or is it because I'm applying the code to 10 different pages all within a single menu? Or is it because of the

If Me.NewRecord Or Me.Dirty Then Exit Sub

line I inserted? But if I remove that, I get the Invalid use of Null error - is there a better way of doing the same job as the above code?

 

[ansentry]

Hairy,

I just had another look at your post and then checked the error number in my error number db and it say what you said:

There is no field named 'Tel ID' in the current record.

I did not read your post correctly the first time, it is not missing a record number it cannot find the field Tel ID (control source).

In mine I have a text box called txtCustomerID and the source is CustomerID

so maybe in one of your form you have the name Tel ID and that is not l inked to control source Tel ID.

If I am not making sence then have a look at the attached screen dump.

Just a little thing (and others may disagree with me) but when I name a field in a table I never leave any gaps in the name You= Tel ID Me=TelID.

After I attached the zip file, I realised what I called it.....Oh well

 

[HairyArse]

Hi John, thank you again for all your help with this. I'm going to go through every single page and ensure that each field is correctly named.

Also I'm well aware (now) of the problems encountered when putting a space into a field name. It's just a shame that the project is too far in to change this now - I have that many queries, forms, sub forms and reports all referring to the fields name stupidly that it would be more effort than it's worth to correct this.

I will double check my field names now, and will get back to you to let know.

Thank you ever so much - oh and you can call me Richard not Hairy

 

[HairyArse]

John I've been through all my forms - the fields in every case are all correctly named and refer to [Tel ID] so that's not the problem, the problem for some bizarre reason seems to be down to the line I inserted:

If Me.NewRecord Or Me.Dirty Then Exit Sub

I inserted this as the 2nd line underneath:

Dim rst As DAO.Recordset

Can you see any reason why this line of code should cause this error?

 

[HairyArse]

Ok so I just removed all traces of the

If Me.NewRecord Or Me.Dirty Then Exit Sub

line but am still getting the error message.
Though this time it's referring to the line:

DoCmd.GoToControl CurCtl

I'm really puzzled now.

 

[ansentry]

Richard,

I have made some changes to my code, when I go to a new record and while the CustomerID is still on (AutoNumber) the code stops working (as it should). When I go to a active record (one with a CustomerID) then it works fine.

Bold denotes the changes.

Private Sub Form_Timer()

Dim rst As DAO.Recordset
    Dim lngID As Long
        Dim CurCtl As String           
                    
                    
    
  [B]If IsNull(Me.CustomerID) = True Then

  
Exit Sub
  
  Else[/B]
  
    
        CurCtl = Me.ActiveControl.Name
        lngID = CustomerID

  
    Me.Requery

    Set rst = Me.RecordsetClone

    'i had me.requery here and it worked of but the code on the internet
    'that I found had it where it is now. Both Work


    rst.FindFirst "[CustomerID] = " & lngID
    
    If Not rst.NoMatch Then
     Me.Bookmark = rst.Bookmark
     
     
     
      DoCmd.GoToControl CurCtl
            Me.ActiveControl.SetFocus
     
     
    End If

    rst.Close

    Set rst = Nothing
    
[B]   End If[/B]
   
    
End Sub

It is 12:45am here so of to bed.

 

[HairyArse]

Hi again John - I realise you're in bed now so probably won't see this until tomorrow. I just got the error message with your newest version of code - but I think I might know why it's happening.

It's happened twice in the last hour and it always seems to happen when I'm in a different section to the telephone log. I'll explain.

My main menu has tabbed pages, Telephone Log, Quotations, Purchase Orders, Orders, Customer Complaints.

Within the Telephone Log system is another tabbed page with each tab representing a different user. I was just in the Orders system when I got the error message. Now obviously there isn't a [Tel ID] as I'm not currently on the Telephone Log page, so I guess the code needs to somehow check to see which page the user is currently in, and if it's not the telephone log page named Phone_Log_Main_Menu then exit the code.

Does that sound logical to you and do you know how to fix it?