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

3871 posts

Uber Geek
+1 received by user: 634


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.

3871 posts

Uber Geek
+1 received by user: 634


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.


Try Wrike: fast, easy, and efficient project collaboration software
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 »

TCF and Telcos Toughen Up on Scam Callers
Posted 23-Apr-2018 09:39

Amazon launches the International Shopping Experience in the Amazon Shopping App
Posted 19-Apr-2018 08:38

Spark New Zealand and TVNZ to bring coverage of Rugby World Cup 2019
Posted 16-Apr-2018 06:55

How Google can seize Microsoft Office crown
Posted 14-Apr-2018 11:08

How back office transformation drives IRD efficiency
Posted 12-Apr-2018 21:15

iPod laws in a smartphone world: will we ever get copyright right?
Posted 12-Apr-2018 21:13

Lightbox service using big data and analytics to learn more about customers
Posted 9-Apr-2018 12:11

111 mobile caller location extended to iOS
Posted 6-Apr-2018 13:50

Huawei announces the HUAWEI P20 series
Posted 29-Mar-2018 11:41

Symantec Internet Security Threat Report shows increased endpoint technology risks
Posted 26-Mar-2018 18:29

Spark switches on long-range IoT network across New Zealand
Posted 26-Mar-2018 18:22

Stuff Pix enters streaming video market
Posted 21-Mar-2018 09:18

Windows no longer Microsoft’s main focus
Posted 13-Mar-2018 07:47

Why phone makers are obsessed with cameras
Posted 11-Mar-2018 12:25

New Zealand Adopts International Open Data Charter
Posted 3-Mar-2018 12:48

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.