Hello everyone! I am new to this forum and have a question that has been troubling me all day. I did plenty of searching and I couldn't find any answer, although it may be a simple one.

So I have a combo box in a form that refers to a table that has a long list of companies. When I test out the combo box and pick one of the companies, it adds that company (with a new Primary key ID) as another record in the company table. So that company is now in the table twice. Is there any way to use the combo box to refer to a record that is already there instead of creating a new one off of your selection? I just need it to do this so that the existing primary key for that company is carried over as a primary key for the relating table... not create a whole new record and primary key like it is.

I am sorry if this is a noob question... Thank you so much for your help!

You need to make your combo box Unbound, meaning the combo box Control Source property should be blank.

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Is your form bound to the same table as the combobox? Try the following:

1. Create a new combobox by using the Combobox Wizard.
2. Select the third option when prompted by the wizard and complete all the screens that follow.

Is that what you wanted?

Just my 2 cents...

Oh I am using Access 2007 with the latest updates. Sorry I forgot to include that information!

Darnellk, I tried your idea and it was succesfull in not adding a duplicate record to the company table. But... it did not carry the company primary key (from the company that I choose in the combo box) into the related tables foreign key feild.

I will try theDBguy's suggestion and make a new combobox next. Thanks for the help guys!

Anymore ideas?

So creating a whole new combo box had the same results... Is it possible to carry over the primary key of an existing record with a combobox? If I could get it to do this, i'll be set! Thanks again!

Ok. I think we'll need to back up a bit. dbGuys suggestion should work by taking you to the correct company, and then your related records (usually in a subform) would automatically get the companyID because the subform would be linked to this field.

Because this doesn't work for you, could you please explain your form layout in more detail, or provide some screenshots?

From what you described earlier, you may need to create some VBA code that will automatically assign they CompanyID to your related records on the AfterUpdate event of your combo box. However, I'm willing to bet there is an easier way if you can provide some more information.

Just a quick question....

View the Properties of the Combo Box and click on the Format Tab, what do you see in the Column Count and the Column Widths? Then under Data Tab, what is the number in the Bound Column. The Bound Column is the Column number that will either be placed in a Field when selected or the Field number used as the Criteria. Is the Bound Column number the number of the Primary Key Field you want?

RAZMaddaz

I have a "1" in the column count and nothing in column width for the combo box format. Then under the data tab I have a "1" in the bound column.

Thank you for helping!!

So things are still not working?

Sorry I wasn't more clear in my last post. I am still having the same problems with the combo box. It is probably due to my lack of experience building forms....

I know the combo boxes main function is in entering records. But I am wondering if I can use it to refer to existing records so that the primary key of the selected record is used as the foreign key for any related tables.

I hope that makes sense. Thanks again!

Hi,


Yes, that is very possible but we can't tell you what to do without understanding your setup. Are you able to post a zip copy of your db with test data?

Just my 2 cents...

Sure thing, here is a copy! ...don't pick fun at my database too much haha ... I haven't had much exposure with access. Thanks again! ...oh and there are a few forms on here, but the one that I am referring to is the Company data entry form and its subforms. The other forms I am trying to work in an update event so that they come up seperately if a feild is updated.... thats for another day lol

Hi,

It's probably not everything you wanted yet but take a look now.

Just my 2 cents...

Wow.... Thank you so much!! I was able to copy what you did onto my version and it works flawlessly!! I never would have been able to figure that out on my own! I didn't even understand some of those events you wrote in... I have a lot to learn thats for sure!

What a great forum community. Thanks DBguy, you just made my friday!

The main reason you were having the problem was if you look at the Combo Box you originally had, it had the Control Source as your Company Field, which is why when you selected something from the Combo Box, it would added it to your Table. The theDBGuy made the Combo Box an unbound Combo Box and then created a Query as the Row Source and the data for the Combo Box. When a company is selected in the Combo Box, the Macro goes to the First CompanyID with that ID and the data in the other Forms with the Company ID are then shown too.

Hi summer,




Glad to hear you were able to get it to work. We are all happy to help.

Good luck with your project.

