Blanks in lookup table

[JulieAsking]

I have 3 command buttons on a form, each relating to a combo box, which opens a lookup table consisting of 3 columns. Whenever I enter a new value for a combo box via any of the command buttons it all goes well in the Form but on checking back to the lookup table itself, there are blank cells through the 3 columned table among the new values that have appeared in the lookup table by my action back in the Form. This then creates blank areas in my combo box drop down list. Anyone have an ideas?

 

[Jack Cowley]

I am a little confused with what you are doing but it sounds like you are trying to add items to your combo boxes. If that is so then maybe this article will assist you though it doesn't use command buttons but the combo boxes Not In List event.

http://support.microsoft.com/support/kb/articles/Q197/5/26.ASP?LN=EN-US&SD=gn&FR=0&qry=q197526&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[JulieAsking]

Thanks Jack. I have bookmarked the site you kindly referred me to to take a look in depth when time allows.
I actually copied the database structure across to a new blank database to see if gaps were still created in the lookup table . Yes, I am using command buttons to open a form to input new values in combo boxes. As I said, this does input the new value in the combo box but somehow creates gaps in the lookup table. The only data I entered in this new database was using the command buttons on the form to add values to the combo boxes. When I checked the lookup values in the table the three values I added were there but in the first column there were 2 blank cells before the new value appeared, the second column got it right and the value appeared in the first cell and the third column had the value appear after a first blank cell. I wouldnt mind this other than back in the combo boxes, these blanks show up as blank "lines" and therefore make the combox box list longer than it has to be.

 

[Jack Cowley]

It sounds like the code you are using is not doing what it is supposed to do if you are getting blank fields. Without seeing the code from the command button(s) that is about all that I can surmise from your description.

 

[Rich]

Because you are adding individual records each time you try and add an entry into individual columns. Access doesn't know that the record you've added into column 2 belongs to the previous entry you made for column 1. It doesn't make sense to add data into a lookup table in this way. If you add say a Company name into Column1 then you should add the the other details into the same record for columns 2&3 at the same time.

 

[JulieAsking]

Thanks Rich. I'm totally new to Access but what you said makes sense. In deleting the original 3 column lookup table and making 3 separate lookup tables instead, I have created a problem in Form view. I changed the RowSource ppty of each combo box to each of the relevant new lookup tables and the values I have entered into the lookup tables show up in the combo box now. However, when I try to enter a new value via the command button, Access is looking for the original 3 column lookup table which I have now deleted. In re-doing the command buttons using the wizard (Open Form, selecting the form to enter a new value, Open Form and find specific data...) there are no fields in right hand window at all so there is nothing to match to fields in the left hand window. These are the steps I used successfully to create the command buttons I now need to change. Can you please tell me what I'm doing wrong here?
Thanks.