Strange behaviour with form and subform

[zozew]

slight problem i have noticed,

I have a mainForm on that form i have a subForm then a subSubForm

the linking between the main and sub is all ok Linked Master field to Linked child field, but between the sub and subSub forms i sometimes get a promt asking me for the subForms linked master value.

I used the form wizard in the first place to create the linked forms so i dont get it.....and once i get the promt i have to go back to an older version of my DB and start over again because i cant link the sub and subSubform again so it works....very strange


Linked master field is: [InmateCases].Form![CaseNumberID]
Linked child field is: CaseNumberID

All relations seem to be ok between the tables so i don really get why it does it...

and when i use the (...) to relink the subSubform to the subForm i get a promt saying:

Parts of the link with missing pair fields will be ignored

click ok then...

I can only see fileds from the mainForm in the comboboxes to the left and field from the subSubForm on the right....where are the subForms fields....?

 

[vbaInet]

Re: Starnge behaviour with form and subform

Sounds like you inherited this db and not sure what's going on?

The developer would have most likely linked subform 1 to subform 2 via a textbox control. Field names are the only names that the pop-up linker understands and as a result you cannot select that name in the list of names. However, you can still use the values from a textbox to link subforms only if you manually type it into the Link properties.

 

[zozew]

Re: Starnge behaviour with form and subform

I just redid the formwizard and i get the same Linked master and child fields on the two subforms, and it works. but the problem is that it might happen again that after a few hours developing suddenly when i reopen the main form and i get the promt asking me for the value again...then its stuck in that way and i cant relink the to forms....very strange. What can be the reason for this strange behaviour and what can i do to counter it...?

thx again for the speedy reply..

Attached is a relations image...thought that might help...

 

[vbaInet]

Re: Starnge behaviour with form and subform

Corruption or there's code that's changing the property incorrectly.

You mentioned "when you open the main form", it may not be related to the link property. It could be a missing field in a combo box's row source, a missing field in the form's record source, an incorrect field in the Filter and Order By properties of the form.

 

[zozew]

Re: Starnge behaviour with form and subform

hehe this is wird....i mentioned i ran the form wizard again a for the three forms and it worked nicely again. and teh link fields where the same as the ones where it stopped working.

I copied the 2nd subform into the old form layout changed the names so it refered to the right stuff and now the linking works....

I also tried deleting the 2nd subform from the main form and replacing it from the navigator adding the link fields manually and it started working again....

Your suggestions...

1. It could be a missing field in a combo box's row source,
2. a missing field in the form's record source,
3. an incorrect field in the Filter and Order By properties of the form.


1. I have comboboxes with value list directly in some of the fields in the tables for the master form and 1st subform but not for the 2nd subform.

And i have one combobox on the main form with a value list doing this:

Private Sub TypeCombo_AfterUpdate()
If Len(Me.TypeCombo.Value & "") <> 0 And Me.TypeCombo.ListIndex > -1 And Me.TypeCombo.Value <> "All Prisoners" Then
Me.Filter = "CustodyClassification = '" & Me.TypeCombo.Value & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

2. i checked the 2nd forms source table and there where some records that where missing the requested ID field...could that be it...bad data?

3. i checked the filter and order by :
[tblInmateHearings].Hearings DESC, [tblInmateHearings].CaseNumberID
And the [tblInmateHearings].CaseNumberID is the one above (2) that was missing but the prompt asked me for the [InmateCases].Form![CaseNumberID] just like in the Link Master field


so....the conclusion i make is....im not sure, at least i dont have to re-develope the last hours ive been working, just do a wird delete and replace operation with the 2nd form to get the linking to work again.
Btw the DB has only a test recordset of six records so i can easily check if there is bad data


thx again for the suggestions

 

[vbaInet]

Re: Starnge behaviour with form and subform

Let me see the db and tell me which is the offending form/subform. Also give a brief on how to reproduce the problem.

 

[zozew]

Re: Starnge behaviour with form and subform

Let me see the db and tell me which is the offending form/subform. Also give a brief on how to reproduce the problem.

hehe easier said then done...about reproducing the problem. Let me trix around a bit and find out if i can reproduce it on command and ill send you the whole DB. Normally it happens when i open the master form going from design view or just opening the DB from windows (the master form opens automatically from an AutoExec)

I do have an earlier version of my DB that has that problem on opening it...you want that one?

thank you so much for taking the time

 

[vbaInet]

Re: Starnge behaviour with form and subform

I do have an earlier version of my DB that has that problem on opening it...you want that one?

Go ahead.

 

[zozew]

Re: Starnge behaviour with form and subform

My DB is 3.2Mb zipped and i can't attach it here, max is 2Mb. can i email it to you?

 

[vbaInet]

Re: Starnge behaviour with form and subform

1. Get rid of forms and reports I don't need to see (obviously in a copy of the db)
2. Compact & Repair, then upload.

Remember to leave some sample data. But ensure you can reproduce the problem in the stripped db before posting.

 

[zozew]

Re: Starnge behaviour with form and subform

1. Get rid of forms and reports I don't need to see (obviously in a copy of the db)
2. Compact & Repair, then upload.

Remember to leave some sample data. But ensure you can reproduce the problem in the stripped db before posting.

I did get rid of almost all but still it seemed bloated, i put it up on http://www.anniblue.com/master_v5.zip

The forms involved are:

master form : InmatesProfile
First subFurm : InmateCases
secon subForm : InmateHearings Subform

It autoExecs the inmatesProfile and you'll get the promt directly just press cancel or ok...and you'll see the 2nd subform (inmateHearings subFrom)as a blank square under the first subform (InmateCases)

thx again

 

[vbaInet]

Re: Starnge behaviour with form and subform

All the links seem fine but it doesn't like the fact that forms are based on tables. Always base your forms on a query.

Replace the following Record Source properties -

Main form:
Select tblInmatesProfile.* From tblInmatesProfile;

Inmates form:
Select tblInmatesProfile.* From tblInmatesProfile;

Hearing form:
Select tblInmateHearings.* From tblInmateHearings;

 

[zozew]

Re: Starnge behaviour with form and subform

ok when you say it doesnt like the fact do you mean that access has some strange bug when it comes to basing a form on a table directly or that it is wrong to do it...

Trying to learn the quirks

Thanks again for taking the time and a late happy new year to you

 

[vbaInet]

Re: Starnge behaviour with form and subform

Thanks again for taking the time and a late happy new year to you

Quite late but belated happy new year to you too

It's just common practice to base a form on a query because they are performance advantages and certain things you can do on a query recordset that you can't do on a table type.

 

[zozew]

Re: Starnge behaviour with form and subform

It's just common practice to base a form on a query because they are performance advantages and certain things you can do on a query recordset that you can't do on a table type.

well there we go, i might be able to reprogram my flashdeveloper brain to do some Access development , thanks for the heads up.
This is my first project in Access and VBA, Im actually an old flashdesigner gone flashdeveloper....now trying to do this in Access so im glad you had the patiance.

Pls dont be a stranger if you see my Nick again ...hehe i still have a few hurdles to overcome before this little baby is done.

 

[vbaInet]

Re: Starnge behaviour with form and subform

Pls dont be a stranger if you see my Nick again ...hehe i still have a few hurdles to overcome before this little baby is done.

I hope the birth wasn't quite so painful

We may see around

Happy developing!

 

[boblarson]

Your subsubform (I'm guessing is Hearing Schedule) and the subform is Inmate Cases, correct? If that is the case, you are trying to link something you can't link. The linking would need to be done via a control on the main form which is set to the caseID on the Inmate Cases form. So the selection is updated but that the Hearing Schedule form will have the correct link. You can't set a link like you were trying unless that hearing schedule form was ACTUALLY a subform ON the Inmate Cases subform.

See this link for more information:
http://www.access-programmers.co.uk/forums/showthread.php?t=45857

 

[vbaInet]

Your subsubform (I'm guessing is Hearing Schedule) and the subform is Inmate Cases, correct? If that is the case, you are trying to link something you can't link. The linking would need to be done via a control on the main form which is set to the caseID on the Inmate Cases form. So the selection is updated but that the Hearing Schedule form will have the correct link. You can't set a link like you were trying unless that hearing schedule form was ACTUALLY a subform ON the Inmate Cases subform.

See this link for more information:
http://www.access-programmers.co.uk/forums/showthread.php?t=45857

Have you managed to have a look at the subform? This is how the subform is linked to the other subform but this time with a full reference to the field which works. Yes, I also used the textbox variation and it still fails sometimes. So, using the table as the source was the culprit.

 

[zozew]

You can't set a link like you were trying unless that hearing schedule form was ACTUALLY a subform ON the Inmate Cases subform.

I kinda figured that out, from the logic of linking...but i used the form wizard and it was the one doing the linking, and i guess the two forms cases and hearings become subforms of the mainForm and because hearings is linked to cases it becomes some kind of a subForm to cases and indirect a subSubForm to the mainForm.

Using vbaInet solution of a query in the Record source solves the wird prompt. so i guess the manner of linking the forms is ok to do or is that just some fluke that it works, or is it not commen practice...?

NOTE: when i first tried it it worked but then i saved and reopened the DB the promt came back.....so maybe boblarson is correct??

 

[zozew]

See this link for more information:
http://www.access-programmers.co.uk/forums/showthread.php?t=45857

I tried the unbound textbox solution and at first when i did it it worked but when i saved and reopened the DB the promt was back but asking me for the textbox value this time....so can it be that when you open or run the DB fresh it has to have some start value that doesnt exist yet?