field does not display in subform in access 2003

thenelson
thenelson used Ask the Experts™
on
Access 2003
I have a continuous subform. When I change the record displayed in the parent form, the first field in the subform does not display (see screen shot below.) When I click in the field, it immediately displays.

I have tried adding repaint, requery, refresh and recalc of the subform and the field in the subform to the current events of the subform and the parent form with no improvement. If I set focus in the current events, the field is deleted (I have code that deletes empty fields in the lost focus event for the field).

Any other ideas?
missing-field.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You don't happen to have another control behind that subform do you?  Or possibly displaying in front of it?  Maybe the label that is associated with the subform?
Top Expert 2009

Commented:
How is the subform linked to the main form?  What are the record sources of the main form and subform?  Can you post the database so we can examine it?
>You don't happen to have another control behind that subform do you?  Or possibly displaying in front of it?
I double checked. I have two fields in the subform that are on top of the problem field but they are both hidden and don't cause any problem with the following records in the continuous form.

>How is the subform linked to the main form?  
subform linking
>What are the record sources of the main form and subform?  
Main form:
SELECT [Patient Information].*, [Complaint List].Status, NewPatientData.[Records From 0], NewPatientData.[Received From 0], NewPatientData.[Records From 1], NewPatientData.[Received From 1], NewPatientData.[Records From 2], NewPatientData.[Received From 2], NewPatientData.[Records From 3], NewPatientData.[Received From 3], NewPatientData.[Records From 4], NewPatientData.[Received From 4], NewPatientData.[Records From 5], NewPatientData.[Received From 5], NewPatientData.[Records From 6], NewPatientData.[Received From 6], NewPatientData.[Records From 7], NewPatientData.[Received From 7], NewPatientData.[Records From 8], NewPatientData.[Received From 8], NewPatientData.[Records From 9], NewPatientData.[Received From 9], NewPatientData.[Talk With Patient], NewPatientData.[Mail Letter], NewPatientData.[Profile Received], NewPatientData.[Remind Patient], NewPatientData.[Make Chart File], [Complaint List].Insurance1, [Complaint List].Insurance2, [Patient Information].BalIsCorrect
FROM ([Patient Information] LEFT JOIN [Complaint List] ON [Patient Information].ComplaintID = [Complaint List].ComplaintID) LEFT JOIN NewPatientData ON [Patient Information].PatientID = NewPatientData.PatientID;
Subform:
SELECT tblPatientProviderList.ComplaintID, tblPatientProviderList.ContactID, tblPatientProviderList.DescriptionID, tblProviderDescription.AllowedContactTypes, tblProviderDescription.Order, IIf([Contacts].[ContactTypeID]=18 Or [Contacts].[ContactTypeID]=19,[OurID],IIf(Len([LastName] & "")=0,[CompanyName],[LastName] & ", " & [FirstName] & (IIf([Suffix],", "," "))) & [Suffix]) AS [Output], tblPatientProviderList.DescriptionID, tblPatientProviderList.ProviderListID, tblProviderDescription.Description FROM ((tblPatientProviderList LEFT JOIN Contacts ON tblPatientProviderList.ContactID = Contacts.ContactID) LEFT JOIN [Contact Types] ON Contacts.ContactTypeID = [Contact Types].ContactTypeID) LEFT JOIN tblProviderDescription ON tblPatientProviderList.DescriptionID = tblProviderDescription.ProviderDescriptionID ORDER BY tblProviderDescription.Order;

>Can you post the database so we can examine it?
Not very easily. The front end is 42,356,736 bytes with almost 35,000 lines of code. The parent form has 7 subforms. I imagine it would take me a few hours to isolate the parent/subform to upload it and I'm not sure the problem would continue after doing that.

For some strange reason, after having this problem for the past four years, the problem just stopped occurring. I have no idea what changed.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
"I double checked. I have two fields in the subform that are on top of the problem field but they are both hidden and don't cause any problem with the following records in the continuous form."

Are you certain that some of that code is not making these controls visible?  If those fields are hidden anyway, try sliding them to the right of the combo boxes in that form and set their TabStop properties to No.  This will place them outside the visible area of the subform, but they will still be available.
Top Expert 2009

