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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Need Help With Subforms, Access 2016    
 
   
cpsmith58
post Mar 24 2017, 01:50 PM
Post#21



Posts: 110
Joined: 22-February 17



OK, I have finally had time to look at this and other than the somewhat nasty listbox SQL, it's somewhat straightforward? As in, you are literally putting everything on the screen. I like it.

I will begin the process of ripping the guts out of my app and moving it to this, I think it's a step up. I will plug route into this also.

Thanks for the not insubstantial effort you put into this!

The union stuff is cool, never done that, just inner joins.
This post has been edited by cpsmith58: Mar 24 2017, 01:54 PM
Go to the top of the page
 
cheekybuddha
post Mar 25 2017, 02:43 PM
Post#22


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


>> I will plug route into this also <<

How do you mean?

Notice that the main form is based on routes. You can navigate through them suing the navigation buttons at the bottom of the form.

If you are ripping guts out then I also suggest you separate conceptual entities out of your routes/counties/cities/locations. Contacts should be in a separate table and joined where appropriate. Same for companies, payment info etc.

It just helps to get things in order!

See here on The Access Web re. adding 'All' to a combo/listbox

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 
cpsmith58
post Mar 27 2017, 08:14 AM
Post#23



Posts: 110
Joined: 22-February 17



a) My existing stuff doesn't have route in it. It will show the route for the children you're on, but you can't select a route and filter down from there.

b) I can't change the route ID on the screen of your database, not sure why, it's not locked and it's' enabled and I can't find a mask.

c) I would want a drop down anyway, populated as others, with "ALL"


EDIT: Ahh, it's configured to use the record navigator to cycle through routes. And I can't edit it because I would be editing a primary key that it is bound to.
This post has been edited by cpsmith58: Mar 27 2017, 08:16 AM
Go to the top of the page
 
cheekybuddha
post Mar 27 2017, 10:02 AM
Post#24


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


>> Ahh, it's configured to use the record navigator to cycle through routes. And I can't edit it because I would be editing a primary key that it is bound to. <<

wink.gif

You can add an unbound listbox to your form with RecordSource based on RouteID and Route.

Then use its AfterUpdate event to do something like:
CODE
Me.Recordset.FindFirst "RouteID = " & Me.lstRoute


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
cpsmith58
post Mar 28 2017, 02:32 PM
Post#25



Posts: 110
Joined: 22-February 17



Weird behavior. I implemented some of what you wrote (no subforms yet)

but if I click ALL 2x in County, the City and Location listboxes depopulate (the queries return 0 records). Honestly I am not sure which part of the select is executing.

CODE
SELECT pkCity, fkCounty, txtCityName
FROM
(
    SELECT A.pkCity, A.fkCounty, A.txtCityName FROM tblCity AS A
    WHERE A.fkCounty = [lstCounty]
    OR
    (
        [lstCounty] IS NULL AND A.fkCounty IN
        (
            SELECT pkCounty FROM tblCounty WHERE fkRoute = [lstRoute]
                )
    )
    OR
    (
                [lstCounty] IS NULL AND [lstRoute] IS NULL AND A.fkCounty IN
          (
            SELECT pkCounty FROM tblCounty
        )
    )
)
UNION SELECT NULL,NULL, '[All]'
FROM tblCity
ORDER BY txtCityName;



Here is a link of it happening (my Flickr page)
https://flic.kr/p/Ta2rrP

Front and back end DB are attached, note I changed some PK and FK field names to be more standardized.

I don't understand how it decides which part of the WHERE clause to execute.

Attached File(s)
Attached File  LiLo_FE.zip ( 345.5K )Number of downloads: 9
Attached File  LiLo_BE.zip ( 149.99K )Number of downloads: 3
 
Go to the top of the page
 
cpsmith58
post Mar 28 2017, 02:35 PM
Post#26



Posts: 110
Joined: 22-February 17



I think the problem is the last part of the query, where County and Route are ALL, so I want ALL the cities.

I tried just making it (pkCity >0) which made it give me everything but it would always give me everything, never filter by county or route.

EDIT:

I think I understand, it's a big OR, where only 1 can be true, so I have to check for COUNTY and ROUTE being NULL, then I can pull everything.


This post has been edited by cpsmith58: Mar 28 2017, 03:07 PM
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 08:10 PM