Show certain records only on subform

[John_G]

OK - you didn't tell us that! It's an unusual arrangement, but perfectly valid. I would still leave that relationship between customers and products out, though - while it is valid, it just confuses the issue.



Back to the problem we started with.

I think all you need to do is reset the source for the product combo box, in the On Current event of the subform:

Me!comboboxname.recordsource = "Select fieldlist from products where CustomerID = " & me.parent.CustomerID
me!comboboxname.Requery

replacing the italics with the actual names you are using.

I know that uses VBA, which you didn't really want to do, but I don't see any other option in this case.

John

[JayGee1969]

Thanks John,

I have tried = Me!ProductCode.recordsource = "Select ProductID from products where CustomerID = " & me.parent.CustomerID
me!ProductCode.Requery

The comboboxname is ProductCode and I assume you mean by fieldlist the field where the values are stored i.e. ProductID, but when I put this in I get the following error:
The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

Which bit have I misunderstood?

[John_G]

Hi -

How and where are you using this statement :

= Me!ProductCode.recordsource = "Select ProductID from products where CustomerID = " & me.parent.CustomerID

Is the "=" highlighted in red a part of it? If so, that is the problem. This statement and the requery have to be in a vba module.

Another place you could put this code is in the After Update event of the customer selection combo box on the main form, something like this:

me.subformcontrol.form.productcode.recordsource = "Select ProductID from products where CustomerID = " & me!CustomerID
me.subformcontrol.form.productcode.requery

where subformcontrol is the name of the control which contains the subform on the main form, not the name of the subform itself.

John

[JayGee1969]

Hi John,

I still do not seem to be getting anywhere with this - as I said before I am fine with the basics of Access but when it comes to programming I get a bit stuck. I am not entirely sure what you mean by 'the name of the control which contains the subform on the main form, not the name of the subform itself' as I cannot see the difference.

Can I be really cheeky and ask if there is a way I can send you a link to the database and you could maybe take a look and see if you can see where I am going wrong. From what you have put it looks like it should be something quite simple, but I am just not quite grasping it. Many thanks,

[Rainlover]

I have a database in which I have a customer order form containing the customers details in the parent form and the products ordered in the subform. As each customer orders from a different set of products I want only to see the products associated with that customer when I click on the subform. I have created a query that will filter out those products for each customer, but when I open up the customer order form it still shows all the products in the products table in the subform. As I select the costumer from a combo box in the main form I want it to automatically apply the same choice to the list of products in the relevant combo on the subform.

Please give your answer in very simple terms because I have no programming knowledge. Thanks,

You should be able to build a Query with the Customer Details including the CUSTOMER ID for the Main Form.

You should also be able to build a Query with the Products Ordered including the CUSTOMER ID for the Sub Form.

Link the Sub to the Main using Master and Child fields of Customer ID.

You need NO CODE. Just follow what I said and it WILL work.

[Rainlover]

You can post a copy of your Database if you want.

Just convert it to Access 2003 before posting.

[John_G]

Hi -

" I am not entirely sure what you mean by 'the name of the control which contains the subform on the main form, not the name of the subform itself' as I cannot see the difference."

When you create a sub-form in a main form, Access creates a "container" for it on the main form. This "container" is a Subform/subreport control. Access gives it a default name when it is created, and you select the sub-form that is to go into that control. To see the name of the control, click the sub-form once to select the control, then press F4 to open the properties box - the first property (Name) is the name of the control, and the second property (Source Object) is the name of the sub-form you specified.

Repeating for convenience:

me.subformcontrol.form.productcode.recordsource = "Select ProductID from products where CustomerID = " & me!CustomerID
me.subformcontrol.form.productcode.requery

where subformcontrol is the name of the control which contains the subform on the main form, not the name of the subform itself.

The red form above is generic in a sense, meaning "whatever sub-form is currently in that control"

Hope this clarifies things for you.


John

Now, if you want to refer to some aspect of the sub-form using VBA, you refer to the subform control

[JayGee1969]

https://dl.dropbox.com/u/31656102/Database.mdb

I think I am getting out of my depth here - the more I look at this the more confused I get. The database does everything else I need it to but I am afraid that if I mess with it now I will stop it performing the other functions I need it to. The link above should open a copy of the database in Access 2003 - if either of you can take a look and either give me really clear instructions or better still just amend what needs amending I would be really grateful.

[Rainlover]

JayGee

You have a few problems with your basic Table designs. Things like Captions and lookups in Tables are misleading as you are now starting to discover. You could do with some proper naming Conventions. Keeping things in a standard format helps.

You have code that does nothing. You should have the Declaration of Option Explicit on every Module. Some correct spacing and indentation helps with readability.

You use some code for Dirty which is totally unnecessary. You should always get an idea of what the code does before using it, rather than just puting it there because you saw it somewhere.

The use of AutoNumber as the Primary key is good. You should use it on all Tables.

Sorry I haven't told you what you want.

[JayGee1969]

Apart from the above do you think you are likely to be able to tell me how to solve my specific problem?

[Rainlover]

I already gave you the answer in post # 20.

Did you do anything along those lines.

I can honestly say that it is very rare for me to use code to link a Sub Form to a Main Form.

EDIT

I did not go back and reRead the problem.

You can build the SQL for your Combo using a query from the properties box. Others have given you the answer but you have a syntax problem that needs fixing. By building a query you should be able to fix that.

When you change records on the Main Form you will need to Requery the Combo.

[Rainlover]

I downloaded your database but it kept crashing in design.

I found it difficult to know which form was what and what was its sub form. If a Main form is called frmClient then the sub should be something like frmClientSub. It keeps them together and makes it easier to follow.

[JayGee1969]

OK, bear with me here - I think you need to lay this out for me like I am really thick - which I appear to be in this instant.

I have renamed my forms, so hopefully it should be clearer which ones are which.

'frmCustOrd1' is the main form and 'frmCustOrd1Sub' is its subform. 'frmCustOrd1' is based on the query 'OrdersQry' and 'frmCustOrd1Sub' is based on the query 'OrderDetailsQry'. Both queries contain the field CustomerID as you referred to in #20 and are linked using Master and Child fields of Customer ID - this was already the case, I have not changed anything here.

I do not understand what you mean by "You can build the SQL for your Combo using a query from the properties box. Others have given you the answer but you have a syntax problem that needs fixing. By building a query you should be able to fix that.

When you change records on the Main Form you will need to Requery the Combo."

How do I sort the syntax problem if I do not understand how to build the SQL? What does it mean to 'Requery'?

As my forms are more complex than simply linking the customer to the products ordered I obviously need more fields in these queries which maybe complicates the problem. Also, as everything else in the database works the way I need it to I am afraid to change too much ie in the relationships etc for fear that it may then stop something else working which already works ok.

[Rainlover]

Requery means to refresh your data. In the situation where something has changed Requery will give the latest Data. Google for more info.

Suggest you create a new Database and import ONLY the forms queries and tables required to demonstrate this problem you have.

Don't forget to convert to Access 2003.

Test as much as you can before posting.

[Rainlover]

It has been a while since you posted. I assume you have solved the problem. Is it time to tahe you off my subscribed list?