General discussion


Microsoft access

By Siliconwombat ·
Im trying to build a database for an advertising agency but have run up against a problem. Im trying to get a combo box to relate to other pieces of information from the same table. In other words you select '1' in the combo box another box displays the next field for that record eg 'a'. like selecting a product and it displays the price? Any help much appreciated. If you know for certain it cant be done please tell me thats worth the points if you can suggest an alternative as well!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to Microsoft access

How Microsoft Access links main forms and subforms
You usually use a subform to display data from tables or queries that have a one-to-many relationship. For example, a Categories table and a Products table have a one-to-many relationship ? each category can have more than one product.

When you create a form and subform based on tables that have a one-to-many relationship, the main form shows the "one" side of the relationship and the subform shows the "many" side of the relationship. The main form is synchronized with the subform so that the subform shows only records related to the record in the main form. If you use a wizard to create a subform, or if you drag a form, table, or query from the Database window to another form to create a subform, Microsoft Access automatically synchronizes the main form with the subform if both of the following are true:

You have defined relationships for the tables you select, or you have defined relationships for the tables underlying the queries you select.

The main form is based on a table with a primary key and the subform is based on a table that contains a field with the same name as that primary key and with the same or a compatible data type and field size. For example, if the primary key of the table underlying the main form is an AutoNumber field and its FieldSize property is set to Long Integer, the corresponding field in the table underlying the subform should be a Number field with its FieldSize property set to Long Integer. If you select a query or queries, the underlying tables for the query or queries must meet these conditions.
Note Microsoft Access uses the LinkMasterFields and LinkChildFields properties of the subform control to link the main form and subform. If, for some reason, Microsoft Access doesn't link the form and subform, you can link the forms yourself.

Collapse -

by Siliconwombat In reply to

Poster rated this answer.

Collapse -

by Siliconwombat In reply to

Thanks. Im sure its all bang on right. unfortunately it was a little too technical so useless really but thanks for trying any way.

Collapse -

by nicrite In reply to Microsoft access

If I am reading this correctly, you want the price to appear on the same form if a product is selected from a combo box...right?

If this is so, then what I have used successfully is as follows:

Create a combo box on the form - lets call it cbo1. Create a text box on the form - txt1.

For the rowsource of the combo box, create a query that contains the name of the product and the price - for instance:

Select [itemid], [itemname], [itemprice] from itemtable

In the properties of the combo box set # of fields to 3 and set fieldwidths to 0;3;0 This will only show the item names.

In the afterupdate event of the combo box, write the following code:

me!txt1 = me!cbo1,column(2)

This will show the cost in the text box - remember the column number starts at 0.

Hope this helps



Collapse -

by Siliconwombat In reply to

Hi thanks for all your help and ui think you know what to do but... im a bit dumb really and i cant get it working. could you email please at twhateley at hotmail dot com. and i will pas on whats wrong so far. thanks again

Collapse -

by Siliconwombat In reply to Microsoft access

This question was closed by the author

Related Discussions

Related Forums