Link to home
Start Free TrialLog in
Avatar of LamontZone

asked on

Open Form event won't filter on Network Shared Server drive

Using an opening dialog I open a form filtering to a specific record or to add a new record. The code works fine in a test environment on a single machine but when moved to a shared server location, the code is ignored and the form is opened to the first record. Also, using the Access Switchboard for navigation, using the 'Open Form Add' command (2) is ignored and the form opens in 'Open Form Browse' (3) mode at record 1. What am I missing?
The code in the opening dialog form is:

Private Sub cmdNew_Click()
On Error Resume Next
    DoCmd.OpenForm "frm_MainVendors", , , , acFormAdd
End Sub

Private Sub View_Click()
    If IsNull([cmboVendorName]) Then
        MsgBox "You must select a vendor."
        DoCmd.GoToControl "cmboVendorName"
        Me.Visible = False
        DoCmd.OpenForm "frm_MainVendors", , , "[SupplierID]=" & "Forms![fdlg_SelectVendor]![cmboVendorName]"
    End If
End Sub
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Sorry to have to say this but the code has never worked because it is incorrect.
So whatever you have had working correctly it wasn't this.

If supplierid is a number this line should be..

 DoCmd.OpenForm "frm_MainVendors", , , "[SupplierID]=" & Forms![fdlg_SelectVendor]![cmboVendorName]

and if it is text it should be..
 DoCmd.OpenForm "frm_MainVendors", , , "[SupplierID]='" & Forms![fdlg_SelectVendor]![cmboVendorName] & "'"

That's a bit harsh, no? Of course the code can work: you can set a filter referring to an open Access object, what's wrong with that? Especially as the criteria form remains open (it's only hidden, not closed). Incidentally, if you replace it with a literal, you can also remove the prefix “Forms![fdlg_SelectVendor]!”, as the combo box is obviously on the form where this code is run.

I'm wondering what this means, though: when moved to a shared server location?

Avatar of LamontZone


In response to Peter57r, the code does work and the exact same formatting works for other forms open events in the same database. I have been using this same technique for over 8 years. What about the add new record code, that doesn't work either on the network.  The [SupplierID] on the form is a number value and the [cmboVendorName] object on the opening dialog form is also a number value.

In response to harfang, the database is developed on a single PC in a Windows 7 environment. When moved to a network server however and accessed by users locally or via RDP, the code is ignored and the form is opened in browse mode to the first record in the dataset. When run locally,the code is fine. If I copy the application off of the server back to a single machine it works fine. It only fails when on the shared server drive.
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How can the code work ?
The parameter (form reference) is within "".

The SuppierID value will be tested against a text string of  "Forms![fdlg_SelectVendor]![cmboVendorName]"
OK - I get it.
Made a wrong assumption about what the code was trying to do.
My apologies.
To hartag: I am accepting your solution with many thanks. I haven't solved the problem but your reminder to trap for the error pointed me in the right direction. I am so used to the code working that I tend to skip error trapping if I am comfortable. The issue is that I am using VBA code purchased and licensed from Database Creations to manage the application rather than via workgroup et. al. In a test environment I turn it off and then turn it on when uploading to the server. For this particular form,  with frmSecure set to On the code is ignored. with frmSecure set to Off it works. I now realize my problem is in the frmSecure code in that module with the current form.
Many Thanks
Thanks for the feedback, I'm glad this helped. Success with your project! — (°v°)