CmdButton to open form and Filter

[d9pierce]

Hi all,
I have an unbound form called StartUp. this form only has cmdButtons
on it to open forms.

I need to create Cmd buttons to open a certain form and filter it to
meet criteria. An example of this is:

Private Sub CmdRetailClients_Click()
On Error GoTo Err_CmdRetailClients_Click

Dim stDocName As String
Dim LinkCriteria As String
stDocName = "Company_Main"
DoCmd.OpenForm stDocName, , , stLincCriteria

Exit_CmdRetailClients_Click:
Exit Sub

Err_CmdRetailClients_Click:
MsgBox Err.Description
Resume Exit_CmdRetailClients_Click

End Sub

this works fine to open the form but I need to add a filter or
something to fill a CboBox "CompanyType" with Criteria like
"Subcontractor" & "Retail Client".. which is text in this Cbo. I need
to create a different CmdButton for each record in this CboBox. I have
tried many variations but cant get it. any ideas would help.

Thanks so much,
dave

 

[Guest]

Hi Dave
Suggest you create a sub in the modules to open reports. In the code below
add

strLinkCriteria= Forms!formname.textboxname

(putting in the form and text box names above of course)

Then call the new sub in the modules called subOpenReports

subOpenReports(strDocName, strlinkCriteria)

Create a module. This is one I use

Function subOpenReports(strReportName As String, Optional strQuery As
String, Optional strWhere As String)
' This function is used in the Click event of command buttons that
' open reports on the Main Switchboards.

On Error GoTo Err_OpenReports

' Open specified report.
DoCmd.OpenReport strReportName, acPreview, strQuery, strWhere

Exit_OpenReports:
Exit Function

Err_OpenReports:
MsgBox Err.Description
Resume Exit_OpenReports

End Function

 

[d9pierce]

Hi Dave
Suggest you create a sub in the modules to open reports. In the code below
add

strLinkCriteria= Forms!formname.textboxname

(putting in the form and text box names above of course)

Then call the new sub in the modules called subOpenReports

subOpenReports(strDocName, strlinkCriteria)

Create a module. This is one I use

Function subOpenReports(strReportName As String, Optional strQuery As
String, Optional strWhere As String)
' This function is used in the Click event of command buttons that
' open reports on the Main Switchboards.

On Error GoTo Err_OpenReports

' Open specified report.
DoCmd.OpenReport strReportName, acPreview, strQuery, strWhere

Exit_OpenReports:
Exit Function

Err_OpenReports:
MsgBox Err.Description
Resume Exit_OpenReports

End Function













- Show quoted text -

Hi, I tried this and recieved an error:
MS cannot find form "Company_Main" refered to in a macro or VB code

Not real sure we are on the same page here with this one. I am not
trying to open a report, I am trying to fill a combo on a form with
data from that combo.

I will have cmdButtons on my unbound form like Clients,
Subcontractors, Malls... which is the data I have in this combo on the
bound form. I am looking for some code that when I click
CmdRetailClients, it opens the form "Company_Main" and just shows
Retail clients. Something like a filter. I could create a query for
each of these buttons but I believe there is a much simpler way to
just show one "CompanyType" in the combo. The purpose of doing this is
to open the form to a specific criteria so I can add my code to hide
certain data that doesnt apply to the callout that does or doesnot
relate to such as "Retail Clients". basically I want to make these cmd
buttons to open a form by "CompanyType" and show only records in that
Company Type so I can make some of the fields visable=False that dont
apply to this company type if you can understand.

thanks,
Dave

 

[d9pierce]

Hi again,
I can also see a new problem that will arrise when "AddNew" is used. I
will also need to figure out how to create a code on my form that when
I choose "Retail client" or "Subcontractor" from the Combo box, it
also alters a form to have certain fields visable or not visable. I
think this would be a combination of IIF arguements? Not real sure how
to do this either so if you could offer any suggestions I would be
most greatful!

thanks,
Dave d9pierce2mchsi.com

 

[Guest]

Hi Dave

Let me see if I understand this correctly. From what you say, you have 2
forms. The first form is an unbound form that operates as a menu. You can
select subcontractors or retail or Malls etc. When you make the selection,
you want to open the second (bound) form and display only certain information
that is dependant on the initial selection. As well, you want to display or
hide certain fields.

If that is the case this is what I would do.
On the unbound form, pass a parameter to the bound form using the openargs.
Also use the where parameter to specify the type. The example below is based
on a simple name file which has a person number and name.
Private Sub btnOpenForm_Click()
On Error GoTo Err_btnOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String ' The where clause
Dim strType As String ' The open argument

stLinkCriteria = "tblPeople.PersonNo=1"
strType = "Clients"

stDocName = "Form2"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , strType

Exit_btnOpenForm_Click:
Exit Sub

Err_btnOpenForm_Click:
MsgBox Err.Description
Resume Exit_btnOpenForm_Click

End Sub

When the second form opens, you use the where clause as a filter, and use
the openarg to decide which fields to hide or display. You can also use the
openarg to do things like set default values. If your client type is 1, and
subcontractors type is 2, you can set the type txtbox to have a default of 1
if the type selected is client.

Private Sub Form_Open(Cancel As Integer)
Dim strType As String
strType = Me.OpenArgs

If strType = "Clients" Then
Me.txtPersonNo.Visible = True
Me.txtName.DefaultValue = "J. Smith"
Etc.
End If

End Sub

I hope this helps.