UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help With Subforms, Access 2016    
 
   
cpsmith58
post Mar 20 2017, 01:41 PM
Post#1



Posts: 72
Joined: 22-February 17



I have a 4 level deep db.

A route can have many counties, a county can have many cities and a city can have many locations (so, tblRoute, tblCounty, tblCity and tblLocation).

I understand how to link subforms but don't know how to physically get all these forms on the screen at once.

Right now have a frmLoc (ona NavForm) with a tab control on it with 3 tabs (Location, City and County, no need for Route at this point) but clicking from the Location tab to the City tab will drop you right onto a City record that is NOT parent to that location. I tried setting global variables and doing FIND but that is just dumb. And hard, for me anyway.

Help! I'm OK with tearing it up, I just need to understand how to start this and how to make room for it all.


Thanks!
This post has been edited by cpsmith58: Mar 20 2017, 01:58 PM
Go to the top of the page
 
doctor9
post Mar 20 2017, 02:28 PM
Post#2


UtterAccess Editor
Posts: 16,833
Joined: 29-March 05
From: Wisconsin


cpsmith58,

I'm not sure I understand the concepts of routes in the context you're talking about. In my mind, a route is a journey from one location to another location with possible locations/stops along the way. If this is what you're talking about, a route is not really a superset of a county. I understand how a county contains cities and can have locations (a restaurant, city hall, a residence, etc.) but a route is something that should be more like a junction table that creates a many-to-many relationship. However, I'd like to hear back from you about what you consider a route before proceeding.

The important point I'm trying to make: Your table structure should be set up properly before considering creating forms.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 02:35 PM
Post#3


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


Hmmm...

Can a county overlap different routes?

I have skimmed over a couple of your other threads on this, but have always thought there is something not right about your setup.

I think I would go about it the other way round. Start with locations

Locations have a city.
Cities have a county.

So tblLocations contains a foreign key to tblCity.CityID. tblCity contains a foreign key to CountyID.

Then separately you have Routes. They just contain locations - either a foreign key to LocationID, or porbably better a m:m relationship via a junction table in case a location could ever be part of more than one route.

From the route's relationship to its location, you can determine its city and county. A route does not need to store CountyID's nor CityID's.

It's a bit tricky on th UI front, especially if you want to build routes by going county->city->location, but not impossible by any means.

If the locations are preloaded then you can use cascading comboboxes/listboxes.

The trick is to separate the entities in your thinking: Routes are one thing. Then you have locations (and they have cities and counties).

If you are entering a new location, I would do it unbound at first. This way you can enter it County->City->Location, or the other way too. Once you have all the elements you can write the records, and then assign to the Route.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
doctor9
post Mar 20 2017, 04:22 PM
Post#4


UtterAccess Editor
Posts: 16,833
Joined: 29-March 05
From: Wisconsin


cpsmith58,

Assuming you define a "route" as a series of locations that are visited in a specific order, here's a simple table structure for holding routes:

tblCounties
CountyID [Autonumber, Primary Key]
strCountyName

tblCities
CityID [Autonumber, Primary Key]
lngCountyID [Foreign Key to tblCounties.CountyID]
strCityName

tblLocations
LocationID [Autonumber, Primary Key]
lngCityID [Foreign Key to tblCities.CityID]
strLocationName
strAddress
strZipCode
lngGPSLatitude
lngGPSLongitude

tblRoutes
RouteID [Autonumber, Primary Key]
strRouteDescription

tblRouteLocations
RouteLocationID [Autonumber, Primary Key]
lngRouteID [Foreign Key to tblRoutes]
intOrder
lngLocationID [Foreign Key to tblLocations.LocationID]

With this setup, you can have a route named "Visit each movie theatre in Dallas, Texas". Then, in tblRouteLocations you'd have 17 records along these lines:

CODE
RouteLocationID - lngRouteID - intOrder - lngLocationID
       123            14          1        163
       124            14          2        211
       125            14          3        72
       126            14          4        98

