Use combo box to edit table information

[RainLover]

I have not looked at ":Analyze" in many years. I would suggest that you use it as a guide only.

 

[hilian]

Hi Rain, I tried to follow your instructions, but I couldn’t make it work.

I started with the AgencyProgram table, and I broke it out into two tables: tblAgy and tblPgm. Each of them now has an autonumber primary key. I used field names with the suffixes PK and FK to designate primary and foreign keys . Then I recreated the query qryAgyPgm, and bound the menu form frmEditAgys, to this query. The first combo box has as its control source the field AgencyName, and the second has ProgramName from this query. The row source for the first combo box, cboEditAgy, is the AgencyName field from tblAgy. The row source for the second combo box, cboEditPgm, is a query based on the two (related) tables to limit the programs displayed to those that belong to the agency shown in the first combo box. When I select an agency for the first combo box, I get this error message.

Run-time error ‘-2147352567 (80020009)’: Cannot add record(s): Join key of ‘tblPgm’ not in record set.

Each time this happens, I reconstruct the row source query in the second combo box, and the combo boxes work until I close the form and reopen it. Then I get the same error message. When it does work, and I modify a record, Access adds a new record to the table rather than changing the existing record. I can’t delete a record.

I also reconstructed the alternate approach, unfortunately, also without success. I started with the AgencyProgram table, and I broke it out into three tables: tblAgyc; tblPrgm; and tblLookupAgcyPrgm. The third table has only two fields, a lookup to tblAgcy and a lookup to tblPrgm. At first I tried binding the menu form, frmEditAgysAlt, to the table tblLookupAgcyPrgm, but I found that if I bound the combo boxes to the two lookup fields, I’d get a type mismatch. Instead, I created a query that joined tblAgcy with tblPrgm. I set the control source for the first combo box, cboEditAgy, to the AgencyName field in tblAgcy, and the control source for the second combo box to ProgramName in tblPrgm. I created a query for the row source for the second combo box that joined the two tables, and filtered ProgramName on the contents of the first combo box. Since the tables aren’t directly related—they’re each related to the lookup table—I had to relate them on their primary keys. When I tested this and selected an agency in the first combo box, I got one choice of an unrelated program in the second box.

I’m attaching a zipped copy of the DB.

Thanks,

Henry

 

[RainLover]

I don't like saying NEVER but I will make an exception in this situation.

"NEVER use LookUps of any type, (Combos, List Boxes, and Value Lists) at the table level.

You can use them in Forms but not tables. By using a Combo Box at the table it gives the appearance that something is stored when it is not. If you get rid of these and reconstruct your Row Source in the Form's Combo Box you should solve your problem.

Other than that why use such a short name as tblAgy. Use tblAgencies this is not too long or short. It is descriptive and unambiguous. I have fixed just this one and reposted your Database for you to see what I have done.


Also you were missing the Field AgencyFK in your query.

Use this at the top of every Module.


Option Compare Database
Option Explicit

 

[hilian]

Rain, Thank you very much for your corrections and for redoing my DB. I have some questions, though. You substituted a text box for the second combo box. That would allow the user to add a program name, but not to delete or to correct the name of a program that is already in the table. How do I set it up so that the user can do each of these things?

I also have a few questions about your instructions. You say, “NEVER use LookUps of any type, (Combos, List Boxes, and Value Lists) at the table level.” I thought I was following your instructions from several posts ago when I set up the table that way. You said to create a main table (which I called tblLookupagyPgm). The table should have the following fields, MainPK (Autonumber); LookUpFK (Number, Long) Foreign Key; and Other Fields as Required. Could you explain this a bit more? There’s a point I’m missing and I think I need to understand it.

Also, could you tell me a bit more about not using a Combo Box at the table level? I put the cascading combo boxes on the form, you’re telling me I did something wrong. I’m not getting what it was. You also said that I should reconstruct my Row Source in the Form's Combo Box. I tried several different ways, none of which worked. How should I have reconstructed it?

You also pointed out that I was missing the Field AgencyFK in my query. In some of my tries I did include it, but it didn’t seem to make any difference. How should it have been used in the Query? Finally, you said that I should use it at the top of every module</SPAN>. There weren’t any modules in the DB, so if you could say more about that, it would be very helpful.

I really don't like imposing on your time by asking you so many questions, but I do want to understand.

Again, many thanks for your help,

Henry

 

[RainLover]

I have never said to use Lookups at Table level. You must be misreading or making assumptions.

Look at this link then if you don't understand anything ask again. But this link should clear up a few things.

http://access.mvps.org/access/lookupfields.htm

 

[RainLover]

Option Compare Database
Option Explicit

Sorry, bad layout in my reply. The above two lines should be use at the top of every Code Module.

Hope that explains better.

 

[RainLover]

Analyze named the fields. I guess you can't just accept whatever decisions it makes.

Access has to have something as a default. It can't possibilly come up with a name that fits your Naming Convention. That is also why Access uses ID as the default Primary Key. It is up to you to fix this type of thing.

 

[RainLover]

I found that if I bound the combo boxes to the two lookup fields, I’d get a type mismatch.

The Foreign Key in the Table you are trying to write to is of Data Type Number. You cannot write Text to a Number Field. You need to write the Primary Key which is Autonumber to the Foreign Key.

Your Combo can have more than one Column. The first is the Bound Field which is the PK which has a column width of ZERO. So although it can't be seen that is what is stored. The second Column could be Text and that is what you see. But as stated it is the Bound Column that is saved.

