Table Design

[plasteredric]

Hi Guys, well im back again...
Working on the forms for my property db.
Is there a way of having a form that will display a searchable list of Landlords and display the houses they own? perhaps in a seperate list.

I've seen that when I have the datasheet view of the landlords in the form it shows the houses beneath when you click the + at the start of the row but this isn't very visually appealing.

Any suggestions?

 

[Beetle]

You would typically do this using a main form / sub form set up where the main form is based on the Landlords table (or a query thereof) and the sub form is based on the Houses table. Might look something like;

BTW - it sounds like you have subdatasheets enabled. This property can cause performance issues and it is generally recommended that it be set to None. For more on that see here. Scroll down the page to the section titled Tables: Subdatasheetname

 

[plasteredric]

Thanks for the advice on the subdatasheetname, i've changed them all to none.

Would doing that way with a form/subform mean that a Landlord can be picked from the list and it would show their houses in the subform automatically?
How would I go about doing it? I managed to get the subform showing the houses but I can't pick the landlord from the list :s

 

[Beetle]

Would doing that way with a form/subform mean that a Landlord can be picked from the list and it would show their houses in the subform automatically?
How would I go about doing it?

Yes, you can use a combo box for this. When doing this, it is important the the combo box be unbound (in other words, not bound via it's Control Source to a field in your record source). If you're unfamiliar with setting this up, you can use the wizard. When you place a combo box on your form (in this scenario you would normally place it in the header section of the main form) it should bring up the wizard. One of the options is "Find a record on my form based on the selection in the combo box", or something like that. I don't remember exactly because I don't use the wizard.

 

[plasteredric]

Thanks Beetle, I really appreciate the help.

Took me a few goes to get it right, but its working how I want it now.

Can the same thing be done using a datasheet/subdatasheet for the list of landlords rather than a combo box?

Also is their a way to take off all the buttons (filter, search, etc) from the bottom of the data sheet?

 

[Beetle]

Can the same thing be done using a datasheet/subdatasheet for the list of landlords rather than a combo box?

You can set up a form with two continuous sub forms. To do this you use a text box to help control the behavior. This text box would normally be hidden, and you would set it's Control Source property so that it returns the PK value of the current record in the first sub form. In this case, the Control Source of the text box would look something like;

=[sfrmLandlords].Form![LandlordID]

Keep in mind in the above syntax that sfrmLandlords is referring to the name of the Subform Control. The Subform Control is the "window" that holds the sub form, which may or may not have the same name as the sub form itself.

Then, in the second sub form, you set the Link Master Fields property equal to the name of the hidden text box, and the Link Child Fields property equal to the foreign key field in the second sub form. It would look something like;

Again, this is done in the properties of the Subform Control, not the properties of the sub form itself. Then you have a form that looks something like the following (I have left the text box visible here for demonstration);

When you select a record in the first sub form (in this case Landlords), the related records are displayed in the second sub form (Houses).

Also is their a way to take off all the buttons (filter, search, etc) from the bottom of the data sheet?

Set the Navigation Buttons property of the form to No. You can see the difference in the example image above.

 

[plasteredric]

Thanks Beetle, im in awe of the effort you put into your posts.
What you suggested looks great and seems to work great too.

I keep getting an enter parameter value - 'textbox' dialog box popping up when I open the form and the 2nd subform appears blank.

I suspect it may be because I have used tab controls on the main form and put the 2 subforms and text box into that as I copied them onto a blank form and it works just fine.

Is there a way to sort this? I would like to use this form as a dashboard with each of the tabs displaying various data.

Thank you

 

[Beetle]

I keep getting an enter parameter value - 'textbox' dialog box popping up when I open the form and the 2nd subform appears blank.

Sounds like there is something wrong with the reference to the hidden text box that controls the link for the second continuous sub form. Check that you have the name of the hidden text box spelled correctly in the Master/Child link property of the Subform Control.

 

[plasteredric]

Sounds like there is something wrong with the reference to the hidden text box that controls the link for the second continuous sub form. Check that you have the name of the hidden text box spelled correctly in the Master/Child link property of the Subform Control.

It seems to be working fine now, the subforms weren't on the tab layer correctly and were just floating infront

Is there a way of making the subforms auto update when a new record is added?

 

[Beetle]

Is there a way of making the subforms auto update when a new record is added?

Not sure what you mean here. When you're adding a record where? In one of the sub forms? In the main form? And what do you mean by auto update? Are you talking about saving the record? The record is saved automatically when you move to another record, etc.

 

[plasteredric]

Not sure what you mean here. When you're adding a record where? In one of the sub forms? In the main form? And what do you mean by auto update? Are you talking about saving the record? The record is saved automatically when you move to another record, etc.

I have create a seperate form for adding a new landlord to the database.
If the 'dashboard' form is open at the same time as when a new record is created and the newlandlord form is then saved/closed, the new landlord doesn't show on the subform on the 'dashboard'.
Is there a way of it detecting the change and refreshing the list? or a work around that would have this effect?

 

[Beetle]

In the Close event of the new Landlord form check if the dashboard form is open and if so requery it.

Private Sub Form_Close()

    If CurrentProject.AllForms(frmDashboard).IsLoaded Then
        Forms!frmDashboard.Requery
    End If

End Sub