Not some basic class as those I taught. Any ideas would be appreciated! It is becoming much clearer to me as to what I need to do. As I mentioned to Scott, I will be studying up on Form creation to see if I can create what you are suggesting.
It does make sense to me though! Use the Form Wizard to build your forms initially, then customize them to suit. You use a main form bound to the Orders table and a subform bound to the OrderDetails table. Any help will be greatly appreciated! This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
I have the same question 0. Report abuse. Details required :. Cancel Submit. Hi Judith. You may have created a look up field at the table level, which is discouraged practice. What you really need is create the Combobox on the form and add unbound Textboxes to display the other columns of the Combobox. How satisfied are you with this reply? Thanks for your feedback, it helps us improve the site. Ken Sheridan. There are three ways you could handle this: 1. Use correlated combo boxes so that you can drill down through the attributes, e.
The basic methodology for this is that each combo box's RowSource is an SQL statement which references the previous combo box up the line as parameter. In each combo box's AfterUpdate event procedure those down the line are assigned to Null and requeried. Use a bound multi-column combo box to select the item, and unbound text boxes which reference the Column property of the combo box to show the other columns when an item is selected. Concatenate the values of the various attributes into a single string expression in the visible column of the combo box.
You'll find examples of the first two approaches in ComboDemo. If you have difficulty opening the link copy its text NB, not the link location and paste it into your browser's address bar.
In this little demo the opening form concatenates the various address columns with the contact's personal name to allow for two or more contacts of the same name to be distinguished.
The concatenation is done by means of the ConcatValues function from the basConcat module in the database. Scottgem MVP. Also please heed DBGuy's advice on doing lookups on the form level not on the table level.
If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain ; otherwise, the DLookup function returns a Null. The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria , the DLookup function returns a random value in the domain. If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.
If more than one field meets criteria , the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:. Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.
You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query. You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based.
To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:. Although you can use the DLookup function to display a value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables and then to base your form or report on that query. Note: Unsaved changes to records in domain aren't included when you use this function.
If you want the DLookup function to be based on the changed values, you must first save the changes by clicking Save Record under Records on the Data tab, moving the focus to another record, or by using the Update method. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.
The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.
The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function.
Use an Update query with a multivalued field. Use a Delete query with a multivalued field. The display value of a Lookup field is automatically shown in query datasheet view by default. When you create a query, you can override this behavior so that the bound value is shown instead.
In this example, suppose you want to see the bound value of a Lookup field in a Headquarters table that "looks up" a New England state name. In this example, add Headquarters and NewEngland tables.
For more information, see Join tables and queries. Drag the a field along with the Lookup field to the query design grid. In this example, add the City and NEState field. In this example, use the NEState field. In the property sheet, select the Lookup tab, and then in the Display Control property, select Text Box. Result Now when you view the query in Datasheet View, you see the corresponding bound value of the display value. When you create a Lookup field, a join is created between the Bound value and the Display value.
Although you cannot directly use the Display value in a query, you can use the corresponding value from the other data source. In this example, suppose you want to use the display value of the Lookup field, NEState , in the Headquarters table in a query as criteria.
In this example, add the Headquarters and NewEngland tables. The two tables should be joined. Drag the fields that you want to use to the query grid. In this example, drag City from the Headquarters table to the first column, NEState from the Headquarters table to the second column, and StateName from the NewEngland table to the third column. Clear the Show check box of the StateName in the query grid. In the query grid, under StateName , in the Criteria row, enter Vermont.
The query criteria is based on the StateName column, which of course is the same value as the Display value, NEState , but is not shown in Datasheet View. On the Design tab, in the Results group, click Run. Result Only the row containing Vermont is shown. When you display a multivalued field in a query, you can display the complete multivalued field containing all of the values separated by commas on one row, or flattened data with a separate row for each value.
For example, suppose you have an Issues table that contains a Title field for each issue and an AssignedTo multivalued field to assign issues to people.
Drag the fields to the query design grid. In this example, drag the Title field and the AssignedTo multivalued field. Result As you might expect, one column displays the Title field and the second column displays the AssignedTo multivalued field:. Display each value of a multivalued field in one row the flattened view. Drag the fields you to the query grid. In this example, drag the Title field and the AssignedTo.
Value multivalued field. Value, appends the Value property as the string. Value to the AssignedTo field. Result The data is flattened such that the Title field is repeated and each value in the AssignedTo multivalued field is displayed in a corresponding row:.
The placement of the same criteria in the query grid in different grid columns has a big impact on the results of your query. Add criteria that displays all the values in a multivalued field in one row.
0コメント