usatrfe
asked on
Using external database table in Access 2007
I am using Access 2007. I have used a very old version of Access many years ago so I cannot claim to be an expert.
I have a main table called “Test Booking System”.
On this table is a field called “Serial Plate No”
I also have a link to a database with a table called “Serial Nos”
I have a form that displays each record in “Test Booking System”.
Each item can be modified – this works OK.
The problem:
I want the item “Serial Plate No” to display a field/record from “Serial Nos”.
(The field name in "Serial Nos" is called "Serial No")
But the “Serial Plate No” box on my form shows “#Name?”
There is an Arrow to the left of the linked table so it must be linked OK - right?.
How do I get data from “Serial Nos”.
(A bit of VBA code would be good if there are any suggestions.)
I have a main table called “Test Booking System”.
On this table is a field called “Serial Plate No”
I also have a link to a database with a table called “Serial Nos”
I have a form that displays each record in “Test Booking System”.
Each item can be modified – this works OK.
The problem:
I want the item “Serial Plate No” to display a field/record from “Serial Nos”.
(The field name in "Serial Nos" is called "Serial No")
But the “Serial Plate No” box on my form shows “#Name?”
There is an Arrow to the left of the linked table so it must be linked OK - right?.
How do I get data from “Serial Nos”.
(A bit of VBA code would be good if there are any suggestions.)
ASKER
Thank you for that.
I used the expression builder to make this:
"= [SerialNos]![Serial No]"
(I have noted that I typed Serial Nos with a space but that was only my typing error. There is no space!).
If the expression builder can find the data elements then surely the expression has been constructed correctly?
I have tried to build expressions for other items but the #Name? is always the result.
I used the expression builder to make this:
"= [SerialNos]![Serial No]"
(I have noted that I typed Serial Nos with a space but that was only my typing error. There is no space!).
If the expression builder can find the data elements then surely the expression has been constructed correctly?
I have tried to build expressions for other items but the #Name? is always the result.
Can you run the underlying data in a query and see all of the data in datasheet mode? This is the first step in troubleshooting this.
ASKER
I have created a Simple query and the datasheet view clearly shows the data that is in the external database (SerialNos). (sorry if this message has been duplicated).
Ok, but what I'm asking is have you run the SAME query (joining the databases and tables) that you use behind the form incorporating ALL of the tables and fields desired on the form at the same time. I get that you can see the SerialNos.SerialNo in a query, but have you gotten the query to show Test Booking System.Serial Plate No AND SerialNos.SerialNo all in the same query.
ASKER
Sorry about my misunderstanding.
This time I have run a simple query but selected a field from my main "Test Booking System" as "Serial Plate No" AND the linked "SerialNos" as "SeriaNo".
The only thing that I have had to do is make a relation ship between these to fields (this didn't exist before!).
This time there is NO data displayed in the datasheet view for this new query - very odd!.
This time I have run a simple query but selected a field from my main "Test Booking System" as "Serial Plate No" AND the linked "SerialNos" as "SeriaNo".
The only thing that I have had to do is make a relation ship between these to fields (this didn't exist before!).
This time there is NO data displayed in the datasheet view for this new query - very odd!.
This is the root of the problem. All of the fields have to be available in the underlying data otherwise, the expression [SerialNos]![Serial No] doesn't mean anything and will produce a #Name? error.
ASKER
Maybe I have missed something but surely if the external database is linked then the fields in that external table are available?.
Could you expand a little on how 'All of the fields have to be available in the underlying data'.
I may show my ignorance here but it sounds like i need to create extra columns in "Test Booking System" then create relationships to fields in "SerialNos".
Or am I off the track?!
Could you expand a little on how 'All of the fields have to be available in the underlying data'.
I may show my ignorance here but it sounds like i need to create extra columns in "Test Booking System" then create relationships to fields in "SerialNos".
Or am I off the track?!
The data is certainly available, but it looks like the joins are not working. On what fields did you create these "relationships" - or joins as they are called. You linked the two tables but what fields from the two are bringing them together? Meaning - what field in one table has the same value in the other table? The data type might not be the same, or one field could be a text field and the other a numeric field, etc.. If you don't have a way to "link" or join them, a query cannot bring the two together to be shown in the same query. Usually you have a Key and a Foreign Key relationship whereby each table has a unique ID field (usually numeric) and the other table refers to this ID in some way.
If the underlying data (all of the fields you want to show in the form) shows NO data, then that's the reason why you are getting that #Name? error. You can't just list fields in the expression builder on tables that are not joined. Relationships are not the same as joins - especially when the two tables will never be "related" because there isn't any "joining" data to link them.
If the underlying data (all of the fields you want to show in the form) shows NO data, then that's the reason why you are getting that #Name? error. You can't just list fields in the expression builder on tables that are not joined. Relationships are not the same as joins - especially when the two tables will never be "related" because there isn't any "joining" data to link them.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I haven't yet solved my problem as yet but armed with your kind help I believe that I can progress to a resolve.
Thank you for the information.
Pete
ASKER
Sorry about the delay
http://office.microsoft.com/en-us/access-help/i-see-name-displayed-in-a-control-HA001181447.aspx