Help needed

[mariewhitehead]

I need some help on a database that is set up for work use.

It's rather large and I have no technicial knowledge of access whatsoever so an answer in plain english would be appreciated.

I have attached a picture of the screen that i am having the problem with. The blank fields contain peoples names and addresses. On this page i have to click on "update service user" on the first user, the database opens a seperate page and i enter a few details, then i click to go back to this screen.

When I return back to the screen, the scroll bar is at the top of the list. This is OK when I am doing the first few letters of the alphabet (the users are listed alphabetically) but when i have too use the scroll bar and choose the letters later down the list, then click to update then return to the screen and the scroll barr is not where i left it...ie: it's at the top.

So what I am asking is, is there a way of freezing this scroll bar so it will stay there even when i have navigated away from the screen to update each user?

If someone could answer me this query then I would be most grateful as it's quite time consuming for me to use the scroll bar and find the next person that i need to update.

 

[sstreet]

This could be achieved by grabbing the ID of the field you are working on when the page is opened. When you close the page you could then go to the record with that ID in the scrolled form. e.g.

'Get the record number
Dim lngrecordnum As Long
lngrecordnum = frm.CurrentRecord

'Go to the record
DoCmd.GoToRecord acDataForm, "Subform", acGoTo, lngrecordnum

 

[mariewhitehead]

I have no idea how to do this!!

can you explain in clear english please??

 

[sstreet]

It's not something that can be easily explained, but here goes:
Your basic problem seems to be that when you close the "service use" form the underlying form is being requeried/refreshed, therefore sorting alphabetically and moving back to the top. The code that I have given you is to obtain the current record number from the main form, so that when you return to it you can go back to that record. (I hope this is making sense!)

The first part of the code would be run when the "service use" form is opened (OnOpen event) and the second part would be used when it is closed (OnClose event). I cannot give you the exact code to use because it depends on how your forms/subforms are put together.

 

[mariewhitehead]

ok, thanks.

I understand what you are saying now, but i am just not sure of where to insert the code!

 

[mariewhitehead]

ok, i have now got in to the back of the database where all the codes are! i think i may be getting closer.....

 

[mariewhitehead]

ok, i have found the code for the page that i open to update the details.
where exactly do i put the code in all this lot??

Option Compare Database

Private Sub cmdAddUseageCriteria_Click()

On Error GoTo cmdAddUseageCriteria_Err:

Dim strlinkcriteria As String

strlinkcriteria = "[User ID] = " & Str(Me.User_ID) & " AND [Service ID] = " & Str(Me.[Service ID]) & " AND [User Ended Service] =  " & Str(Me.User_Ended_Service)

DoCmd.Close
DoCmd.OpenForm "frmUsage", acNormal, , strlinkcriteria

Forms![frmUsage]![CmdReturnToServicesScreen].SetFocus

UseageExit:

Exit Sub

cmdAddUseageCriteria_Err:

If Err.Number = 94 Then

MsgBox "There is no service on this record. Please select another and try again"

Else

MsgBox "Error - " & Err.Description & Err.Number
DoCmd.OpenForm "frmServices"
Resume UseageExit

End If

End Sub
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

MyYesNo = MsgBox("This should only be used to delete errors!  If the user has ended the service please update this on the usage screen. " + vbCrLf + vbCrLf + "Do you still wish to delete this record?", vbYesNo, "Warning!")

If MyYesNo = vbYes Then
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Else
    'exis sub
End If

Exit_Command19_Click:
    Exit Sub

Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click
    
End Sub
Private Sub cmdUsersLook_Click()
On Error GoTo Err_cmdUsersLook_Click

    Dim stDocName As String
    Dim stlinkcriteria As String

stlinkcriteria = "[User ID] = " & Str(Me.User_ID)


    stDocName = "frmUsers"
    
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stlinkcriteria

Exit_cmdUsersLook_Click:
    Exit Sub

Err_cmdUsersLook_Click:
    MsgBox Err.Description
    Resume Exit_cmdUsersLook_Click
    
End Sub
Private Sub cmdUserupdate_Click()
On Error GoTo Err_cmdUserupdate_Click

    Dim stDocName As String
    Dim stlinkcriteria As String
    
    stlinkcriteria = "[User ID] = " & Me.User_ID
    
    If IsNull([User ID]) Then
    MsgBox "No User is selected!", vbOKOnly, "Warning!"
    
    Else
    

    stDocName = "frmViewUserDetails"
    DoCmd.OpenForm stDocName, , , stlinkcriteria

    End If

Exit_cmdUserupdate_Click:
    Exit Sub

Err_cmdUserupdate_Click:


    MsgBox Err.Description
    Resume Exit_cmdUserupdate_Click
    
End Sub

 

[mariewhitehead]

nobody??