Open another form to display record

[silentwolf]

Hi guys,
just wondering what the issue could be with following.

I have created a form frmMietvertrag with a datasource of a query.

I open that form from "frmTenant" The button is on the FormHeader

Private Sub btnMietvertrag_Click()
    DoCmd.OpenForm "frmMietvertrag", datamode:=acNormal, WindowMode:=acWindowNormal, WhereCondition:="TenantID=" & Me!TenantID
End Sub

In that form is a tabControl for more information regarding the tenant

So my question is following

When most of the Data is filled in the frmTenant and some subforms in the tabControl then the frmMietvertrag is opening alright and showing textfields and so on and filled correctly.

However if there are Information missing in the tabControl for example what Appartment it is and so forth but FirstName and Surname and TenantName in the frmTenants are includet the frmMietvertrag is opening without any controls so it is complety empty not even the controls are showing...

What is wrong?

Many thanks for your input

Cheers
Albert

 

[MajP]

If you filter a form to 0 records and allow editions is false that is normal behavior. This can also happen if your query is an inner join and you need a left join.

 

[silentwolf]

Hi MajP,

thanks for your reply!
Will check your answer with my database and hope to work it out!

Getting back to you!

Cheers

 

[silentwolf]

Here is the Query as mentioned shown below

I thought I have it worked out with this join but still the same issue.

If you filter a form to 0 records and allow editions is false that is normal behavior.

Sorry this I don' understand completely.. allow edition is false is that a property on the form?

 

[Gasman]

Yes, along with Additions and Deletions.

 

[silentwolf]

Hi Gasman,

I guess this are the properties you are refering to?

Below the pic.

So is it set wrong?
Perhaps is the same location in english versions of access?
Does any of those need to be set to NO?

Sorry not sure??

Löschen zulassen = allow deletion
Anfügen zulassen = allow addition
Bearbeiten zulassen = allow edition

Filter zulassen = allow filter

 

[MajP]

You show a left join now but lets say it was an inner join. Since I did the summary query it only includes records where the appartment has summary information (room, room dimensions). So until those are filled out not every apartment is in the summary table. This also means not every apartment is in the forms recordsource. If you open the form to an apartment that is not in the recordsource and one of the following is true
1. The query is not updateable
2. The form has allowadditions set to false

It cannot show an empty row to allow additions and no records are returned. This will make it appear as no controls.

 

[silentwolf]

Ok I understand,

and sure it does not make any sense to write a document with missing information.

So I guess I just ignore the fact of no data or controls are displayed and return to the forms to enter approbiate data.

I also treet the frmMietvertrag supose to be called frmRentalAggrements as a non editable Form and if there is data that need to be updated
then I go back to the subforms and record those there.

Is that correct that way?

Cheers

 

[MajP]

So here is a demo.
1. I have a form based on a non updateable query. Since it is a summary query you cannot add records to it.

2. The combo box can be used to filter this. If I filter to something that exists I get

But if I choose something that does not exist then

Because I cannot show an empty row it shows no controls.

If I wanted to avoid this situation I would only allow records in my combobox that existed in the form.

Can you send a snap shot of the form.
I would think the summary information is not part of frmMeitVertrag rowsource. That should be a subform linked by appID on the main form. That may solve your problems. If you pull that out of the query.

 

[MajP]

Also My guess is qryApartmentTennant needs to be based on left joins. You probably want to show all apartments regardless if they have a tenant. If it is an inner join then no all appartments would show, but only those with tennants.

 

[silentwolf]

Hi MajP,
thanks for your help!!

As my workflow is a little different I think all I need to do to prevent the frmMietvertrag to open if there is no Appartment selected for the Tenant
is following Code.

Private Sub btnMietvertrag_Click()
    If IsNull(Me.sfmAppartmentTenants.Form.AppTenantID) Then
        MsgBox "You must provide a Appartment for this Tenant!", vbOKOnly
    Else
        DoCmd.OpenForm "frmMietvertrag", datamode:=acNormal, WindowMode:=acWindowNormal, WhereCondition:="TenantID=" & Me!TenantID
    End If
End Sub

I think that would do the trick

What do you think?

Cheers

 

[Gasman]

Have you tried it?
I would have thought a DCount() would identify if there are any records?

 

[silentwolf]

Yes I did try it,

so the form below allows me to enter Tenant Informations.
the sfmAppartmentTenant is shown as Wohnungen
if there is no Appartment is selected the btnMietvertrag shows me the Messagebox if there is an appartment selected it will open the form and
get all informations.

Not good practice?

 

[silentwolf]

Oh,

it works only if I got no appartment selected if there is an appartment selected and not all fields are providet then I get again an empty form .(

it is getting late on my end I will take a rest from it for now.

@MajP I will give you tomorrow the info!

to all others many thanks for your help I will get into it again tomorrow and see what I have come up with.

Cheers

 

[MajP]

If you can upload we can suggest how to do it. You may need to modify your queries or possible set up some subforms on your main form so that you can always see the basic information even when the complete related fields are not included.

 

[Gasman]

Go to bed. Get some sleep.
Then *think* carefully of the logic.
Then implement that logic.

 

[Josef P.]

I have created a form frmMietvertrag with a datasource of a query.

What does this query (SQL) look like?
Is there a Where statement?
Does it possibly contain something like Surname like '*'?

 

[silentwolf]

Good Morning Access People )

Some Changes for your understanding:
I have renamed my form frmMietvertrag to frmRentalAgreement

Below the Current TestVersion with all Queries & Forms

Many thanks for taking a look at it

Cheers

Albert

 

[Gasman]

We need data as to where it does not work?
I just opened form frmTenant and clicked on the only button there Mietvertrag, and it opened to data?

I have no idea as to how you are meant to move to the next tenant?

 

[Josef P.]

I think MajP has already described the problem in #2.
Inner joins vs left/right join

SELECT
   ...
FROM (
      tblAppartment
      INNER JOIN qryApartmentSummary ON tblAppartment.AppID = qryApartmentSummary.AppID)
      INNER JOIN (tblTrashCan
              INNER JOIN ((tblSalution
              INNER JOIN tblTenant ON tblSalution.SalutionID = tblTenant.SalutionID)
              INNER JOIN tblAppTenant ON tblTenant.TenantID = tblAppTenant.TenantID) 
       ON tblTrashCan.TrashCanID = tblAppTenant.TrashCanID)        
       ON tblAppartment.AppID = tblAppTenant.AppID;