Full Version: Open Forms From Listbox Based On Specific Criteria
UtterAccess Forums > Microsoft® Access > Access Forms
2ME
Hi,

I have a listbox based on a table contains forms name
I use the below code to define the criteria but the problem now, that I have one of the forms based on different query so I get parameters when selecting that form
CODE
Private Sub Printt_Click()
    On Error Resume Next
Dim sCriteria As String
Dim stDocName As String
    stDocName = Me.Printt.Column(2)
sCriteria = " 1 = 1 "
If Len(Me.DF & vbNullString) = 0 Or Len(Me.DT & vbNullString) = 0 Then
MsgBox "Define the period .", vbOKOnly, "Hi-Tech SysAdmin"
Exit Sub

    ElseIf Not IsNull(Me.Macdepid) Then
        sCriteria = sCriteria & " AND " & "MachinePerformance2.[Macdepid] = " & Me.Macdepid & ""
End If
      If Not IsNull(Me.MachineID) Then
        sCriteria = sCriteria & " AND " & "MachinePerformance2.[machine_id] = " & Me.MachineID & ""
End If

    If Not IsNull(Me.ProductID) Then
        sCriteria = sCriteria & " AND " & "MachinePerformance2.[Product_ID] = " & Me.ProductID & ""
                
    End If
        DoCmd.OpenForm stDocName, , , sCriteria
End Sub

so how can I bypass the paramters when opening the form

any suggestion would be appreciated
MadPiet
test for the exception at the top of the code and skip it?
If frm.Name = "notThisForm"
THEN 'Do Nothing'
ELSE...
2ME
The if condition doesn't solve the problem
Any suggestion would be appreciated
GroverParkGeorge
If I understand correctly, you MUST either provide the required parameters for the second form's recordsource, or use a different method.

"... one of the forms based on different query..."

I assume the parameters of concern are in that different query? If so, you can pass those parameters to it when you open it, or make a second version of that query to use that does not require the parameters.

But that begs another question, if you don't provide parameters to it, will the form open with the desired recordset?
2ME
Hi, George,

The criteria in the code to open a form in list box
When I double click the form the form opens without any issue as all the parameters mentioned in the code

When I double click another form which doesn't require a criteria I get parameters pop up
GroverParkGeorge
This is not clear.

You have two different forms. One is based on a query with parameters. One is based on a query without parameters. Is that right?

Which opens correctly?
2ME
The form with parameters opens correctly

The form that doesn't require parameters asks me for the same parameters included in the code
GroverParkGeorge
Reviewing the originally posted code again, it seems to me that Pieter's suggestion should have worked, although you report that you tried it and didn't get the result you wanted.

If you don't want to pass the full criteria to this one form, then using If to check for it, and only adding the additional criteria otherwise, should do what you want.

In other words, if you check for the form name immediately after selecting it here:

stDocName = Me.Printt.Column(2)

Then only start adding criteria if that form is NOT the one selected, you should end up here:

DoCmd.OpenForm stDocName, , , sCriteria

with sCriteria being either a ZLS or " 1=1", which would otherwise work, I believe.

So, follow that suggestion, but use the IF clause to bypass all of the places where you assign criteria, except for the " 1 = 1 "


2ME
Could you please help me modifying the code to implement your point of view?
GroverParkGeorge
What have you tried so far?

Here's what I might try:

CODE
Private Sub Printt_Click()
    On Error Resume Next
    Dim sCriteria As String
    Dim stDocName As String
    sCriteria = " 1 = 1 "
    stDocName = Nz(Me.Printt.Column(2), "")
    If stDocName <> "TheNameOfTheFormYouWantToByPassGoesHere" Then
        If Len(Me.DF & vbNullString) = 0 Or Len(Me.DT & vbNullString) = 0 Then
             MsgBox "Define the period .", vbOKOnly, "Hi-Tech SysAdmin"
             Exit Sub
         ElseIf Not IsNull(Me.Macdepid) Then
             sCriteria = sCriteria & " AND " & "MachinePerformance2.[Macdepid] = " & Me.Macdepid & ""
         End If

         If Not IsNull(Me.MachineID) Then
             sCriteria = sCriteria & " AND " & "MachinePerformance2.[machine_id] = " & Me.MachineID & ""
         End If

         If Not IsNull(Me.ProductID) Then
             sCriteria = sCriteria & " AND " & "MachinePerformance2.[Product_ID] = " & Me.ProductID & ""  
        End If
    End If

        DoCmd.OpenForm stDocName, , , sCriteria

End Sub
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.