Ok, first we have too many things going too many directions.
What I will do is this . . . I will share with you what to do from start to
finish. We will act as though all you have built is the main form and the
pop-up form. We will act like no code has been written having to do with the
interactivity between these two forms.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
The assumptions:
1) The pop-up form is based on a query (if it isn't, make a query and change
the record source of the pop-up form to this query)
2) No code exists between the two forms
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
The Process:
1) edit the criteria of query behind the pop-up form. Under NetReq_SystemID
(or whatever the name of the field is relating the pop-up form to the Main
form) enter this criteria Forms!frm_System!SystemID (this criteria is
referencing the key field located on the main form)
2) on the main form - the only code you should have under the command button
is this -
Private Sub cmdNetReq_Click()
DoCmd.OpenForm "frm_NetRequirementsTab"
Call Form_frm_NetRequirementsTab.tabOperator_Click
End Sub
Get rid of all that other stuff.
3) add the code to each page in the tab control just like you sent me
Public Sub tabOperator_Click()
Me.Filter = "NetReq_NetTypeID = 1"
Me.FilterOn = True
End Sub
Private Sub tabMaintainer_Click()
Me.Filter = "NetReq_NetTypeID = 2"
Me.FilterOn = True
End Sub
Private Sub tabTrainer_Click()
Me.Filter = "NetReq_NetTypeID = 3"
Me.FilterOn = True
End Sub
4) Viola - it should work great for you!
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Stuff to get rid of
All the other code you included - get rid of it, delete those subprocedures.
Delete -
Private Sub Form_Load()
' Sets the new form data to relate to current system ID
Me!txtNetReq_SystemID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub
Private Sub TabControl_Change() ***Delete the entire subprocedure***
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Here's what we did
1) We gave the pop-up form a query as a record source. Because of the
criteria we added, this query can only show records related to the main form.
This was the problem you were having this time. Because you applied the
filter as I told you (to the pages in the tab control on the pop-up form) it
removed the filter applied when you ran your command button.
2) Since we added the filtered query as the record source for the pop-up
form we didn't need all that code on the command button. We just needed code
that said open the form and run some code on one of your tabs. So we deleted
all that other junk on the command button.
3) We are leaving the code on each page in the pop-up form alone. It is
good and will do its job.
4) Then we had the matter of all this other junk code which is a waste and
may be messing things up. So we deleted it.
5) It should work great; I think you will be pleased.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Next however, I can already hear you asking this question . . . If I change
records in my Main form I want the pop-up form to update to reflect the
records related to the new record I am on in my main form. If you decide to
go this route give me a holler and we will do it. It is falling off a turnip
truck simple.
If you have more questions email me at (e-mail address removed)
Don't type the three d's when you email me, I typed that so computers will
be less likely to harvest my email and start spamming me. You get the gist
of what the email address is. If you email me, I will email my phone number
and you can call if you like. My fingers are wearing down to nubs on this
problem. Sometimes 5 minutes of conversation can cover hours of typing.
Happy coding, you are almost there!
Seth
MaryF said:
Seth,
Thank you again for the very detailed response. I have added the code to
each page as you suggested:
Public Sub tabOperator_Click()
Me.Filter = "NetReq_NetTypeID = 1"
Me.FilterOn = True
End Sub
Private Sub tabMaintainer_Click()
Me.Filter = "NetReq_NetTypeID = 2"
Me.FilterOn = True
End Sub
Private Sub tabTrainer_Click()
Me.Filter = "NetReq_NetTypeID = 3"
Me.FilterOn = True
End Sub
As well as adding the Call command to the end of my command button code:
' Opens the Net Requirements form for that cooresponding systemID record
Private Sub cmdNetReq_Click()
On Error GoTo Err_cmdNetReq_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String
Dim NetReq_SystemID As Long
stDocName = "frm_NetRequirementsTab"
stLinkCriteria = "[NetReq_SystemID]=" & Me![SysID]
If DCount("*", "tbl_NetRequirements", stLinkCriteria) = 0 Then
strMsg = NewData & "There is no data for this system, would you like
to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, , Me!SysID
Case vbNo
Exit Sub
End Select
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Call Form_frm_NetRequirementsTab.tabOperator_Click 'the statement I just
added
Exit_cmdNetReq_Click:
Exit Sub
Err_cmdNetReq_Click:
MsgBox Err.Description
Resume Exit_cmdNetReq_Click
End Sub
This is the corresponding OnLoad code for frm_NetRequirementsTab for the
cmdNetReq_Click button:
Private Sub Form_Load()
' Sets the new form data to relate to current system ID
Me!txtNetReq_SystemID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub
Before I added this third layer (the TabControl) to my frm_NetRequirements,
the cmdNetReq and frm_NetRequirements Form_Load worked great.
I still have the code for the TabControl_Change:
Private Sub TabControl_Change()
Dim cnCurrent As ADODB.Connection
Dim rsNetReq As New ADODB.Recordset
Set cnCurrent = CurrentProject.Connection
Set rsNetReq = New ADODB.Recordset
On Error GoTo ErrorHandler
rsNetReq.Open "SELECT * FROM tbl_NetRequirements", cnCurrent
Select Case Me.TabControl.Value
Case 0 'Operator training
Me.Filter = "NetReq_TypeNetID = 1"
FilterOn = True
Requery
Case 1 'Maintainer training
Me.Filter = "NetReq_TypeNetID = 2"
FilterOn = True
Requery
Case 2 'Train the trainer
Me.Filter = "NetReq_TypeNetID = 3"
FilterOn = True
Requery
Case 3 'Other Training
Me.Filter = "NetReq_TypeNetID = 4"
FilterOn = True
Requery
End Select
rsNetReq.Close
cnCurrent.Close
Set rsNetReq = Nothing
Set cnCurrent = Nothing
ErrorHandler:
If Err.Number = 2105 Then
Resume Next
End If
Done:
End Sub
The problem: I do get the records filtered into the correct tab, but ALL of
the records in my database are now being viewed, not just the data for the
filtered SystemID.
(BTW, if I do not include the TabControl_Change sub (comment-it-out), an
input box "Enter Parameter Value" for NetReq_NetTypeID shows up. And if I
enter any number or just hit OK without entering data, no data is displayed
on the pages.)
Seth, what am I doing wrong? I am sorry I am making this difficult....I can
send you a shortened copy of my db if you think that would help.
Thank you again for everything,
Mary
:
Mary:
I would suggest the same method I shared with you before.
He is the generic gist. Each tab in a tab control is called a Page. If you
click on a page in design view and then look at the event properties you will
see an event named 'On Click'. This is where you would use the Filter code I
shared with you previously. Except this time you would change the actual
filter to filter records based on the field you are referencing in your
response.
Me.Filter = "NetReq_NetTypeID = 1"
Me.FilterOn = True
This assumes the name of field on the pop-up form that holds the value 1, 2
or 3 is NetReq_NetTypeID.
Then each time you click on a 'Page' in the tab control it will run a filter
for the respective NetReq_NetTypeID value for that page.
Remember, you need to add this filter for each Page in your tab control.
And within each Page you need to change your value that NetReq_NetTypeID is
equal to (Ex. 1, 2, 3 etc).
One final problem you will have after doing this is when you click your
command button on your main form the pop-up form will open and show all the
records. What you will need to do is call the 'On Click' procedure for the
default page of your tab control as the final code on your command button.
It should go a little something like this:
Existing code on the command button . . .
Call Form_frm_NetRequirementsTab.{Your Page Name Here}_Click
Then lastly, after deciding which Page is your default page, alter the On
Click event code by changing the word Private to Public. This is necessary
for you to be able to call the subprocedure from the main form, otherwise
Access will not find that subprocedure because it is Private, which means is
can only be seen and called from within that form's class module. And as you
can tell frm_System and frm_NetRequirementsTab are two different forms and
thus they each have their own class module.
Recap -
1) Add the filtering code to the On Click event of each Page on the pop-up
form
2) Determine which of the Pages on the pop-up form is the default Page
3) Alter the Private/Public of the On Click event of the Page that is the
default page
4) Add the code to the end of the command button procedure on your main form
This truly is very simple. Not knowing your skill level makes it difficult
to know how much detail to go into. Should you have additional problem feel
free to reply.
Good Luck, you are very close.
Seth
:
Hi Seth,
Thank you very much for your response. I did as you suggested and
unfortunately, I could not get it to work.
So I have changed the design. Instead of inserting the individual NetType
forms into each TabControl tab, I now have just copied the fields into each
tab.
I can now see the data and it filters to the correlating SystemID in the
main form. But in EACH tab, I get all of the filtered records. How can I
separate those filtered records to go into its correlating tab, ie.
NetReq_NetTypeID=1 into tab 0?
How do I write:
If NetReq_NetTypeID = 1 then
display the data in tab 0
Is it within the TabControl_Change event, and do I still use a case
statement????
Thank you again for all of your assistance and time.
Mary
:
If your situation is a straightforward as you say and you only use this
pop-up form for this one purpose you might consider setting the value of the
filter property for each of your respective forms (on the pop-up form) where
the filter references the SystemID field in your main form (SystemID =
Forms!frm_System!SystemID) and then when the pop-up form opens apply the
filter.
Here is some code out of one of my apps:
Me.Filter = "AccountNo = Forms!frmTransaction!cboAccount"
Me.FilterOn = True
I would place this code on the OnLoad event of the Pop-up form.
Happy coding,
Seth
:
Hello,
I have a tab control on a pop up form (frm_NetRequirementsTab) with 3 tabs:
Operator (NetReq_NetTypeID=1), Maintenance (NetReq_NetTypeID=2), and Trainer
(NetReq_NetTypeID=3). This pop up form is called from a command button
(cmdNetReq) on the main form (frm_System). Each tab pulls a record NetReqID
(PK) from the tbl_NetRequirements with the corresponding NetTypeID, that
matches to the main form's current SystemID record. The relationship is a
one to many, SystemID (PK) to NetReq_SystemID (FK). And there is a one to
many from the tbl_NetType (NetTypeID) to tbl_NetRequirements
(NetReq_NetTypeID).
What I want to do is open the pop up frm_NetRequirementsTab to display the
related records to the current main form record. For example, let us say the
current record in frm_System is SystemID=12. When I click the command button
it opens frm_NetRequirementsTab, it displays the tabcontrol with the training
information for system 12. And when I click the Operator tab (NetTypeID=1),
I see the data for the operator training for SystemID 12, with NetReqID=32;
when I click the Maintenance tab (NetTypeID=2) I see System 12's maint
training data with NetReqID=36, etc...
I created new forms for each NetType and then inserting the forms into each
corresponding tab of the TabControl on frm_NetRequirementsTab.
frm_TabNetReqOp 'Operator Training
frm_TabNetReqMaint 'Maintenance Training
frm_TabNetReqTrainer 'Train the Trainer Training
However, I cannot figure out how to get the pop up form to open to the
related record in the main form, and to display its correlating NetType data
in each tab. I have started the code for the TabControl below, but I am
really stuck. I have been working on this for weeks, and now very frustrated.
Can anyone please help? I hope I explained it clearly. Thank you in
advance for you assistance.
Mary
Private Sub TabControl_Change()
Dim curconn As ADODB.Connection
Set curconn = CurrentProject.Connection
Select Case Me.TabControl.Value
Case 0 'Operator training
NetReq_TypeNetID = 1
FilterOn = True
Requery
Case 1 'Maintainer training
NetReq_TypeNetID = 2
FilterOn = True
Requery
Case 2 'Train the trainer
NetReq_TypeNetID = 3
FilterOn = True
Requery
End Select
End Sub
