Filter a subform combo based on data from the main form (1 Viewer)

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
It seems like I am missing something in one of these codelings.
I get different errors depending on what I change in the Where section.
1.
Private Sub ClientID_AfterUpdate()
Dim SQL As String
SQL = "Select ClientID From ClientT Where ClientID=" & Me.[cboClientID]
Me.ProductReceiveSF.Form.RecordSource = SQL
Me.ProductReceiveSF.Form.Requery
End Sub

2.
Private Sub cboClientID_AfterUpdate()
Me![ProductReceiveSF]![cboProduct].RowSource = "SELECT ProductNAme, ClientCode, ProductCode, ClientName FROM ClientT INNER JOIN [ProductT].[ProductClientID]=[ClientT].[ClientID] WHERE ClientID=" & Me!cboProduct
Me![ProductReceiveSF]![cboProduct].Requery
End Sub

I have a main form and subform scenario and there are more than 5 tables involved. I am trying to filter the list of Products displayed in the subform based on the ClientID in the mainform. Right now its shows all Products from All Clients.
 

cheekybuddha

AWF VIP
Local time
Today, 15:28
Joined
Jul 21, 2014
Messages
2,237
Hi and welcome to AWF!

In number 2 you have:
... WHERE ClientID=" & Me!cboProduct

Is that what you mean? Should it be:
... WHERE ClientID=" & Me.Parent.cboClientID
?
 

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
cheekybuddha thank you for the reply
I tried your suggestion, though I still get a blank in my subform combo.
Also do i need to use the parent form name, I was of the understanding the ME. was the parent. But I tried both ways.
When I put me.addreceiveF.cboclientID I got an error saying cannot find the addreceivef field.

I have seen this type of code in other threads and I did try making the SQL sentence short (like in 1.) at which stage I tried using all possible combo's (excuse the pun) with the Where clause.

I also increased the bound columns and their width but all show up as blanks, its like the data is not being passed to the subform combo field.
I am absolutely sure that the subform name is correct and the combo names are correct.
 

cheekybuddha

AWF VIP
Local time
Today, 15:28
Joined
Jul 21, 2014
Messages
2,237
OK,

Are the procedures in 1 and 2 for different controls? (The first is for ClientID, the second for cboClientID)

When referring to subforms you must make sure you reference the name of the subform control. Confusingly, depending on how you added the subform to your form, this may or may not be the same name as the name of the form used as its SourceObject. You can find out the name of the subform control by clicking on its very edge and looking at what is displayed in the name property - but make sure your not looking at the properties for the subform!

From the limited description of your forms, I'm struggling to visualise which controls are on which forms.

Is the subform linked by Master/Child fields?
 

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
Have a look at the screen grabs
the first one shows the form and subform. the next one is the one field i am trying to use to control the contents of the sub form. the third is the field the main form controls.
for the 1. I removed the linkage. on 2 I retained the linkage which was automatic when i dragged the form onto the main form.
So the main form one use the Receive, client, carrier, shipper tables. sub form uses a Transaction table, linked via a receiveid in Receive & Transaction tables.
In query view I get to see all the fields populated correctly - no dupes. see sample 3.
As mentioned I have changed the stuff after the Where to many different fields/controls to no avail. I get a complete list or errors depending on what i included after the Where - lol - trial and error method.
I really appreciate the followup. hopefully we can knock this duck our of the water.
 

Attachments

  • SampleForm.jpg
    SampleForm.jpg
    274.5 KB · Views: 502
  • SAmple1.jpg
    SAmple1.jpg
    75.4 KB · Views: 495
  • Sample2.jpg
    Sample2.jpg
    130.5 KB · Views: 493
  • Sample3.jpg
    Sample3.jpg
    224 KB · Views: 318

cheekybuddha

AWF VIP
Local time
Today, 15:28
Joined
Jul 21, 2014
Messages
2,237
Hi,

Apologies for the delay in getting back.

You don't appear to have linked the subform ProductReceiveSF to the mainform via LinkMasterFields/LinkChildFields.

What is the RecordSource for ProductReceiveSF? If it is just a table name, what are the fields in the table?

This code seems off:
Code:
Private Sub ClientID_AfterUpdate()
Dim SQL As String
SQL = "Select ClientID From ClientT Where ClientID=" & Me.[cboClientID]
Me.ProductReceiveSF.Form.RecordSource = SQL
' ...
You are setting the RecordSource to just a single field. What about the others? With the proper linkage this code won't be necessary. I'm guessing it should link on ReceiveID from the mainform to an equivalent foreign key in the subform.
 

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
So SF record source is TransactionT. Pic attached.
SF Combo is cboProduct, control source is TransProductID, row source is blank (controlled by MF combo called cboClientID.
cboClientID is populated with alphanumeric data. when this box is filled with a ClientCode, this client code is used to Filter SF combo with the products of that client only.
In code 1. above the explanation was to remove the linkage, hence its blank. When I used that code.
in code 2. above the linkage is there when I tried that code.
I have tried both codes, as both purport to do the same job. I have been to many websites and I really don't remember where I got it from. But the asking party said it solved their problem - similar to mine.
So when the code is run (after update) I was hoping that the SF would get
TransactionT.ReceiveID populated with the ReceiveT.ReceiveID
The TransactionT.TransProductID limited by the ClienID selected in the MF.
 

Attachments

  • Sample4.jpg
    Sample4.jpg
    62.8 KB · Views: 478

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
Private Sub ClientID_AfterUpdate()
Dim SQL As String
SQL = "Select ClientID From ClientT Where ClientID=" & Me.[cboClientID]
Me.ProductReceiveSF.Form.RecordSource = SQL

Basically I am of the understanding that this code will
1. update the combo of the SF with clientID and hence clientcode. I have replaced Client with Product and I have tried making a very complicated Select using 5 tables and joins. I test the select statement out before copying it to the string, then removing the last part so that I could include "& Me.[cboclientid]" into the SQL= string.
 

cheekybuddha

AWF VIP
Local time
Today, 15:28
Joined
Jul 21, 2014
Messages
2,237
Hi,

Looking at what you have posted above, try removing the code in ClientID_AfterUpdate().

Then, in the property sheet for subform ProductReceiveSF put [ReceiveID] in both the LInkMasterFields and LinkChildFields.

This should filter the subform recordset appropriately for whichever record is selected in the main form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 19, 2002
Messages
42,970
Why are you not using the master/child links as cheekybuddha recommended? When these are properly set, Access will keep the main form and subform in sync automatically.

When you add a subform to a form, Access AUTOMATICALLY sets the master/child links for you IF you have defined a relationship between the two tables in the Relationships window. If you don't have a defined relationship, you can still make the link but you have to remember to do it yourself.
 

accesskiddo

New member
Local time
Today, 11:28
Joined
May 3, 2021
Messages
6
Hi again, thank you so much for taking the time cheekybuddha and Pat Hartman.
Unfortunately it did not work. I got a list of all products from all clients.
I deleted the event in After update. Put ReceiveID in the Linkage Master/Child.

There are 7 tables involved with this form. MF - Client, Carrier, Shipper, Receive & SF - Product, Transaction, TransactionType.
In the SF (sample2 above) I am using TransProductID as the control source for Product. and this statement for the row source
SELECT ProductT.ProductID, ProductT.ProductCode, ProductT.ProductName FROM ProductT;
Maybe this statement is the problem.
If you need more info I would be happy to oblige. And again I appreciate the help.
 

Attachments

  • Sample5.jpg
    Sample5.jpg
    103.6 KB · Views: 369

Users who are viewing this thread

Top Bottom