Solved: Show Subform based on two Fields

[brorick]

Hello. I would like to open a subform based on two fields on my main form. All my subforms would have the name convention, subform_ID#_State. So, when I click on a button on my main form I want the subform to show based on the ID # in the ID field and State in the state field.

If the fields contain the following data then the subform_22_TX will show.
ID#: 22
State: TX

Any ideas how I can get this to work. : pray2:

[geekgirlau]

If Nz(Me.ID, "") <> "" And Nz(Me.State, "") <> "" Then
Me.SubFormControl.SourceObject = "subform_" & Me.ID & "_" & Me.State
Else
Me.SubFormControl.SourceObject = Null
End If

[brorick]

It worked!! You are awesome. Thank you Geekgirlau! :bow:

[brorick]

My original thought was that I would link the code to a button. Is it possible to have it automatically show.

Example: Each time I click to view the next record on the form, only if the ID # and state field are populated then the corresponding subform will show and if not the subform will remain invisible. What event on the form would I place the existing code as it is to accomplish this?:mkay

[Norie]

How many subforms do you have?

What's the difference between them?

[brorick]

The code that geekgirlau provided works perfectly because it allows me the flexibility to pull the various subforms based on the ID# and State fields. I figured it would be a much more efficient form if the main form automatically showed the subforms rather than the user having to click on a button to view the form.

The data within in the subforms do vary and there is no common denomenator. That was why I decided that the ID# and State field would be the key indicators to identify the appropriate subforms. I hope I answered your question.

[Norie]

If it's only the data changing in the subforms, why do you have multiple subforms?

[brorick]

I should state that the subforms are based on query results and because each query pulls from different tables the field names are different from one subform to the next. Each subform contains different field names and data. Sorry for the confusion.

[Imdabaum]

My original thought was that I would link the code to a button. Is it possible to have it automatically show.

Example: Each time I click to view the next record on the form, only if the ID # and state field are populated then the corresponding subform will show and if not the subform will remain invisible. What event on the form would I place the existing code as it is to accomplish this?:mkay


I would put a bit of code in the AfterUpdate Event for both text fields.

The ID field.AfterUpdate would check to see if the State text field is populated. And check the ID field for the StateField.AfterUpdate. This way you allow users the flexibility of entering info in any order. Unless you want to get complex and lock the state text field until they have an ID... but the code isn't that difficult you just put geekgirl's code in an AfterUpdate and put something like this around it.


If (NZ(Me.IDfield)>0 AND NZ(Me.StateField) >0) Then
Execute geek's code.
Else
' do nothing and delete the else statement and simply End If
MsgBox "Identify State" 'Not necessary and possibly 'overbearing and annoying.
End If



Umm just read the other line... and well now I'm confused so these text fields change depending on the query and you're modifying the .Visible property or do the queries populate the same fields just with different data dependant on the queries?

[brorick]

The main form is based on a table. The subforms are based on a query. All the ID# and State field do is help identify which subform to show. There is no other direct link between the main form and the subform. I will give your code a try. Thank you.

[Imdabaum]

Ok, then the code I wrote should be fine... should being the key word.

[brorick]

I tried in the afterupdate field and it worked. I think the other scenerio I need to consider is what if the data has already been prefilled in on a previous date. Therefore, when the user views the form they would not necessarily update the field because the data is already there. Therefore placing the code in the after update event doesn't seem to do the trick.

I would imagine I need the code to be in a place that automatically looks at the fields and readjust the subform accordingly each time a user moves to another record in the main form. Any other thoughts?:dunno

[Imdabaum]

Did the code not work for the scenario where users update the ID and state fields? Or did it just plain not work?

[brorick]

I am sorry, I modified my original response. It worked in the afterupdate event for the fields. Unfortunately when I move to the next record that might not contain data in the ID# or State fields the subform from the previous record is still visible. I know that there must be sum way to almost requery the form each time I move to the next record so that the correct subform will appear or disappear based on the data or lack therefof in the ID# or state field.

[Imdabaum]

Place the same code you used for the AfterUpdate event and go to the actual Form events and select OnCurrent-->Code Builder. Put the same code there. Now in some of my programming classes they say that if you can write the same code more than once it should be a function/sub. So if you want to create one sub or function that makes that check


Sub [name] (ID as String, State as String)

If Nz(Me.ID, "") <> "" And Nz(Me.State, "") <> "" Then
Me.SubFormControl.SourceObject = "subform_" & ID & "_" & State
Else
Me.SubFormControl.SourceObject = Null
End If
EndSub




The only difference would be that your AfterUpdate and OnCurrent code would then look like this. In the end 15 lines to just paste Geekgirlau's code into the threee events. 10 lines if you make a function and call the function from the events. I'm sure it makes a big difference..... not really.




Call [name]



The easy way would just to be paste the code in the three events. But some true coder might argue.

[brorick]

Thank you. I will give it a try.

[brorick]

It worked!! That did it for me. A big thanks to geekgirlau and Imdabaum for providing me with options that both worked. You guys rock. :clap: :bow:

[geekgirlau]

Sorry I haven't gotten back to you earlier, but it looks like you were in good hands with Imdabaum!

Just to reiterate, I would recommend that you create a new procedure in your form with the code as follows:



Sub SetSubForm()
If Nz(Me.ID, "") <> "" And Nz(Me.State, "") <> "" Then
Me.SubFormControl.SourceObject = "subform_" & Me.ID & "_" & Me.State
Else
Me.SubFormControl.SourceObject = Null
End If
End Sub



Then in the AfterUpdate event for the ID and State fields, add the following:

Call SetSubForm
OR just
SetSubForm

You also need to call this procedure again in the OnCurrent event for the form, to cope with situations where you are not changing the value of either of those fields.

[brorick]

Geekgirlau thank you again for the additional information. I appreciate your continued help. :friends:

Does anyone know why when I added the following two pieces of code I get a parameter window that pops up asking me for the Me.CityID. This field does exist on my Main form and my subform.


Me.subformcontrol.LinkChildFields = Me.CityID
Me.subformcontrol.LinkMasterFields = Me.CityID


The referenced subform contains the CityID and I want to sync the subform with the main form based on the CityID. I placed it right after the reference to this code.


Me.SubFormControl.SourceObject = "subform_" & Me.ID & "_" & Me.State


What am I doing wrong? :doh:

[Imdabaum]

I've never actually used the LinkChild/ LinkMaster properties... but maybe I should learn what they are so I can. Sounds useful. I'll get back to you.

[brorick]

I figured it out. :thumb For whatever reason I have to list the field on the main form in quotes. Therefore my code should read as follows.



Me.subformcontrol.LinkChildFields = "CityID"
Me.subformcontrol.LinkMasterFields = "CityID"


Now it works with no issue. Thanks again for helping.

[Imdabaum]

Thanks for the training... Just out of curiosity. What are those properties used for? Does it filter the subform according to the main form?

[brorick]

Hello Imdabaum. Whenever you add a subform to a main form you can identify a common field between the two forms. The common link allows Access to display related records between the two forms. The link between the two forms is the LinkChildFields and LinkMasterFields. In my case I used the CityID to synchronize the two forms. I hope this helps.

[Imdabaum]

Yes that is very helpful. I can use that on my subform rather than passing in parameters in the open function I created.