How does autofilled form fields info end up in table fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a databes which basically is an expansion or the template Order
Entry from scratch. All of the relationships are ok, the forms are working, I
enter data from combo boxes and the following related fields automatically
display the appropriate info. However when I review the datasheets and
subsheets in tables, the information is not in the fields. I also can't get
the info to print in a report. Is there an expression to get this info to
appear in the datasheets and where would I put it?
 
You used the Lookup Wizard in your table for these fields? It does a great
job of messing things up, so that what you see is not what you get. Details:
http://www.mvps.org/access/lookupfields.htm

The solution for your report is to include the lookup tables in the query,
or else provide criteria based on the stored value (which you probably can't
see.)

If you include the lookup tables in your query, and some of the records have
no entry in that field, you need to use outer joins so as to get all
records. Details:
http://members.iinet.net.au/~allenbrowne/casu-02.html
 
OK, I did use the lookup wizard for these fields and I read the pages you
sent. I am still confused. In order to create a form where you use a combo
box to find information, it is selected, and the following related text boxes
automatically reveal the related information, I have created a multi-table
query and used form wizard, selecting various fields and tables and had a
subdatasheet/form created. Only info physically typed in (=Date() didn't put
the info in the date field on the table) or clicked from the combo boxes is
placed in the tables. I can't figure how the form will automatically enter
the appropriate information, yet it won't automatically enter into the
corresponding table fields. I even tried datasheet style form, the fields on
the datasheet form work, but again going to Tables and looking at the
datasheet view there is no info in the same fields. Maybe it isme but this
makes no sense?
 
Combo boxes in Access can display multiple columns when dropped down. The
Column Count property indicates how many columns it should display.

They have a Bound Column property which tells which of the columns is
actually the stored value. Typically it's the first column (i.e. the
BoundColumn property is 1).

They also have a Column Widths property which specifies how wide each column
should be displayed. If this property is set to:
0"; 1"
then the first column is not displayed because it is zero-width. Instead,
Access shows what is in the 2nd column. Access is not *storing* one thing,
but *displaying* something different.

To see what is being stored there:
1. Open your table in design view.
2. Select the lookup field.
3. In the lower pane, click on the Display Control tab.
4. Change the Column Widths so the Bound Column is not zero-width, e.g.:
0.5"; 1"

Once you can see what data is actually in the field, you can then begin to
figure out how to set the criteria in your query to match it.
 
I guess my explanations have unfortunately brought us down the wrong path. I
already knew what info was in multiple columns of the combo box. After
selected, Column 1 info (primary Key Hidden) appears in the combo box and is
stored in all related tables. The subsequent text boxes on the form which are
related to the combo box automatically fill with the correct data. This
related dated, however, doesn't "cascade" into any of the tables. I also have
trouble with cascading the date. If it is manually typed into a field on a
"Table" then it cascades, yet autodate (=Date() ) or manually typing in the
form will not cascade the information into any of the tables. I guess to
compare, hopefully this will clarify, if you look at the preloaded template
for order entries and select a product, the unit price automatically enters
into the field from the product table. this value is found in the approriate
Unit Price field on all tables, datasheets, subdatasheets, forms,
reports,etc. I have expanded this concept from scratch to include item
descriptions, unit of measure and varied pricing. my product ID appears in
all related fields, but the related information does not? It only appears in
the form and subform created.
 
In general, you don't want the same information stored in multiple tables.
With your example, the product price is likely to change, and so it makes
sense to use the AfterUpdate event procedure of the ProductID combo to
lookup the current price and offer it as the default price for a new order.
You can see how Northwind does this - using DLookup() - if you open the
Orders Subform in design view, and examine the AfterUpdate event of the
combo.

Store the product description also? Hmm, might be needed if you really want
the flexibility to call a spade something else. But the unit of measure
probably should not change, unless you are working across different
countries that use different measurements. The cases where you want to store
the same information in different tables are very limited.

In any case, the AfterUpdate event of the combo is where you assign the
value to the text box bound to the other fields.

Is that what you were asking?
 
Back
Top