Use combo box to edit table information

[hilian]

On a form I have a set of dependent combo boxes. I want to the user to be able to use these to edit the information in the underlying tables. I set the row source in each box to the appropriate table, but when I set the control source to the table I want the user to be able to edit, the same table as the control source, the field shows “#Name?”, and when I click on an item from the drop-down list, it doesn’t go into the field. I know I entered the table and field information correctly because I used the Expression builder to enter it. If I leave the box unbound, the item does enter the field, and I can edit it, but it doesn’t change the item in the table.

How do I set this up?

Thanks,

Henry

 

[RainLover]

If you could change the details of the contents of a Combo Box then you would change every preceding record.

Is this what you want to do?

Maybe you should explain in simple terms what you want to do.

 

[hilian]

I want to allow the user to to edit information in two tables: to add or delete records or to change the contents of a particular field. I'm using dependent combo boxes because there is a one-to-many relationship between the two tables that I want to allow the user to edit.

 

[RainLover]

On a form I have a set of dependent combo boxes

We are in Guessing Mode here as it could be one of many things.
I assume NONE of these boxes are working. Please confirm this.
Suggest you do a Requery on each of the Boxes. This should be done on the Form's On Current Event.
Can you post some pics of the properties of the boxes and anything else you may feel will help.

 

[hilian]

There are two combo boxes. The second, one cboEditPgm depends on the value in the first, cboEditAgy. That part works. What doesn't work is that when I set the record source to the field in the table that needs to be edited, I can't edit it with the combo box.

I'm attaching a word document with screen shots of the Data properties of the two combo boxes and the event procedure for the cboEditAgy combo box, the first one. I am requerying the second box in the event procedure of the first.

Thanks,

Henry

 

[RainLover]

The Box cboEditPgm.
The Control Source is a Field from the Query/Table that is the Record Source of the Form.
You should be able to select this simply by using the Drop Down Arrow in the Line Control Source.

 

[hilian]

I tried, and for some reason, when I click on the down arrow, nothing shows. The line remains blank. That's why I used the Expression Builder. Could there be something in the way I set up the combo boxes that explains why I can't simply select the table and field?

Thanks,

Henry

 

[RainLover]

Check out the Row Source. Does it have any Data.

 

[hilian]

Thanks Rain,

By row source, do you mean the data property or the table? The table has data, and the combo boxes populate as long as they are unbound. The problem occurs when I specify the table and the same field as control source. I just noticed that I'm using a query as the row source and the underlying table as the control source. Could that have anything to do with the problem?

Henry

 

[RainLover]

Are the Tables for the Combo and the Form the one and same or are they different.

 

[hilian]

The form is unbound. I was setting up a menu system, but it didn't occur to me that this form isn't a menu. Should it be bound to the query that unites the two tables (one to many relationship)?

 

[RainLover]

Unbound forms are for other Databases not for Access. Access uses Bound Forms because it is so much easier and quicker to develop. There is room for Unbound Forms such as a Menu.

So bind your from to the Table or as I prefer a Query.

The Combo should be bound to the Table that holds the Data that you wish to insert into the Form. So you have two different Record Sources. One for the Form and another for the Combo.

You should be looking at storing the Primary Key from the Combo's Record Source and storing that as a Foreign Key into the Form. This is not mandatory that it has a Primary key especially if you are only storing one column of data. But I use an Autonumber as the PK as a matter of standardisation.

Trust I helped and not confused.

 

[hilian]

Thanks, Rain. I think I understand it, except for storing the Primary Key from the Combo's Record Source as a Foreign Key into the Form. Where do you store a primary key in a form? Since there are two tables, and each has its own primary key (both are Autonumbers), which one do I use?

Otherwise, I think I understand what you are saying. I can bind the form to the query that unites the two tables, and each combo should be bound to its own table. I can't try it tonight, but I will as soon as I can tomorrow morning. Then, I'll get back to you.

Henry

 

[RainLover]

Assume you have a Table that you use as a lookup for the Combo. It would look something like this. Change the Names to suit your situation.
tblLookUp
LookUpPK (AutoNumber as Primary key)
LookUpData (Text)
OtherData (Could Be Any Data Type) Not Required, Yet on the other hand there could be several.

tblMain
MainPK (Autonumber)
LookUpFK (Number, Long) Foreign Key
Other Fields as Required.

The Form is Bound to tblMain. This is called the Record Source.
The Field LookUpFK is part of the Record Source. The Combo Box's Control Source is bound to this Field.

The Combo also has a Row Source which is the Table tblLookUp and the Field is LookUpPK. LookUpPK is stored in tblMain, LookUpFK.

Does this help. Keep asking questions until you get a full understanding.

 

[hilian]

Rain, thanks for your new instructions. I tried to follow them, but I’m not sure I did correctly.

