Geekzone: technology news, blogs, forums
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

3961 posts

Uber Geek
+1 received by user: 673


Topic # 27202 17-Oct-2008 13:01
Send private message

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,, 100
2,, 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,, 100, Mince Pie
2,, 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?

Create new topic
167 posts

Master Geek
+1 received by user: 6

  Reply # 171794 17-Oct-2008 13:46
Send private message

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.

3961 posts

Uber Geek
+1 received by user: 673


Reply # 171811 17-Oct-2008 15:00
Send private message

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.

167 posts

Master Geek
+1 received by user: 6

  Reply # 171820 17-Oct-2008 15:42
Send private message

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.

Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:

Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:

Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:

News »

Microsoft Dynamics 365 Business Central launches
Posted 10-Jul-2018 10:40

Spark completes first milestone in voice platform upgrade
Posted 10-Jul-2018 09:36

Microsoft ices heated developers
Posted 6-Jul-2018 20:16

PB Technologies charged for its extended warranties and warned for bait advertising
Posted 3-Jul-2018 15:45

Almost 20,000 people claim credits from Spark
Posted 29-Jun-2018 10:40

Cove sells NZ's first insurance policy via chatbot
Posted 25-Jun-2018 10:04

N4L helping TAKA Trust bridge the digital divide for Lower Hutt students
Posted 18-Jun-2018 13:08

Winners Announced for 2018 CIO Awards
Posted 18-Jun-2018 13:03

Logitech Rally sets new standard for USB-connected video conference cameras
Posted 18-Jun-2018 09:27

Russell Stanners steps down as Vodafone NZ CEO
Posted 12-Jun-2018 09:13

Intergen recognised as 2018 Microsoft Country Partner of the Year for New Zealand
Posted 12-Jun-2018 08:00

Finalists Announced For Microsoft NZ Partner Awards
Posted 6-Jun-2018 15:12

Vocus Group and Vodafone announce joint venture to accelerate fibre innovation
Posted 5-Jun-2018 10:52 to launch Kogan Mobile in New Zealand
Posted 4-Jun-2018 14:34

Enable doubles fibre broadband speeds for its most popular wholesale service in Christchurch
Posted 2-Jun-2018 20:07

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.