Tabbed Subform - Form Properties

[crhaines94]

Hello,



I have this Tabbed Subform, and each tab has a combo box that searches a unique form. Each tab is a product category. A simple example, each category would be: Toys, car parts, food, and clothes. I made forms for each category and then am making a master form that has tabs for each category. Then the combo box would allow you to search for each item in that category.

The problem that I am running into is that for tab 1 to work (say toys), I need to set the master form properties to the toys form. But if I go to tab 2 (Car parts), it wont pull up that form (car parts form) on that tab because it is trying to find the Toys form (although the combo box is correct).

How do I assign each tab its own record source so that way I can pull up each of the forms that I want on each tab.

Thanks!

Chris

[ranman256]

Instead of doing all that work,couldn't you make just 1 form,
Then pick a category from a combo?
The 1 form shows that data?

[orange]

Chris,

You may get some ideas from these video tutorials by Dr. Verschuuren. He has several Access and Excel videos that I have found to be good for concepts.

http://youtu.be/2M6TMwfc6i4 --Create Access Form with Subform
http://youtu.be/Ou1zwJunbuA --Access Subform based on Many to Many
http://youtu.be/8BZoR7JaKsY --Tabbed Access Form with SubForm

[crhaines94]

I see what your saying. But I want each page to only display 1 product. I believe your method, you would select a category and that would display all the products in that category. I need the form to display 1 product at a time.

I could create a secondary filter which would then populate lists for that category, but I am trying to make this extremely simple for the employees. I figured that having tabbed sub forms inside of 1 form would be the simplest for the users.

I have created a login screen on startup that limits what users can see, after a successful login, the master form (with all the tabbed sub forms) will open and that is all they can do or see. I had created buttons for them to exit and return to login and for them to print current record. But that is basically it.

It may not be possible to do what I want though correct? Making each tab have its our record source to pull the appropriate form?

[orange]

Perhaps I'm misunderstanding your last post. But if you have the subform Format Default View set to "Single Form", you may get what you're looking for. Easy enough to test it and see.
This would be current Main form, and 1 subform with default view set to Single Form. You'll get a record counter displayed to allow moving from 1 to all records in this related subform.

[crhaines94]

I checked, it was set to single form. I was really hoping it was that simple

I have attached screenshots of the subform properties and what it looks like in each tab in design view. You can see that the subform is pulling records from the mag and tinnery query, which will populate the tinner and mag tab (as shown in the design view). However, if we go to the aviation tab, the form is blank (in design view). Which is because the aviation form is pulling its data from the aviation query. So if the subform record source is tinner and mag query, it wont populate the aviation form in the aviation tab.

[orange]

You could set the recordsource of the Aviation Form to the Aviation query based on an Event.
See this post for some related info.

[crhaines94]

ok that makes sense! After looking at that post, I understand what hes trying to do, however, I do not know how to code that for my situation.

Here is where I need clarification ( I am fairly new to VBA, i appreciate you baring with me).

My Aviation Search tab is named AvSrch, so I would have: if me.AvSrch.value = 0 then

And here is where I get hung up. I know that I need to call the aviation form as the record source, but I dont know how to call that.

Once that is down, I would just copy that code for each tab.



Private Sub YourTabbedControl_Change()
If Me.YourTabbedControl.Value = 0 Then 'First Page
'Do code for Page 1
ElseIf Me.YourTabbedControl.Value = 1 Then 'Second page
'Do code for Page 2
ElseIf Me.YourTabbedControl.Value = 2 Then 'Third page
'Do code for Page 3
End If
End Sub

[davegri]

tabs-davegri-v01.zip
See if this helps.
Shows how to modify the rowsource of the search combobox depending on the tab selected.
Then the search combobox after_update event is modified to search the proper form.

[crhaines94]

Hi Davegri,

Thank you so much for setting that up.

I think that will work, I am testing it now.

I am currently running into to a bug (shown below, how do i bypass this?)

Also, I believe that your code should work even though you have all your data in separate tables and mine is in 1 data sheet (Master PIM). From that excel imported table, I created queries to separate the categories and then making forms for each category.

[davegri]

The code must refer to the CONTROL holding the subform. It may or may not be the same name as the subform.
Notice on the left, the selection is not for the subform, but the control holding the subform.

Edit also see syntax error. Need space after LIKE
strFilter = "Part Number Like " &

Edit Edit
Also need brackets around part number to account for space
strFilter = "[Part Number] Like " &

[crhaines94]

That is what I have

[crhaines94]

Here is the error that I am getting

[davegri]

See revised post#11.
Part Number needs brackets.

[crhaines94]

That Worked!! Well sorta. That fixed the error that I was having, on update however, the form comes up blank.

I think it has somthing to do with the tabcategory_change() code.

I dont know what to put in place of "Select Toys_PK, CPName from tblCarParts;"

You can see the code that I have. I try to present as much information as I can i these screenshots.