Avatar of LamontZone
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
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

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?


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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Thanks for the feedback, I'm glad this helped. Success with your project! — (°v°)