Missing row source of a field in a linked table

[moon-ray]

I have a database that I am developing. I am using Access 2010 on a Windows 7 platform with Service Pack 1.



The database has been split into a data database and one containing the forms, reports and code. I started to have problems with a combo box in a form after changing the controls row source to display records that I had marked as OBE. In troubleshooting, I noticed that in the design view of the table that is the source for that form the row source for the field was blank.

I closed that database and opened the database that holds the data. I opened the problem table and viewed the problem field. The row source definition was there. I have relinked the fields, but the row source still is blank.

I copied the row source from the data database into the forms database. All worked until I closed the database and opened it again. As I expected, that problem field row source was blank since changes cannot be saved to a linked table..

The data database is encrypted. I unencrypted that database and relinked the forms database to it. The row source still was blank. I checked the data database, and the row source there. Below are two screen captures of the row source property.

Does anyone know how to correct this problem?

Thanks, in advance.

Row source in the data database:

Row source in the form database:

[June7]

Do you have lookups in other tables? Do they work?

Advise not to build Lookups with alias in table. http://access.mvps.org/access/lookupfields.htm

Build combobox on form. Does the combobox on form work?

[moon-ray]

Thank you for the lookup advise. The comboboxes work.

[moon-ray]

How was this thread marked as solved. It was not solved. I still do not know why the row source was missing.

[moon-ray]

I need to expand my answer about the comboboxes work. When I open the form, the comboboxes work. When I use a technique that I use regularly, I change the row source to display includes OBE records or do not by adding to the WHERE clause of the SQL statement that makes up the row source. One combo box loses it value, but if I click on the down arrow, the previous value is available in the drop down values. I cycle through the Show OBE and Not Show OBE and the second drop down box whose row source is changed goes blank. It did not the first time I cycled through the cycle. I have copied the new SQL statements from the immediate window in the Code windows and pasted them into a query design window. The SQL statements display the correct records including the record of the bound column in the fields that are going blank. I am not finished troubleshooting this issue and am not asking for help here.

I just want to know why the row source for a field in a linked table disappears.

[June7]

Because the combobox works, I assume you also set a link to the lookup table?

I never build lookups in tables. So I just tested by setting a lookup in a backend and then looking at the link in frontend. This all works for me. Cannot replicate the issue so can't explain why it fails for you.

Does it matter if the combobox works? Wasn't that a solution?

[moon-ray]

Does it matter if the combobox works? Wasn't that a solution?

As I wrote above, the comboboxes work until I modifying the row source. I am still troubleshooting. I am not sure if the problem is not associated with the problem that the row source is not available in the linked table. Until the link table problem is resolved, I can not rule out it is causing the problem, the problem exhibits itself by causing both issue, or it has nothing to do with it.

Second, I have set up the table with the information so that when I add that field to a form or report, the display is correct without having to change several properties each time the field appears.

Lastly, this should not be happening.

I am just asking if someone has seen this problem and knows a solution. That you tried to reproduce the problem, I thank you for your time and effort to help. I have been using this technique for over 10 years without anything like this ever happening. While I only write personal databases I use myself, 10 years ago, I wrote two financial databases that were used in production at my company at the time. With all of this experience, I never have seen this happening before. I am just trying to find out why.

[June7]

I tried again to replicate the issue. The modified RowSource is immediately reflected in the link.

Only thing I can think of is some kind of corruption.

[accedeholdings]

Your solution is epic. I tried it on our end and it worked. Thanks for this solution.

[moon-ray]

The problem must have been something corrupt in the data database. I solved the problem by importing the tables back into the database and split the database again. Everything is working.