The Column widths could be 0cm, 5cm, 4cm. This would show two columns in the drop down box.

Hope this helps.

 

[RainLover]

You said
You substituted a text box for the second combo box.
I replied with
I have fixed just this one and reposted your Database for you to see what I have done.


In other words I left it to you to fix because the Row Source was wrong.

 

[hilian]

Hi Rain, thanks for all of your posts. I had to work through them a couple of times, but I think I’m getting it.

Part of my confusion about lookups is that when access displays a foreign key in a table it calls the field Lookup To _____. I read the link, and think I’m clear, although the authors seem to be saying that any lookups, which including foreign keys, are a problem. I think that’s not what they’re saying, because without lookup fields you couldn’t relate tables, but I’m not sure.

I also added the lines:

Option Compare Database
Option Explicit

to the code module. I was familiar with Option Explicit, and I looked up Option Compare Database, so now I think I understand why they’re there. I hadn’t explicitly created a module, and I didn’t realize that the code statements that were created for different purposes constituted one.

I recreated cboEditPgm. When I put ProgramName as the first column in the row source and AgencyFK as the second, with the criteria for this column (the second) as match the contents of the first combo box, the cascading combo boxes work correctly. If I put either AgencyPK in the first column, following the way you set up the first combo box, or AgencyFK as the first column, with ProgramName as the second, the program name doesn’t show in the box, even if I set the column widths to 0 and 3.48. what am I doing wrong here?

I also tried to edit the data—I tried this when I had AgencyFK as the second field for the second combo box. I found that I could create a new record but not edit an existing record. I checked the AgencyFK field in the table and found that Access had created a new record with a new number rather than using the same number (corresponding to an agency). For example, the "Agency Thirty eight" lookup is 27. All of the "Agency thirty-eight" programs have this as a lookup, but when I modified “Agency thirty eight program one” into “Agency thirty eight program one and a half.” the number was 60.

I think I’m close, but there seems to be one more step. Can you see which it is?

Henry

 

[RainLover]

Henry

It looks like it is time to post a copy of your DB again.

The FK in one Table matches the PK in the other. Relationships are created by using these two fields. They are really just pointers to the record that holds the information you need.

The FK and the PK should not be visible to the user. This is why in Combo Boxes the PK is always Column One and the width is set to Zero and the Bound Column is One.

You do not use the FK in a Combo Box, but you do store the value of the PK in the FK field of the Record Source of the Form

 

[hilian]

Hi Rain, Thanks for taking another look. When I put ProgramName in the first column and AgencyPK (with the criterion statement) in the second, the combo boxes work properly. When I reverse it, and put AgencyPK in the first column. it doesn't work. This is what happens: When I try it with just AgencyPK and the criterion, I get only one number (the agency primary key) in the second box. When I add ProgramName to the second column, without specifying column widths, I get the agency primary key number in the second box the correct number of times but not the agency name. When I set the first column width to 0 and the second to 3.88, I get nothing in the second box--I even tried copying from the first box in case I had mistyped the widths, but the same thing happens. When I set the first column to something other than 0, I get the agency primary key number the correct number of times but, again, not the agency name. The same happens when I leave the column width blank, although shouldn’t Access set the width of each column as an equal fraction of the overall width of the combo box? It appears that the problem is that the second column isn’t showing.

Before I zipped the current copy of the file, I removed the extrra tables and the alternalte version of the frmEditAgys menu.

Thanks,

Henry

 

[RainLover]

What is the name of the Form in question, and the Combo Box you are having problems with.

It is 3:00 AM here so going back to bed in a minute or two.

Will have a look later today when I am awake.

 

[RainLover]

A quick look at the DB shows you have not followed the instruction I suggested in post #19.

You need to do so. Most importantly by get rid of all look ups at the TABLE level.

Please fix and repost the Database.

 

[hilian]

Hi Rain, Thanks for looking at the DB so quickly. I got rid of the table called tblLookuptoAgyPgm. I also got rid of the table called switchboard items, which isn't being used.

Now I'm confused. So far as I can see, there is only one lookup left, and that's AgencyFK in tblProgram. I don't think you're telling me to get rid of that field. Are you? If that goes, then what connects the programs to their agencies?

I went back to post # 19. You said, "You should not have Lookups at this level. Tables. Combo Boxes are for Forms, not Tables." I'm having trouble understanding what you're telling me I need to do when you say Combo Boxes are for Forms, not Tables. I know that you can't put a combo box into a table. Could you say some more about this?


Thanks,

Henry

 

[RainLover]

Big misunderstanding.

Don't get rid of Tables or Fields. Get rid of the actual Combo Box within the table. Change them to text boxes.

Do you understand now.

 

[hilian]

Hi Rain,

I guess I'm more confused than before. I'm looking for combo boxes within the tables and I'm not seeing them. You're not telling me to change the combo boxes on the form to text boxes, so where do I look for the combo boxes that are causing the trouble?

I'll be out of contact for a couple of hours, but I'll check later this evening.

Thanks,

Henry

 

[RainLover]

Hope this explains. Change "Display Control" from Combo Box to Text.

 

[hilian]

Hi Rain, I tried viewing and printing the image with three different programs (word processor, paint and presentation), and none of them made the type readable. Could you send it again.

Thanks,

Henry

 

[hilian]

Hi Rain, Thanks. I tried to read and print the image with three programs (word processor, paint, and presentation), and none of them made the type readable. I even tried a magnifying glass. No luck! Could you send it again?

Henry