field does not display in subform in access 2003

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
LVL 39
thenelsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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?
0
Helen FeddemaCommented:
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?
0
thenelsonAuthor Commented:
>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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
"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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
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.
0
thenelsonAuthor Commented:
"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.
0
Helen FeddemaCommented:
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.
0
hnasrCommented:
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.
0
thenelsonAuthor Commented:
"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.
0
Vadim RappCommented:
> 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?
0
thenelsonAuthor Commented:
>>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.
0
Vadim RappCommented:
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?
0
hnasrCommented:
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.
0
thenelsonAuthor Commented:
>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.
0
Vadim RappCommented:
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?
0
thenelsonAuthor Commented:
If the field is blank and I click on it (give it focus), the text instantly shows up. There is no conditional formatting set.
0
Vadim RappCommented:
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.
0
thenelsonAuthor Commented:
It is the only visible field so I cannot make another first in tab order. With full text selected, the text is visible.
0
Vadim RappCommented:
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.
0
thenelsonAuthor Commented:
"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.
0
Vadim RappCommented:
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?
0
thenelsonAuthor Commented:
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.
0
thenelsonAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.