Commented:
Could you post the database (or a cut-down version of it)?  Continuous forms behave in odd ways, so it would be easier to figure out what is going on if I could see the form.
"Are you certain that some of that code is not making these controls visible?
It is a continuous form. If they were visible they would show up on all the records. Also they would only cover a small portion of the control that is not showing up.  I moved them anyway and there was no change.

"Could you post the database (or a cut-down version of it)? "
The front end is over 205 MB, has over 35,000 lines of code, about 70 forms and over 40 table relationships. I did try to pull that form with the sub form out to a separate database but gave up after an hour, so I guess in a word, No.
Top Expert 2009

Commented:
You may have to recreate the subform and/or the main form.  Try making a new subform with only a few controls, and place it on a new main form with just enough controls for the needed links.  If that works, add more controls gradually and see what happens.
Hamed NasrRetired IT Professional

Commented:
What happens if you reorder fields in subform?

Switch positions of 1st and 2nd fields, and change their tab order; or just try to change tab order of the mentioned two fields.
"Switch positions of 1st and 2nd fields"
The top one is blank. What was the 2nd field is now on top and it is blank. What was the 1st field is now the second one displayed and it is showing up.
> For some strange reason, after having this problem for the past four years, the problem just stopped occurring. I have no idea what changed.

so it looks like you don't need help anymore?
>>For some strange reason, after having this problem for the past four years, the problem just stopped occurring. I have no idea what changed.

The problem stopped for about one week. It is back now.
The best would be if you could reproduce this problem on fresh database, with just these two forms in place and several records.

Is there any VBA code in the either form?
Hamed NasrRetired IT Professional

Commented:
Without a sample database, it is not easy to spot the problem.

Try this:
Duplicate the 1st field, modify its tab order to be the first.
    Run form and check.

Hid the field and try again.
>Duplicate the 1st field, modify its tab order to be the first.
I don't understand. It is a continuous form so there is only one visible field. I did turn off the tab stops on the non-visible fields.
Could it be that when it shows up, the first field is focused, and the highlighting when it's focused makes it invisible, i.e. white on white? if you focus it again and select its full text, will it be visible?
If the field is blank and I click on it (give it focus), the text instantly shows up. There is no conditional formatting set.
it may be that it's already in focus, with full text selected, then when you click, you unselect the text. Make another field first in tab order so that field is in focus initially, and see if it helps.
It is the only visible field so I cannot make another first in tab order. With full text selected, the text is visible.
On the picture in your initial question there's field "coordinator" with invisible text, followed by several other fields - "Referred by" and more. Then how is it the only visible?

Also: change it from combobox to textbox and see if the problem disappears. If it does then we will have to look at how combobox is specified.
"On the picture in your initial question there's field "coordinator" with invisible text, followed by several other fields - "Referred by" and more."

Yes the "coordinator", "Referred by", etc is a textbox - I forgot since I set them up to look like labels. But the subform is a continuous form so each line is not a separate textbox or combobox. The textbox with "coordinator", "Referred by", etc is locked so I cannot set it to the first tab sequence. I should not have used the term "field" when I meant "control". The only control that is not locked and is visible is the combobox.

When you mentioned "tab order", I went into the subform and turned off the tab stop of every control except the combobox. So far, the problem has stopped!! I'll give it a few days to see if it holds.
In case we return to this - so I understand correctly: on the screenshot in the beginning we see 5 records, each with 2 fields , one going into the "label" which is in fact locked textbox - those are the ones with texts "Coordinator" etc; another going to the "value" in the combobox on the right, with values like "Page, James MD" etc. Right?
That is correct.

So far, since I have turned off the tab stops for every control except the combobox (with values like "Page, James MD" etc), the problem has stopped. Doesn't make sense to me why this would make a difference but if the problem does not return in a few days, I'll assign your comment where you mentioned tab stops as the solution.
Turning off the tab stops of the hidden controls solved the problem. The reason for the problem is I created a textbox that is not hidden that covers up the text area of the combobox under it. Normally only the down arrow of the combobox shows since the textbox is on top of it. When I click on the text box, VBA changes the focus to the combobox. I did this because it is a complicated dynamic subform that allows on the fly changes of what is displayed. I designed the subform a few years back so I forgot what I did.

 Dale Fye was the first commenter to mention tab order but for some reason it did not click with me. When Vadim Rapp mentioned it, it changed the tab order settings which fixed the problem. So I am splitting the points to both of you.

Thanks for your help. This has been driving me nuts for years.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial