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.




4684 posts

Uber Geek

Trusted
Subscriber

#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
172 posts

Master Geek

Lifetime subscriber

  #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.



4684 posts

Uber Geek

Trusted
Subscriber

#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.

 
 
 
 


172 posts

Master Geek

Lifetime subscriber

  #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




News »

Freeview On Demand app launches on Sony Android TVs
Posted 6-Aug-2020 13:35


UFB hits more than one million connections
Posted 6-Aug-2020 09:42


D-Link A/NZ extends COVR Wi-Fi EasyMesh System series with new three-pack
Posted 4-Aug-2020 15:01


New Zealand software Rfider tracks coffee from Colombia all the way to New Zealand businesses
Posted 3-Aug-2020 10:35


Logitech G launches Pro X Wireless gaming headset
Posted 3-Aug-2020 10:21


Sony Alpha 7S III provides supreme imaging performance
Posted 3-Aug-2020 10:11


Sony introduces first CFexpress Type A memory card
Posted 3-Aug-2020 10:05


Marsello acquires Goody consolidating online and in-store marketing position
Posted 30-Jul-2020 16:26


Fonterra first major customer for Microsoft's New Zealand datacentre
Posted 30-Jul-2020 08:07


Everything we learnt at the IBM Cloud Forum 2020
Posted 29-Jul-2020 14:45


Dropbox launches native HelloSign workflow and data residency in Australia
Posted 29-Jul-2020 12:48


Spark launches 5G in Palmerston North
Posted 29-Jul-2020 09:50


Lenovo brings speed and smarter features to new 5G mobile gaming phone
Posted 28-Jul-2020 22:00


Withings raises $60 million to enable bridge between patients and healthcare
Posted 28-Jul-2020 21:51


QNAP integrates Catalyst Cloud Object Storage into Hybrid Backup solution
Posted 28-Jul-2020 21:40



Geekzone Live »

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


Support Geekzone »

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.