Opening specific Access form from a cell in excel-CodePudding

I have been researching this and cant seem to find a solution that works. I am attempting to have a button in excel that when clicked opens access and opens a specific form. We keep all our machine setup sheets as forms in access.

How our operators use it:

When you want to find your form to print out to take to the machine, you open the access db and it automatically opens the form. You then hit "Toggle Filter" and then filter to the part number you are working with. They then print out that form.

How I want to use it:

I have a cell in excel with a part number. When a command button is clicked, it opens up the access db and filters for the specific form I want to view.

My current code is below:

Sub DisplayForm()

Dim StrDbpath As String
Dim PartNum As String


PartNum = Worksheets("CA Info Search").Range("A2").Value
StrDbpath = "F:\MACHSET\Setup Files.mdb"
Set appAccess = CreateObject("Access.Application")
With appAccess
    .OpenCurrentDatabase StrDbpath
    .Visible = True
    .DoCmd.OpenForm "Machine Setup Sheet"
    
    With .Forms("Machine Setup Sheet")
        .Filter = "[PART #:]='" & PartNum & "'"
        .FilterOn = True
    End With
End With
End Sub

EX. Part number I am testing with is 350-0158-000

When this is ran, the db opens and prompts me to enter parameter value for Part #:. I just hit enter and it takes me to a blank screen. If i double check the advanced filter to see what is being filtered, it says I am filtering Part #: by '350-0158-000' but the form is still blank

[1][Parameter Prompt] [1]: https://i.stack.imgur.com/6dEuc.png

[2][Blank screen after I hit enter]
[2]: https://i.stack.imgur.com/dexzK.png

[3][Advanced Filter]
[3]: https://i.stack.imgur.com/6eUsX.png

[4][Advanced filter showing filter value] [4]: https://i.stack.imgur.com/LnXN2.png

CodePudding user response：

Too much to put in a comment.

To continue from my last comment, you don't need to open the form again, just apply the filter. Try the below (I've no means to test it).

'...
Set appAccess = CreateObject("Access.Application")
With appAccess
    .OpenCurrentDatabase strDbPath
    .Visible = True
    .DoCmd.OpenForm "Machine Setup Sheet"
    
    With .Forms("Machine Setup Sheet")
        .Filter = "[PART #:]='" & PartNum & "'"
        .FilterOn = True
    End With
End With

Setting the .FilterOn = True property, will reset and reapply the filter.

CodePudding user response：

weird bug that goes away. the problems occur because the spaces in the form name and the with statement, but once I fixed those problems, I could put the spaces and with statement back. Hence, I couldn't verify the answer. assumptions you have a worksheet named CA Info Search. A2 is formatted general and that cell shows 350-0158-000. Also form is named Machine Setup Sheet and actually has a column named Part #: try this code:

Sub DisplayForm()
Dim StrDbpath As String
Dim PartNum As String
PartNum = Worksheets("CA Info Search").Range("A2").Value
StrDbpath = "F:\MACHSET\Setup Files.mdb"
Set appaccess = CreateObject("Access.Application")
appaccess.OpenCurrentDatabase StrDbpath
appaccess.docmd.OpenForm "Machine Setup Sheet"
appaccess.Forms("Machine Setup Sheet").Filter = "[PART #:]='" & PartNum & "'" 'need the apostrophes to indicate 350-0158-000 is a string
appaccess.Forms("Machine Setup Sheet").FilterOn = True
appaccess.Visible = True
End Sub

If the above code doesn't work, then remove the spaces from the form name and adjust the code accordingly. Replace all instances "Machine Setup Sheet" with "MachineSetupSheet". Then if the code works and you want the with statement and "Machine Setup Sheet", try adding them back in. Solution worked in Access and Excel 2016