Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 March 28th, 2005, 02:15 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Options in unbound combo box not saving?

I'm back. After having resolved one issue, I now have another. I have 1 bound and 4 unbound combo boxes on a form. When I enter data into the form and save it, the option I selected in the bound combo box is saved, but the options I select in the unbound combo boxes are not saved to the record. The unbound combo boxes are linked, so the options chosen in 2 drive the options available in the other 2 and 2 are hidden until a certain option is chosen in the first. I hope that there is a way to save the data without changing the process I have in place. Any advice is appreciated, but Randall, if you are out there, I need you again!!!

Jen
 
Old March 28th, 2005, 04:54 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Either:

1) Make those comboboxes bound to the table where you want the data stored; OR

2) Put BOUND textboxes on the form that correspond to the fields in question (either visible or invisible). When a combobox is chosen, put something similar to this on the combobox's AfterUpdate Event (substitute my fake names with your real names).

Me.txtMyTextBox = Me.cboMyComboBox


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old March 28th, 2005, 05:28 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, my Access neophyte status is definitely showing. I think I have a fuzzy idea of what is wrong and your advice is a good starting point. I have the 4 unbound combo boxes. Then, because it was easy, I created the rest of my fields in Table Design mode and they are all in the same table. The form is bound to this OtherFields table, so there is no place on this table for the values of the combo box fields to be entered when the record is saved. I'm going to play with this a bit, but I will most likely be back, so please don't abandon me!

:D

Jen
 
Old March 29th, 2005, 04:42 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm still stuck guys. Do I create separate tables to save the combo box info to? When I try to bind the combo boxes to a table(s), I must have done something wrong, it didn't work. If the form is bound to a table that can save the values, will that work?

I hope my confusion isn't contagious...

Jen

 
Old March 30th, 2005, 08:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The easiest way is to make the form bound to a table that will hold the values. Saving the values to a table that the form is not bound to is possible with a little programming. For simple databases, the first suggestion is the simplest way to go.
 
Old March 30th, 2005, 10:06 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Are the values in your combo boxes in a look up list, or from a look up table?

There are different structures here that you could be working with that will have different solutions:

1. You created the combo boxes and typed in the values you wanted yourself in the wizard.

2. The combo boxes are looking up values in seperate tables (or all in one table).

The easiest thing for a beginner to do, if you have created your database structure first, is to click once on your table, and then click the AutoForm button.

What is your database structure, BTW? This will also determine your solution here.

Also, are you looking up data (for reports), or are you doing data entry with this form?



mmcdonal
 
Old March 30th, 2005, 12:25 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks! This helps. I probably didn't start off with my database in the best way to achieve my goal. The combo boxes all have their own tables. The bound fields on the form were in the same table, and the form is bound to this table. So I added fields into this table for my combo boxes and changed the Control Source of each combo box to the corresponding field in the table. Now the data from the combo boxes is saved, but not the values I want. Each combo box table has an Autonumber field that I use to establish the relationships as the first column. This autonumber value is what is being saved. How can I set it up so the value that shows in the drop down of the combo box is what is saved (second column in table)?

Getting there.... :-)

Jen


 
Old March 30th, 2005, 12:55 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hiya,

   Where did you get the bad joo joo, jen? It seems to me that you ARE there, not just getting there. You want the combo boxes to be bound to the foreign key, not to the meaningful data from the look up table. This relationship will enforce referential integrity. If you bind to meaningful data, then you could lose referential integrity.

   What you want to happen is to display the meaningful data where you want to see it, but to keep the foreign key relationship intact.

   On your form, make sure that the first, or bound column (FK), is 0" wide, so that it is not displayed.

   When you make a query, be sure to put the foreign key table in the query designer, and select the meaningful data column to be displayed in your query, and it will use the FK in the background for referential integrity.

   Then if you make a report based on the query, the meaningful data will be displayed. You will need to make your reports on queries, not tables, otherwise the FK will appear as the data, and that is probably not what you want.

HTH

mmcdonal
 
Old March 30th, 2005, 04:16 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't think I have that much bad joojoo. I just liked the term and I'm a cartoon junkie. (think Ren and Stimpy) I'm glad that I'm "there" but your advice has be a bit vexed. This is my first attempt at any sort of database design and at using Access. So much of the Access lingo is unfamiliar. For my first 2 combo boxes, the Primary Key and first column in their corresponding tables is ChangeID and TypeID. The next colunm is the actual values that are displayed in the dropdown. In the Type table, I have 2 additional columns, with the same data that is in the Change table. I use the ChangeID field to establish the one to many relatioship. (more info, see my previous post, How to Hide a Combo Box, from 3/26) I have my Primary Key column and any other columns that I don't want displayed hidden. (set to 0 in Column Widths) Do I need to set the fields that I have the combo boxes bound to as the foreign key?

Thanks for your patience!

Jen
 
Old March 30th, 2005, 04:29 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Actually I was thinking George of the Jungle (the cartoon) where I first heard joo-joo.

Yes you can save the data in the combo boxes, but what do you want to save? Meaningful data, or the PK?

And you will have to do some trapping since there are all sorts of permutations for errors.

See, if you create a table that looks up to the look up tables, and then create the form, you won't have this problem. I am not sure why you are using unbound combo boxes on a data entry form. This is data entry, right?

Anywho, you can do it with your existing form and unbound combo boxes. You just need to pass the right data to the table programmatically. You would pass it based on the PK of the bound table for the record you are submitting.

Also, if you are passing data to a report/query, then it is different.




mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Count in combo box(display results in text box) mboyisis Access 4 April 4th, 2008 07:08 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Unbound combo box / listbox problem VBM2 Pro Visual Basic 2005 3 March 13th, 2007 11:04 AM
filtering records on subform from an unbound combo jd_erd Access 3 February 12th, 2007 11:21 PM





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