Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 1st, 2005, 11:20 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple tables with relationships Please Help

I am very new to VB.net and I am using an access database that has one main table with other tables extending off of it almost like pick lists. they populate certain fields in the main table with a drop down menu to chose from with 1 to many relationship.

I wrote code from a web form to populate the textboxes with the information only from the main table. the problem is now that if someone wants to update the selected record it won't happen due to the fact that the other tables make up the fields in the main database as drop down lists.

Can someone explain to me what I can do??
 
Old February 1st, 2005, 01:35 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are you talking about a combo box? Because for a combo box you set can set the control source to be your data table and the row source to be another table to fill the combo box

 
Old February 1st, 2005, 01:39 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I know that you can populate the textboxes on your web page with items from the Pick Lists (foreign key tables) but you have to watch out which columns are bound. The Pick Lists have at least two columns, an autonumber primary key, and the meaningful value you want your users to see. The pick list table, and the main table, will only recognize the autonumber PK associated with the meaningful value as the proper value to be updated.

Assume your mian table is:

tblEmployees
PK autonumber
txtFirstName
txtLastName
txtCity
txtState
FK to tblZipCodes

and the pick list table is:

tblZipCodes
PK autonumber
txtZipCode

and assume there is data in the second table like this:

PK ZipCode
1 20515
2 21043
3 20904

If your user picks "20904" from your text box, what you need to pass to the main table is "3", or the record number for "20904."

So in your code, instead of passing the meaningful value your user selects (20904), you need to pass it's key.

When you create this system in Access, you will see that the Key field is hidden, the user selects the meaningful value, and then the Bound Column is set to 1, or the PK field. That is the value that is passed.

HTH



mmcdonal
 
Old February 1st, 2005, 01:56 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by marcin2k
 Are you talking about a combo box? Because for a combo box you set can set the control source to be your data table and the row source to be another table to fill the combo box

Yes I am talking about a combo box on the main table that populates using the other table.

 
Old February 1st, 2005, 01:57 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by marcin2k
 Are you talking about a combo box? Because for a combo box you set can set the control source to be your data table and the row source to be another table to fill the combo box

Yes I am talking about a combo box on the main table that populates using the other table. How do I set the control and row source....

 
Old February 1st, 2005, 02:01 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The table is more like:

tblEmployees
PK txtID
txtFirstName
txtLastName
txtCity
txtState
FK to tblZipCodes

and the pick list table is:

tblZipCodes
PK txtZipCode

 
Old February 3rd, 2005, 08:20 PM
Registered User
 
Join Date: Feb 2005
Location: Peoria, IL, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I'm understanding your situation correctly, you're using the zip code table to normalize your data for most frequently used zip codes, but you don't want to limit the entries to what's in the zip code table.

First, I recommend setting up the zip code field as text, since some people use the 4-digit extension format -- i.e. 60439-1032

If you go to the Data tab of your Combo Box properties, your Control Source should be the zip code field in your Employees table (the data source for your form), your Row Source Type should be Table/Query, and your Row Source tblZipCodes. Since your tblZipCodes table has only one field, your Bound Column is 1. If you want users to be able to enter in a zip code not in the list, make sure your Limit To List is set to No.

When "Limit To List" is no, the user can select from the options or type in something different. Typing in something different will not add the option to the list for next time, but it will store it in your main table. You can use validation to ensure typed-in entries meet criteria, if that is a concern.

Regards,

Bonnie





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple relationships from table Vince_421 Access 2 May 30th, 2007 11:34 AM
Polls tables relationships nabeelalkaff BOOK: ASP.NET Website Programming Problem-Design-Solution 2 August 10th, 2006 12:18 AM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Avoiding multiple Relationships Marden Lee SQL Language 4 February 16th, 2005 08:30 AM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.