Find records in subforms

[Guest]

Hi everyone, I have a question about using subform. I will be deeply
appreciated if any one could give me any suggestion as I have been searching
for a long time for this and just can not figure it out.

I am trying to set up a database for our special agreements with customers.
There are 4 tables: Customer detail, Special agreement 1, special agreement 2
and Product detail. Customer Name is the link between Customer detail,
Special agreement 1 & 2. Product name is the link for Product detail, special
agreement 1&2.

A form has created based on Customer detail and special agreement 1 & 2 are
the subforms. Now, I created a combo box based on the customer name so that
when a specific customer name is selected, agreement detail 1& 2 will show in
the subforms. However, even for one customer, there are different agreements
for different products. At this stage, all the agreements for each customer
are showing in the subforms and it looks really messy and not very easy to
view.

I am trying to find a way that I could make the database show the agreements
not only by selected customer name but also the selected product name as
well. Is there anyway I can do this?

Hope I explain this clear. Again, thank you very much for your help.
Best regards
Joanna

 

[Scott McDaniel]

So you have Customers, and those Customers have xx Agreements, based on a particular Product?

You can filter for the product like this:

With Me.NameOfYourSubformCONTROL.Form
.Filter = "ProductID=" & Me.YourProductComboBox
.FilterOn = True
End With

This would be done in your main form, and I'd probably move this to a Function call:

Function FillSubform()
'/if no customer or product, just exit
If Nz(Me.CustomerCombo,"")= "" OR nz(Me.ProductCombo,"")="" Then Exit Sub

With Me.NameOfYourSubformCONTROL.Form
.Filter = "ProductID=" & Me.YourProductComboBox
.FilterOn = True
End With

End Function

And then call this in the AfterUpdate event of your Customer and Product combos:

Sub cboCustomer_AfterUpdate()
FillSubform
End Sub

This assumes that your Main form and Subform are related on Customer ... so that when you select a Customer, the subform
automatically retrieves all records for a customer.

NOte that you'll need to change the names of tables/columns/controls, etc to match those in your database.

Also: Be careful when referring to Subforms. The correct syntax is:

Me.NameOfYourSubformCONTROL.Form.<some property or object>

NameOfYourSubformCONTROL is the name of the Subform Control object on the main form, and may or may NOT be the actual
name of the form being used as a subform.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[strive4peace]

LinkMasterfields and LinkChildfields
---

Hi Joanna,

If you will always want the subforms to reflect the customer, then use
the LinkMasterfields and LinkChildfields to limit subforms to a
particular Customer -- and I would recommend using CustomerID (make sure
it is ON both main and subform -- can have Visible=no)

to further limit the subform records, if you have a combo, for instance,
that shows product, you can also apply a filter as suggested by Scott.

I would make a slight modification to his code in case you want the
ability to show all products:

With Me.NameOfYourSubformCONTROL.Form
if not isNull(Me.YourProductComboBox) then
.Filter = "ProductID=" & Me.YourProductComboBox
.FilterOn = True
else
.Filter = ""
.FilterOn = False
end if
End With

~~~~~
LinkMasterfields and LinkChildfields:

click ONE time on the subform control if it is not already selected

click on the DATA tab of the Properties window

SourceObject --> this will be the form that is in the control

LinkMasterFields --> CustomerID
LinkChildFields --> CustomerID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname

where MainID (CustomerID) is an autonumber field (usually) in the parent
table and a Long Integer field in the child table

the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)

It is common to set the Visible property to No for the control(s) used
in LinkChildFields

then, while still on the subform control, click the ALL tab -- change
the Name property to match the SourceObject property (minus Form. in the
beginning if Access puts it there)

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.
the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks Scott. That is very nice of you!

I can find the records in subform by select customer names. However, after
select the customer name, I would like to have another combo box (on the same
form) to select product so that the subform can show the agreement for a
specific customer for a certain perduct. The problem is as the main form is
set based on the table-Customer, product is another table which linked with
agreement 1 & 2 by producte name but not linked with table-customer.

Can this be done by any chance?

 

[strive4peace]

Hi Joanna,

don't know if you saw the response I wrote or not ...

anyway, to answer your question ... I am assuming that you do have a
table that ties product with agreements? since agreements are what you
want to filter, that is your concern... and the Product combo box would
not be bound.

what is your table structure?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal:

I just saw your answer. Thanks so much for it. They are great help to me! As
I am a starter, would that be possible for me to ring you (or e-mail you
directly) to discuss?

Thanks!
Joanna

 

[strive4peace]

Hi Joanna,

you're welcome

here is something that may help you too:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming

If you want to find out about getting custom help, send me an email

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*