Quote from Gaev on January 29, 2021, 7:02 pm

@mark-waples

I am looking for a demonstration that shows how to layout and control an access database that has "one-to-many" relationships but avoiding the use of a Horizontal grid.

Say you have two Tables .
- Continents with a field named thisContinent
- Countries with fields named parentContinent and thisCountry

Open Countries Table first
Open Continents Table next ... within this command, refer to a subroutine named ContinentHasChanged

In this subroutine, you can do a dbpQuery (on the Countries Table) to limit the records to those where parentContinent matches the value of the field thisContinent in the current record within the Continents Table.

Then, you can loop through the matching records using dbpNext, dbpPrev, dbpFirst, dbpLast, dbpGoToRecord

During the testing stage, you might consider displaying both Grids ... to verify that the records in the Countries Table View are in sync with the current record in the Continents Table.

If any of this is not clear, let us know.

Quote from Mark Waples on January 30, 2021, 2:37 pm

@Gaevhat's the

 

Say you have two Tables .
- Continents with a field named thisContinent
- Countries with fields named parentContinent and thisCountry

What is the relationship in access please? - I really don't get the concept of access relationships?

 

Thank you

Quote from Mark Waples on January 30, 2021, 3:04 pm

@Gaev

In this subroutine, you can do a dbpQuery (on the Countries Table) to limit the records to those where parentContinent matches the value of the field thisContinent in the current record within the Continents Table.

Can you show me contents the DPquery? in a subroutine again - beyond me sorry.

Once I have been shown I will remember how to do it - I just need a simple guiding hand.

Quote from Gaev on January 30, 2021, 4:42 pm

@mark-waples

Can you show me contents the DPquery? in a subroutine again - beyond me sorry.

1) Whenever a record in the Continents Table is inserted, deleted or edited (saved), the subroutine is invoked.

You can know details of this record by using the special variables like ...

[MyDB.Clients.FirstName]

... in the sample case being used for discussion, it would be ...

[yourDBHere.Continents.thisContinent]

2) So you would code something like ...

SetVar "[queryContinent]" "[yourDBHere.Continents.thisContinent]"
dbpQuery "yourDBHere" "Countries" "[parentContinent] = [#34][queryContinent[#34]"

... so now, when you move up/down records in the Continents Grid, you should be able to see that the rows in the Countries Grid change to reflect the change in value of thisContinent in the Continents Grid.

As mentioned before, you can loop through the filtered set of records in the Countries Table via script ... you can even use [yourDBHere.Countries.$RecCount] to obtain the number of matching records.

I have not had time to verify this, so let us know if you encounter issues.

 

Quote from Mark Waples on February 1, 2021, 12:26 pm

@gaev

thank you for your response - I think it boils down to my lack of experience with handling databases and my skillset is more inline with graphic form design.

I think you have to decide early on whether or not to use a flat-file or relational database method.

In the end I decided to start again and design the application as a relational database with grids.

Once that worked as expected you can then place the fields as you would like to see them displayed in another form.

I am still learning about database construction and this has been a steep learning curve.

So once again, thank you for your response.