Form - Subform (1 Viewer)

[moi]

Hello all,

Is there a way to limit the row of a subform? its only one record per buyer (sort of additional info/detail of the buyer).

Also, in the view (show all record), i wanted to hide the subform if no record, how to do that.

Thank you.

 

[arnelgp]

use Query:

from ChatGPT:

SELECT t1.*
FROM YourTable AS t1
INNER JOIN (
    SELECT GroupField, MAX(FieldNameToFindMax) AS MaxValue
    FROM YourTable
    GROUP BY GroupField
) AS t2 ON t1.GroupField = t2.GroupField AND t1.FieldNameToFindMax = t2.MaxValue;

 

[GaP42]

If there is only (ever) one of these records per buyer then there is a question as to why it is not part of the buyer record. A subform would not be needed. However if you retain the additional table populating the subform then, to ensure only one record your subform format is set to single record display, and change properties to hide navigation on the subform. You may also want to manage the data settings for Allow Additions (when a record already exists)

 

[moi]

use Query:

from ChatGPT:

SELECT t1.*
FROM YourTable AS t1
INNER JOIN (
    SELECT GroupField, MAX(FieldNameToFindMax) AS MaxValue
    FROM YourTable
    GROUP BY GroupField
) AS t2 ON t1.GroupField = t2.GroupField AND t1.FieldNameToFindMax = t2.MaxValue;

Hello Arnel,

Many thanks for picking up all my queries/post.. You help me a lot in my simple project..

I wish i can learn more through this group..

 

[moi]

If there is only (ever) one of these records per buyer then there is a question as to why it is not part of the buyer record. A subform would not be needed. However if you retain the additional table populating the subform then, to ensure only one record your subform format is set to single record display, and change properties to hide navigation on the subform. You may also want to manage the data settings for Allow Additions (when a record already exists)

Thank you.. I may consider rebuilding the table..

 

[isladogs]

See if this article helps

Limit the Number of Records

This is a simple application demonstrating how the number of records in a subform can be limited to a specified value

isladogs.co.uk

 

[moi]

See if this article helps

Limit the Number of Records

This is a simple application demonstrating how the number of records in a subform can be limited to a specified value

isladogs.co.uk

Thank you isladogs.. i will study your demo..

 

[Pat Hartman]

Also, hiding the subform when there is no record is a bad idea. How would you add the data later?

 

[moi]

Also, hiding the subform when there is no record is a bad idea. How would you add the data later?

Hi, that is what i said, but then i tought if there is something like switch to show the subform to add record if available to add.. Like i was told, they dont want to see that thing (subform) empty. If there is, maybe you can guide me through.

 

[GaP42]

You can make a subform visible or not using the subform property. OnCurrent event could be used to set the subform as visible if a record is present, or not visible if not. However you will need a button on the main form to toggle the visible property of the subform when you need to add data (and presumably Allow additions property is true too).