Unanswered: Form using fields from a Linked Database
I have a database that is linked to another database. I wanted to include some of the fields from the other database on my form but keep getting errors. I'm new to this so I'm probably doing it wrong. When I go to try to set the control source for the field in my local form (that should be getting information/linked to the field in the linked database), it brings up the expression builder and I get
Then when I go into the form and try to fill in data, it gives me an error saying it's locked by the expression. I guess what I really need to know is how do I enter that field name properly so that it isn't an expression? Appreciate any help offered. Thanks.
In order to take advantage of data in another database, you will want to link to those tables in the database. Go to File/Get External Data/Link Tables and choose the database that has the data you want. Then select all of the tables you want to use. You should see the tables in your current database. Each linked table should have an arrow pointing at the name (this signifies the table is linked and not actually in the database).
Once the tables are linked, then you would just use them as if they were in the database. Also, FYI, if you delete a table with an arrow pointing at the name, you aren't actually deleting the table, just the link to it. Think of it as a shortcut to a program. Delete the shortcut and you aren't deleting the program.
Thanks for the reply. I already have the table from the other database linked properly and I also have set up the relationship as one to one. The only problem is that when I try to set the control source, it doesn't work. That's where I get the error. When I go to the properties and use the drop down menu, the fields from the drop down menu do not include the fields from the linked table. So, I proceed to use the expression builder which gives me the errors saying that the control is bound to an expression. Any other ideas?
In that case, you can only have one record source for a form. The only way you can have edittable data displayed from several sources is to have subforms in the main form.
If, however, you only want to display a field, you can use VBA to look up and set the value for a field, or you can set the field source equal to something returned by a DLookup function.
Keep in mind, DLookups are relatively slow. Personally, I would do it in a subform if the data is related. Otherwise consider multiple forms. In general, the data being modified/displayed on one form is somehow related and therefore subforms should work for you.