Sorry to bring this back from the dead... but I am experiencing another related problem. Just as above, I have a combo box with company names, and a combo box with years. The company names combo box does a great job at looking up a company name in my company table, however, when I try to add more than one date record (using the period combo box) to a single company name, it overwrites the previous date record. How can I get these combo boxes working so that I can add multiple records for one company? Thanks for the help!

Can you post a zip copy of the latest version of your db with test data?

Sure thing! Here is my database. Using the form "Company Data Entry" I am unable to enter multiple records for one company. For example: I picked the first company from the list, then from the year list I pick 2010 and enter some test data for the relating subforms. That works fine. Then I go back to the form and pick the same company, but this time ill pick a different year say 2009 and enter some test data. This doesn't work, and the more years I try to add, the more records become replaced. I have already tried a few things, like messing with the data entry property, but that hasn't worked. Thank you so much for your help!

...also, if you are in my database and have some free time... could you take a look at my relationship between my subforms and their relating seperate forms (the parent market subform and the child submarket forms)? I can't seem to get that to work either...

Thanks!!

Hello forum,

I added my question here because it seems to fit with this topic. I am having a problem with a database I am working on. I am new to Access and have done a lot of reading on the subject. However I have come across a problem that I cannot solve despite the numerous articles I have read. I believe that everything is set up correctly (which it must not be since it is not working) and cannot figure out how to fix the issues I am having. I am hoping someone will be able to provide some advice.

In Access 2010 I am trying to create a form for an end user to enter all project details. The form is really a master form with two subforms. I am trying to create a combo box on a subform and put the subform on the master form.

In my efforts to normalize my data I created separate tables for any values that would have been entered in the main table but would have repeated.

The three tables I have are as follows:
tblMain
pkMainID
ProjectName
Amount
fkProjectTypeID
fkYearID

tblProjectType
pkProjectTypeID
ProjectType

tblYear
pkYearID
Year

I established one to many relationships enforcing referential integrity between tbl Main/tblProject Type and tblMain/tblYear.

First, is this the correct approach to normalization? From everything I have read it seems that this is correct; however, I have seen examples of databases for Addresses where I would have figured there would be a separate table for States but the database is not set up that way.

On my master form I have text boxes for Project Name and Amount and combo boxes to select the Project Type and Year. Both combo boxes for Project Type and Year are set up the same. They are each separate forms I created and dragged onto the master form as subforms. The row source for the combo boxes is SELECT(table.tableID), (table.fieldname) FROM (table)
Bound column is set to 1, column count is set to 2, column width shows 0”,1”. The master child link between the forms is set to the fkID field from the master table and the pkID field from the child table.

I am having a couple of problems when I test the form and use the combo boxes to select values.
1-I get the following error message “You tried to assign a null value to a variable that is not a variant data type”

2-When I test it the list in the combo box one of two things happens. One, the list gets appended with the equivalent primary key number for each new selection. For example, I select Project A, I receive the error message I pointed out above, I click OK and the number 1 is added at the end of the list of Projects. The list now shows Project A, Project B, Project C, Project D, 1. When I look at this in the table the 1 that was added to the list has a primary key of 5.
The second thing that sometimes happens is the first name in the list changes from the value to the primary key of whatever was selected in the previous record. For example, I select Project A, when I move to the next record on the master form instead of clicking on the drop down arrow in the combo box and seeing Projects A-D the list shows 1, Project B, Project C, Project D.

3-After I test data entry in the form and I look at the master table, there are no values being stored in the foreign key fields.

Thank you.

Hi,



What it sounds like to me is that you do not have the combobox bound to the correct field in your table. Besides, based on the table structure you have presented, I don't see a need for subforms. Also, you probably don't need a separate table for "years" too.

Just my 2 cents...

It will definitely be a lot easier if I don't have to have subforms and everything can go in one table. I was just trying to normalize all the data based on what I read which suggested that any repeating values should be stored in their own table.

Thank you for your help!

Hi,



You read the rules right but there are many levels of normalization. I recommend going only up to 3NF.

Just my 2 cents...

summernightdrive,

Here's your db back. Let me know if it's any close to what you want.

Just my 2 cents...

Wow DBguy you sure do make Access look easy! I tried and tried to get this functionality but could never get it to work! Thank you so much!

Hi,



Glad we could help. Good luck with your project.