How do I bind a text box with a field, which doesn't belong to form's "Record Source" table, through the Design View?
Example: I have "Order.cust_id" (Record Source=Order) and I want to display "Customers.name". I believe it is trivial but I have no experience with MS Access. I tried to use the text box "Control Source" property but no luck.
views:
347answers:
3
A:
You could create a new View (e.g. OrdersAndCustomerNames), select all the columns you want to use in the form, then instead of using the Order table as Record Source, you would just switch to OrdersAndCustomerNames. You say you have no experience with MS Access, so I am guessing you are not building anything huge and overly complicated, so I would do it this way. I am quite sure it can be done more elegantly but this will do for now.
Peter Perháč
2009-06-01 12:36:12
Thanks but I need more *control* because I'll use 4 to 5 tables.
Nick D
2009-06-01 12:54:03
+2
A:
One method would be to convert the text box to a combo box. Then set the row source to include both the cust_Id and the Customer.Name from the customer table. SQL statement example
Select Cust_ID, Name From Customer
Order By Name;
By setting the number of columns to 2 and the column widths; the first column as zero (i.e. "0;6") then the foreign key would be hidden from the user and the customer name would be displayed.
Note this method does force you to have limit to list set to true.
Also you do end up with a drop down list which may not be what you want.
Mark3308
2009-06-01 12:44:01
I want to display on a form fields from various tables and I have the foreign IDs in one table. I am going to use text boxes and combos. I dont care if I must use VBA to accomplish it.
Nick D
2009-06-01 12:51:49
OK I am glad to help. Note you can set this up in the table design view - then when you create a form it would default to a combo box. But the downside to this is you hide the foreign key values from yourself.
Mark3308
2009-06-01 14:10:59
Bad advice, Mark3308 -- lookup fields in table design are terrible because they cause so many problems (you think you can query on the displayed value, but you can only query on the value that is behind it, and you can't see that). Combo boxes are a UI object, and tables and queries are data objects, not UI objects (even though one can make due with them as a UI). Combo boxes belong on forms and nowhere else.
David-W-Fenton
2009-06-03 03:01:37
OK agree that lookup fields in a table design are bad - tend to confuse the hell out me (hence my comment about the downside). However if you have lots of forms to create using a particular column it can save time and effort setting up a new combo box each time. You can always remove the look up column once the forms have been created. But thinking about it the best approach would probably to just copy a combo box on a form to paste around.
Mark3308
2009-06-03 12:27:42
+1
A:
You can use DlookUp as the control source of a textbox:
=DlookUp("[Name]", "Customer", "ID=" & Cust_ID)
Syntax: What to look up, table name, where statement
The Where statement should follow the rules for Jet SQL, which means that you must use delimiters if the field is text or date format.
Note that Name is a very bad name indeed for anything. I suggest you rename the field immediately before things get worse.
It can be useful to know the error(s).
Remou
2009-06-01 13:36:20
Can you please update the code with Customer.id, Customer.name, Order.cust_id keywords. VB is not my forte and I get some error msgs :)
Nick D
2009-06-01 13:56:24
Done, with some notes. DlookUp is fairly standard for forms and you will get information on it in MS Help. The expression builder can also help with built-in functions.
Remou
2009-06-01 14:11:31