In this case, the first column is the primary key for the table, 14 is the primary key value for the route called "Visit each movie theatre in Dallas, Texas", the intOrder value tells you where, on the route of locations, this location occurs. The last column is the primary key values in the tblLocations table for the locations named "Texas Theatre", "LOOK Cinemas", "Angelika Film Center & Café" and "Inwood Theatre".

This last table is a Junction Table that creates the many-to-many relationship between locations and routes (one route can have many locations and one location can appear on many routes). Note that you don't need to store the city or county in this table, as you can find these details by following the foreign keys to the appropriate tables. For example, all four movie theatres listed above would have the same value for the lngCityID value, since they're all located in Dallas, Texas.

Again, if your definition of a route is different from my definition, please explain what a route is, and how it relates to counties, cities and/or locations.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 04:31 PM
Post#5


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


Dennis,

Have a look at post #17 onwards in this thread, and also cpsmith58's other last few topics.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cpsmith58
post Mar 21 2017, 07:43 AM
Post#6



Posts: 72
Joined: 22-February 17



If I gave you my address and told you to stop by, you'd go to Google maps, plug it in and get a route, Point A to B to X etc, a line on a map, to get from your house to mine.

My route is not that. It is a superset of counties, a territory owned by a route manager. A county can't be in more than one route. Ergo, tblRoute is the great grandparent, tblCounty is the grandparent, tblCity is the parent and tblLocation (a bar or VFW or truck stop where we have equipment) is the child.
This post has been edited by cpsmith58: Mar 21 2017, 07:45 AM
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 08:00 AM
Post#7


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


>> A county can't be in more than one route. Ergo, tblRoute is the great grandparent, tblCounty is the grandparent, tblCity is the parent and tblLocation <<

A location can't be in more than one city, a city can't be in more than one county (can it?).

Once you have a location defined, that's all you need to link to a route. From the location you can find the city and county. Separate your entities.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 08:06 AM
Post#8


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


OK, I see that you need to assign counties to a route, and why you might go with the structure you started with.

Can you post a db with just the tables and some dummy data of routes,counties, cities and locations and perhaps we can mock up a ui for you.

hth,

d


--------------------


Regards,

David Marten
Go to the top of the page
 
cpsmith58
post Mar 21 2017, 08:26 AM
Post#9



Posts: 72
Joined: 22-February 17



Thanks!

It's split, as I was told to do. Some of the fee stuff is a work in progress.

To be clear, the goal is both to get a well designed interface but also to learn how to put 4 forms in one place, which is what I don't understand. I don't want to be fed a fish, I need to learn to fish?

Again, thanks!
This post has been edited by cpsmith58: Mar 21 2017, 08:28 AM
Attached File(s)
Attached File  LiLo_BE___Copy.zip ( 120.79K )Number of downloads: 4
Attached File  LiLo_FE.zip ( 538.67K )Number of downloads: 5
 
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 09:03 AM
Post#10


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


Ah, sadly I can't open them - I only have A2K7, and I'm not able to open newer .accdb's.

If you're able to save the be down to version 2007 or below, I'll take a look later.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
doctor9
post Mar 21 2017, 09:24 AM
Post#11


UtterAccess Editor
Posts: 16,833
Joined: 29-March 05
From: Wisconsin


cpsmith,

No offense, but "territory" is a much better descriptor than "route" in this case. smile.gif

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cpsmith58
post Mar 21 2017, 09:25 AM
Post#12



Posts: 72
Joined: 22-February 17



I have the back end tables as an .mdb, I couldn't get the forms to come over, my apologies.

Doctor9, no argument, but I am using the business terms that are meaningful to the users. To them, a route IS a territory.
This post has been edited by cpsmith58: Mar 21 2017, 09:26 AM
Attached File(s)
Attached File  Database1.zip ( 123.66K )Number of downloads: 5
 
Go to the top of the page
 
cpsmith58
post Mar 21 2017, 11:30 AM
Post#13



Posts: 72
Joined: 22-February 17



