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




4160 posts

Uber Geek
+1 received by user: 760

Trusted
Subscriber

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, 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?

Create new topic
170 posts

Master Geek
+1 received by user: 8


  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.



4160 posts

Uber Geek
+1 received by user: 760

Trusted
Subscriber

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.

 
 
 
 


170 posts

Master Geek
+1 received by user: 8


  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:



Geekzone Live »

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


Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



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.