Database Help Please

Forums › Desktop computing › Database Help Please

alasta

6888 posts

Uber Geek
+1 received by user: 3362

Trusted

Subscriber

#27202 17-Oct-2008 13:01

Hi, everyone. I'm currently dabbling with Access and SQL but I'm a novice with this sort of stuff and I'm hoping that there may be a guru here who can give me some advice. I have a couple of database tables which look like this:

Key, Domain Name, Product ID
1, alasta.geek.nz, 100
2, alasta.net.nz, 101

Product ID, Product Description
100, Mince Pie
101, Steak Pie

I have then constructed a query which joins them together like this:

Key, Domain Name, Product ID, Product Description
1, alasta.geek.nz, 100, Mince Pie
2, alasta.net.nz, 101, Steak Pie

When I use a userform to update data in the above query, I want to change 'Mince Pie' to 'Steak Pie' and have the Product ID field in the first table update to reflect the change. However, in practice, what actually happens is that the Product Description field in the second table changes while the first table remains unchanged.

What do I need to reconfigure so that this change would update the first table rather than the second table?

xcubed

195 posts

Master Geek
+1 received by user: 17

ID Verified

Lifetime subscriber

#171794 17-Oct-2008 13:46

You don't need to base your form on that query.

Instead, make sure that Product ID is defined as the primary key of the second table, then open the first table in Design View and choose Lookup Wizard from the Data Type dropdown for the Product ID field. Follow the steps to make the field a lookup into the second table.

When you create your form, base it off the first table and your Product ID field will automatically appear as dropdown list.

alasta

6888 posts

Uber Geek
+1 received by user: 3362

Trusted

Subscriber

#171811 17-Oct-2008 15:00

Thanks for the reply.

I have checked that the product ID is the primary key and the first field in the second table. When I open the first table in the designer view I can drop down the data type menu for the product ID field but it just gives me selections of different data types - I can't find a 'lookup wizard' anywhere.

However, I have found a 'lookup' tab down the bottom with some options. I have set:
Display Control : Combo Box
Row Source Type : Tables/Views/Functions
Row Source : Table: product_plans
Bound Column : 1
Column Count : 1
Column Heads : No
List Rows : 8
List Width : Auto
Limit to List : No

Now when I use the wizard to create a userform based on the first table I get a dropdown box for product ID but there is no content in the dropdown box.

I'm not too sure where to go from here.

xcubed

195 posts

Master Geek
+1 received by user: 17

ID Verified

Lifetime subscriber

#171820 17-Oct-2008 15:42

What version of Access are you using? I'm using 2003.
The Lookup Wizard is an optional component, so you may not have it installed. As you pointed out, you can set the lookup properties manually, but it's not quite so straightforward.

The Column Count field determines the number of columns from the Products table that will appear in the list. In this case, I suggest that you set the Column Count to 2 and the Column Widths to "0cm;5cm" so that the Product ID field is hidden and the Description field is shown.

I recommend you change the Display Control option to List Box rather than Combo Box, as this will ensure that you can only choose Products that already exist. You should also create a relationship between the Product ID fields if you haven't already, to ensure referential integrity.