Just found a problem. In my current structure, I may need a new City (or Location, but let's ignore that for now, the problem is the same, regardless). My method for accomplishing that is to require that the user be in the County parent record, then they click the 'New City" button. That calls:

DoCmd.OpenForm "frmCity", acNormal, , , acFormAdd, acWindowNormal, Me.txtpkCounty

The last parm in that is the Parent record's primary key, so in the City, that becomes the foreign key back up into the County, so when you save the new City, you have the FK needed to point to the parent.

Great, right? Nope, because instead of calling that frmCity as normal (it's on a tab control, that tab has a subform, that subform contains frmCity), so when you go into City normally, you're within the framework of the app, now you're in this form floating in nowhere.

It works, but it's hideous. I'd like to do the same, but open frmCity in its normal nested environment. But I think that will change because I want to nest it all differently to maintain normal GGP->GP-> Parent -> Child relationships, which is why I started this thread in the first place.

Grrr..
Go to the top of the page
 
doctor9
post Mar 21 2017, 04:55 PM
Post#14


UtterAccess Editor
Posts: 16,833
Joined: 29-March 05
From: Wisconsin


cpsmith58,

Went into your posted files, re-attached the backend to the frontend, but there's a popup form asking for a USER ID and Password. What should I enter to use the database, and where do I find the "New City" button once I'm in?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cpsmith58
post Mar 22 2017, 08:04 AM
Post#15



Posts: 72
Joined: 22-February 17



Apparently you have an older version, the newest I uploaded (on this thread), I had to downgrade to 2007 and it lost the forms, not sure why.

As far as user form, that info aligns with user table (so cpsmith58, 1 for user/pw). That does nothing at this point, other than binary/you can get in or not.

New City is on the County screen in the top right. It's there so when I call the City form, I have the County parent/foreign key in hand.

Note the challenge I mention in post #13, about the form popping up full screen and not in the 'interface'

THANKS
This post has been edited by cpsmith58: Mar 22 2017, 08:07 AM
Go to the top of the page
 
doctor9
post Mar 22 2017, 09:11 AM
Post#16


UtterAccess Editor
Posts: 16,833
Joined: 29-March 05
From: Wisconsin


cpsmith58,

Since the city form is already open, you just need to manipulate it, not re-open it.

Try this:

CODE
    Me.Parent.SubfrmCity.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me.Parent.SubfrmCity.Form.txtfkCountyID = Val(Me.txtpkCounty)


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cpsmith58
post Mar 22 2017, 09:26 AM
Post#17



Posts: 72
Joined: 22-February 17



Boom, that's big. I will make that change.

Still don't comprehend the 'form within subform link all 4 levels' thing (original reason for this post).

But that is a good step!
Go to the top of the page
 
cpsmith58
post Mar 22 2017, 01:01 PM
Post#18



Posts: 72
Joined: 22-February 17



Setting the foreign key (primary key of the county) onto the new record of the city form got an error.

Me.Parent.SubfrmCity.Form.txtfkCountyID = Val(Me.txtpkCounty)

Currently trying to figure out the 'path' to setting a textbox (city foreign key of county) on frmCity


Attached File(s)
Attached File  26.png ( 18.01K )Number of downloads: 1
 
Go to the top of the page
 
cpsmith58
post Mar 22 2017, 01:34 PM
Post#19



Posts: 72
Joined: 22-February 17



Tried this, seemed promising

CODE
Me.Parent.SubfrmCity.SetFocus
DoCmd.GoToRecord , , acNewRec
Form_frmCity.txtFKCounty.Value = Val(Me.txtpkCounty)

[/code]

3rd lined died with this error, hmm.

EDIT, typo

Form_frmCity.txtFKCountyID.Value = Val(Me.txtpkCounty)

Much better, woo hoo!

Still need to know about form in subform in subform etc, I suspect I would have avoided all this pain!

Thanks!
This post has been edited by cpsmith58: Mar 22 2017, 01:38 PM
Attached File(s)
Attached File  27.png ( 3.8K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Mar 22 2017, 01:55 PM
Post#20


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


I wasn't able to see your forms of course, so I mocked up a basic UI, that uses listboxes to navigate the counties, cities and locations for each route.

The details of each are displayed in subforms in a tab control with master/child based on the listboxes.

It might give you some ideas.

hth,

d
Attached File(s)
Attached File  cpsmith58.zip ( 293.7K )Number of downloads: 5
 

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th March 2017 - 01:05 AM