I started with my original table, called AgencyProgram, which has three fields: ID, AgencyName and ProgramName. I used Analyze to create three individual tables. The main table is tblAgyPgmLookup, which has three fields, an ID number, Lookup_to_tblPrgm, and Lookup_to­_tblAgcy. The two lookup fields that Access created weren’t numeric, they were text: the agency and program names. The other two tables are tblAgcy and tblPrgm. I went ahead anyway, and bound the form to tblAgyPgmLookup.


For the first combo box (cboEditAgency), I had three choices as a control source: ID, tblAgcy_ID and tblPrgm_ID. I chose tblAgcy_ID. Then as a row source I chose the AgencyName field from tblAgcy. I tried it and it worked.

Then I did the second combo box(cboEditPgm). I chose tblPrgm_ID. As the row source. I had to create a query since I need the second box to filter records based on the first. This is the query:

SELECT tblPrgm.ProgramName FROM tblAgcy INNER JOIN tblPrgm ON tblAgcy.ID=tblPrgm.ID WHERE (((tblAgcy.AgencyName)=Forms!frmEditAgys!cboEditAgy)) GROUP BY tblPrgm.ProgramName ORDER BY tblPrgm.ProgramName;

When I tried it, I got this message for the first combo box, “The value you entered isn’t valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the field size setting permits.” I got the same error message for the second box. What did I do wrong?

Before I got your last post, I tried it with a query, which you suggested was the way you prefer, and had better success, but in the end there was a different problem. I started with the original table, AgencyProgram, and used Analyze to create two tables, tblAgy and tblPgm: tbl Pgm has a lookup to tblAgy. Then I created a query with AgencyName from tblAgy and ProgramName from tblPgm. I bound the form to the query, and that allowed me to have AgencyName from the query as a control source for the first box and Program Name for the second one. I used the tables as row sources and created a query for the second box to filter records by the contents of the first one. It worked. The problem is that I seem to be able to add records to the tables, but not to edit or delete them: any change I make results in a new record.

Which is the best way to go, and what's the next step?

Many thanks,

Henry

 

[RainLover]

Henry

You are missing one simple thing. I wish I could tell you what it is but I have no idea.
The best thing for you to do is to post a copy of your database and I will have a look at what you are doing.

Delete all the stuff that is not needed. Do a compact and repair then zip and post it.

Make sure your attachment is in A 2003 or I cannot help. I do not have 2007 or 2010.

 

[RainLover]

Also there is a link in my signature to Sky Drive.

There you will find a sample of Combo Boxes that should help.

Please give it a try.

 

[hilian]

Hi Rain, Thanks for looking over the database. I’m attaching a zipped file. I haven’t looked at the link yet. It took a while to sanitize the tables. Aside from deleting fields from one table, I had to change data in another one.

There are two forms that relate to the problem. They are frmEditAgys andfrmEditAgysAlt.

The first one, frmEditAgys is bound to a query that unites the agency and program tables (tblAgy and tblPgm). The combo boxes are bound to the AgencyName and ProgramName fields from the query. The row sources are the fields in the underlying tables. The combo boxes on this from work, but any change in the data, even a deletion, results in a new record.

The other form, frmEditAgysAlt, is bound to a table (tblLookupAgcyPgrm) that contains lookups to two other tables (tblAgcy and tblPgrm). The combo boxes are each bound to fields from tblLookupAgcyPgrm, and their row sources are the fields in the related tables. These combo boxes don’t work.

Again, many thanks for taking a look.

Best,

Henry

 

[RainLover]

A few problems.
First look at tblPgm. Then Field tblAgencyID. This is a Field not a Table so the name is incorrect. Change it to AgencyID. Then look at the Combo Box. You have constructed this incorrectly. Open the Row Source. It refers to a Table that does not exist. Having done that delete the Combo Box. You should not have Lookups at this level. Tables. Combo Boxes are for Forms, not Tables.

Suggest you tidy up all your Tables. You also need to apply some good naming conventions. ID is not Good.

Have a look here for a paper on Naming conventions. http://www.access-programmers.co.uk/forums/showthread.php?t=225837

There is no sense in proceeding until you fix these problems. Each Table should have a Primary Key of Autonumber. In the Table mentioned I would call it ProgramNamePK. If you read the article you will see what I mean.

There is a good chance that you will fix your problems while doing this. If not then repost a new Copy with the suggested changes.

Also I see that you are using the built in Switchboard. You can continue to use that but will be better off using your own.

Hope this helps a lot more.

 

[hilian]

Hi Rain, Again, thanks so much. I see I have some work to do. I found the paper on naming conventions and printed it. I need to work through the changes.

Actually, something interesting happened here. I used Analyze to create the tables from an original table with both fields. Analyze named the fields. I guess you can't just accept whatever decisions it makes.

I'll work through your suggestions, and with any luck, that will be it. If not, I will send you a new file.

You've been a lifesaver, many thanks,

Henry