Is a ListBox the Correct Solution? (1 Viewer)

[painterz]

Hi All,

I've been stuck on this for days and I'm still stuck. :banghead:

I have a main form (Neighborhood). Within it I have a subform (Family)--master/child link AddressID, within Family I have a subform People, master/child FamilyID.

I need the Family subform to display a list of families living at an address (Neighborhood). My first thought was use a datasheet--but I found out you can't have a subform on a datasheet.

My next thought was listbox, but it has to have an updatable query in order to select a record. I don't need the list box to actually do anything except allow the user to select which family to show so the People can be added, deleted, or edited. I've tried making the listbox unbound but couldn't figure out how to get it to work.

I've attached a screen shot. When I move through the Address records using the record selector, all of my data changes except the list box on the Family subform. It looks like it needs to be requeried--so again, I don't think it's the right solution or I don't have it set up correctly.

BTW, I've tried using a datasheet outside of the subform, but it didn't update the FKs in the underlying query.

I've considered a split form, but I've heard it doesn't always format correctly and I've never used one. Any ideas?

Thanks!

 

[MarkK]

I think your UI is too complex. You are building an all-in-one tool and it's getting in its own way.

You have two work-flows that I can discern:
1) manage families moving into and out of an address
2) manage people moving into and out of a family

I recommend you create a mainform/subform tool for each workflow.

 

[painterz]

Hi Mark,

Thanks for responding. So I would build a main/subform for Address to Family, then a main Family form, with a People subform? I know my main Address to People subform works, that's been my design for the last 20 years.

It would make my life easier breaking this down, I've been trying to get this to work since mid-January, but it seems that my design of main/sub/sub-subform should work if I just knew what I was doing.

More to ponder…

Thanks!

 

[MarkK]

I know, I feel like I'm watching you paddle upstream on this job, and I really think almost all the problems you are running into are a result of the complexity of your form/subform/subsubform arrangement. What Access provides is not really adequate for what you are doing there. And there is no need for such a monolithic UI. Build a separate (simple) tool for each job, IMO.

Cheers,

 

[painterz]

So still pondering…

I've attached my previous design. Do you see a problem if I make the Family subform a datasheet (Address form/ Family subform) and make the People subform (Address form/People subform) unbound?

The problem I was running into with this design, I had both subforms bound but when I added a person to the People subform, the FamilyID didn't record in tblPeople (FK) and the AddressID (FK), PeopleID (FK), and FamilyName didn't update in tblFamily so my new people weren't associated with the address. I'm not sure if having the People subform unbound would solve my problem.

I was trying to avoid a lot of coding. I'm really rusty and I reach a whole new level of frustration wasting a day writing simple code when it used to take me minutes.

Thanks

 

[MarkK]

I don't know what to recommend precisely without knowing how your tables are structured. I would stay away from unbound forms, I don't see a good enough reason to do that.

If you want to, post your DB and I can make changes or troubleshoot, and show you stuff way more quickly and thoroughly than via these verbal posts.

 

[painterz]

I've attached my db. My current design will open (Copy of Neighborhood Input Form). My last design is Neighborhood Input Form. I've made a few changes to get the "Copy…" to work but I can change them back then upload again. Let me know.

Thank you!

 

[painterz]

I'm not sure how to pick up an attachment someone else posted.

Cronk created this graphic for me, http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=62303&d=1459125196. It shows the relationships between the tables and I think it accurately reflects my relationships set in my queries.

 

[painterz]

Hi Mark,

I have ControlsVisible() working. I'll send it to you in a PM.

Thanks

 

[MarkK]

Here's a quick example. See how simple each part is? One form is based on one table. One form has one subform, again, based on a single table. Edit anywhere. Add stuff anywhere. All the links and foreign key IDs are automatic.

And you can't set family data from the person form, for instance. Each interface has it's own specific data type and function, so you're not reaching too far to get at data that isn't owned by the current type of object (in the current table).

Also, you should compile from time to time, and resolve the errors that come up. Hit ->MainMenu->Debug->Compile I just commented out stuff that wouldn't compile.

Hope this helps,

 

[painterz]

Hi Mark,

Thanks for the forms. Your solution is elegant but will take me longer to look up information. When a phone number or email address is requested, I will need to drill down to the third screen to retrieve the info. I see you caught one of my errors by having the check boxes at the people level rather than the family level.

Sorry to be stubborn, but I would prefer one screen. I can look at once to get whatever info is being requested. (Currently, I am the developer and user.) I've attached my current design. I've gone back to a datasheet for the Family subform and form view for the People subform. I believe my problem stems from qryFamily, which is the recordsource for the Family subform.

I decided my protocol should be to add a new family on the Family subform. Use an after update to requery the info so it shows in the People subform. The qryFamily isn't writing the AddressID or PeopleID to tblFamily so I lose my link to the People subform.

Do you know why this is happening? I think if I can get past this sticking point, I can finally get these forms working.

Thanks!

 

[MarkK]

Yeah, I'm not recommending my approach for finding stuff. I would add popup selectors for important data, but that was beyond the scope of our discussion, and my available time.

But no, please be stubborn. Tenacity in pursuit of YOUR solution is a major asset.

I don't know where to find this problem . . .

The qryFamily isn't writing the AddressID or PeopleID to tblFamily so I lose my link to the People subform.

. . . but the query shouldn't write anything. Links, and updates, are handled by the subform control and its .LinkMasterFields and .LinkChildFields properties. Is it a particular form/subform combination where this is failing?

Cheers,

 

[painterz]

Hi Mark,

The master/child link between frmAddress and frmFamily (subform) is the AddressID, so why isn't the AddressID being recorded in tblFamily? qryFamily is the underlying query to frmFamily and it contains tblAddress, tblFamily, and tblPeople. AddressID is the PK in tblAddress and FK in tblFamily, AddressID doesn't exist in tblPeople.

Thanks

 

[MarkK]

Your qryFamily actually returns people, in which case it can return many rows per family, and in that case it is ambiguous which row should receive the new AddressID. The subform architecture expects to be able to link a single row in a child table to a single row in a parent table. If there is ambiguity in this relationship, then the update cannot be performed.

Modify your qryFamily to return one row per family. Then the subform can update its foreign keys without ambiguity.

qryFamily also does not use any fields from tblAddress. I would remove tblAddress from that query also. Just use tblFamily as the RecordSource for that subform.

 

[painterz]

You're right! So this got me thinking, my hierarchy is:

tblAddress
tblFamily
tblPeople

So the master/child link between my frmAddress and frmFamily is AddressID. I was using PeopleID as the master/child between frmFamily and frmPeople and changed that to FamilyID. I think I'm finally on the right track! I'll spend the afternoon playing with the new changes to see if I can add records and everything records correctly.

Thank you!!! I knew this would be a syntax issue just didn't know where to look

 

[painterz]

Hi Mark,

frmAddress has both frmFamily (m/c AddressID) and frmPeople (m/c FamilyID) subforms. When I added a record to frmFamily I didn't get a new record in tblPeople. I thought I would have at least gotten a near empty record that showed PeopleID (PK) and FamilyID (FK) in tblPeople since the master/child link is FamilyID. Do I still have a syntax issue?

Thanks