Open Form in Sub form instead of window

[Jakboi]

Hello,

I have a search function for my database. I put in the search criteria and matching criterian open in a new window. I would like to add a sub form to this and have the results open in the same form...if that makes sense.

Here is the search form:

Private Sub Command12_Click()

If (IsNull(cboType)) Or (IsNull(txtCriteria)) Then
 MsgBox "Please select search type", vbOKOnly, "please select search type"
 ElseIf [cboType] = "ShortAccountTitle" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmShortAccountTitle"
 ElseIf [cboType] = "ContactType" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmContactType"
 ElseIf [cboType] = "AccountNumber" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmAccountNumber"
 ElseIf [cboType] = "DateEntered" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmDateEntered"
 ElseIf [cboType] = "EnteredBy" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmEnteredBy"
 ElseIf [cboType] = "InitialContact" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmInitialContact"
  End If
 
End Sub

Thanks for any direction.

 

[JonathanAnon]

What I usually do is to design it as follows.

On the left you will see "search by" then you have a field to whether to search by name, location etc etc. The list box underneath's rowsource is a query which has the Search string field as a parameter.

When you press the search button, it requeries the list box, which re-runs the query that it is based on. It then displays the fields that you wish to display in the list.

 

[Jakboi]

Thanks for the response and nice setup btw. See when I use the search function in the "before" picture it opens each form as the code says in a new pop up or tiled window.

I would like the form to actually show up below the search form. I am not sure how I would make the sub form box below, if thats what I should use to do this.

I have seach criteria and if it matches then I have add data. Its hard if it is being tiled or is in the new window. Any suggestions on how I can have this open up below the search function instead of a new window would be appreciated. Thanks.

Where the box is or the subform on the after picture is where I would like the results to show.

 

[rainman89]

What if.... and im just saying if.... you make the subform visible instead of trying to open it up?

that is have all the subforms there and if the criteria is met then make that subform visible... etc

 

[JonathanAnon]

Thanks for the response and nice setup btw. See when I use the search function in the "before" picture it opens each form as the code says in a new pop up or tiled window.

I would like the form to actually show up below the search form. I am not sure how I would make the sub form box below, if thats what I should use to do this.

I have seach criteria and if it matches then I have add data. Its hard if it is being tiled or is in the new window. Any suggestions on how I can have this open up below the search function instead of a new window would be appreciated. Thanks.

Where the box is or the subform on the after picture is where I would like the results to show.

Regarding the interface, I always the background in Paint Shop Pro, drop it in to the form and then "Send To Back". Looks MUCH more professional than the Access interface.

tbh, I'm not good on using subforms, I dont use them. There are two ways that I would handle this
a) The way I have done so in my previous post is my preferred way, as you dont have to pass variables between forms.
or
b) have the results come up in a separate popup form.

Think you need to decide HOW you want it work first, and then code based on that.

 

[Jakboi]

that is have all the subforms there and if the criteria is met then make that subform visible... etc

I think that is what I am trying to do. I think I am trying to find a way to do it like this:

If search criteria "ShortAccountTitle" is used then show subform "frmShortAccountTitle", etc. for each of the search criteria. However I am not sure how I could make that happen.

This way when I try to search the 3000 or so records for different criteria the results would open in the subform, instead of a form by itself.

Right now when using the search, the results open up in their own form, but trying to figure how to have them open up in a sub form depending what criteria is used.

I am not sure if I am making this more difficult then it should be or if there is an easier way.

Thanks.

 

[rainman89]

Private Sub Command12_Click()

If (IsNull(cboType)) Or (IsNull(txtCriteria)) Then
 MsgBox "Please select search type", vbOKOnly, "please select search type"
 ElseIf [cboType] = "ShortAccountTitle" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmShortAccountTitle"
 ElseIf [cboType] = "ContactType" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmContactType"
 ElseIf [cboType] = "AccountNumber" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmAccountNumber"
 ElseIf [cboType] = "DateEntered" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmDateEntered"
 ElseIf [cboType] = "EnteredBy" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmEnteredBy"
 ElseIf [cboType] = "InitialContact" And [txtCriteria] > "" Then
  DoCmd.OpenForm "frmInitialContact"
  End If
 
End Sub

in this code you would chane the docmd.openformname to

me.subformname.visible="true"

 

[Jakboi]

Ok so like this for each one replacing the form name:

me.frmShortAccountTitle.visible="true"

Do I have to do anything for the subform area? This is the code for the search button, but does the subform need anything?

Thanks alot you have been great help.

 

[rainman89]

No. just make sure its default visible property is set to no

 

[Jakboi]

Thanks. I have been trying to solve something on my own but have not been able to. With this above I get this:

Compile Error : Method or data member not found.

This is what I have:

Private Sub Command12_Click()

If (IsNull(cboType)) Or (IsNull(txtCriteria)) Then
 MsgBox "Please select search type", vbOKOnly, "please select search type"
 ElseIf [cboType] = "ShortAccountTitle" And [txtCriteria] > "" Then
  Me.frmShortAccountTitle.Visible = "true"
 ElseIf [cboType] = "ContactType" And [txtCriteria] > "" Then
  Me.frmContactType.Visible = "true"
 ElseIf [cboType] = "AccountNumber" And [txtCriteria] > "" Then
  Me.frmAccountNumber.Visible = "true"
 ElseIf [cboType] = "DateEntered" And [txtCriteria] > "" Then
  Me.frmDateEntered.Visible = "true"
 ElseIf [cboType] = "EnteredBy" And [txtCriteria] > "" Then
  Me.frmEnteredBy.Visible = "true"
 ElseIf [cboType] = "InitialContact" And [txtCriteria] > "" Then
  Me.frmInitialContact.Visible = "true"
  End If
 
End Sub

Does anyone know where I can find more information on the "Me" object?

 

[Jakboi]

I have attached a database I am working on in which I am trying to get the results to show in the bottom of the form, instead of a new window or popup. In the customer search form I want to have the results show up in the sub form below it named "Child48".

If anyone can be of assitance that would be great.

 

[rainman89]

here. take a look at what i did. maybe that will give you a start. i placed all the forms on the main form and set them visible=false. then based on whats selected i made the appropriate form visible

 

[Jakboi]

Oh I see. I am pretty new to this so I really appreciate the help. Thanks alot.