Field in a form reverting to first item of that type.

[NerdyName1]

Hello I know I haven't been here a while but I have a nagging issue with a database I administer.

We have a form field with individual combo boxes and text boxes set up, it is pulling information in from our source correctly however when attempting to select an item which has multiple values of the same name but its unique identifier is different it is reverting to the first item in that list.

For example;

3 Items of, Cheese, Cheese and Cheese - but each have other different properties for example where the cheese is coming from. If you were to select the 3rd Cheese in the list it only displays the first item and all of the properties for that item.

I have been looking in the After Update settings in the VB but everything I have tried has gotten me nowhere.

Can anybody please help!

Thanks!

 

[CJ_London]

sounds like you do not have a primary key, or if you do, it is not the bound column for your combo

 

[NerdyName1]

sounds like you do not have a primary key, or if you do, it is not the bound column for your combo

Both of these I have done and it is still refusing to behave!

Thanks

 

[CJ_London]

OK, then you'll need to provide more information.

Please provide the sql to the rowsource to your combo. And advise these settings for the same combo

column widths
bound column
column count

 

[NerdyName1]

OK, then you'll need to provide more information.

Please provide the sql to the rowsource to your combo. And advise these settings for the same combo

column widths
bound column
column count

SELECT tblBAcU.SellingName, tblBAcU.OfficialName, tblBAcU.Business_AcU, tblBAcU.[Short Name], tblBAcU.Continent, tblBAcU.Country, tblBAcU.MainResort, tblBAcU.LocalResort, tblBAcU.System FROM tblBAcU WHERE (((tblBAcU.SellingName) Is Not Null)) ORDER BY tblBAcU.SellingName;

column widths : 5cm;5.755cm;3cm;0cm;0cm;3cm;3cm;3cm;2cm
bound column: 1
column count: 9

Thanks again!

 

[CJ_London]

As I suspected, despite you saying you had done it, you are bound to the sellingname field and as far as I can see, you do not have a primary key

Assuming your table does have a primary key (autonumber) called say BAcUPK then modify your rowsource to

 SELECT [COLOR=red]tblBAcU.BAcUPK[/COLOR], tblBAcU.SellingName, tblBAcU.OfficialName......

and set the following

column widths :0; 5cm;5.755cm;3cm;0cm;0cm;3cm;3cm;3cm;2cm
bound column: 1
column count: 10

Note that if you are referencing the columns in your combobox from elsewhere (another control or vba code), then the column number used will need to be incremented by 1 since they are all moved over by one.

If you don't have a primary key, you will have to add one

If your primary key is there, but just not identifiable to anyone bar your self, then change the bound column to the column it is in - for example, if it is your system field, then the bound column needs to be 9.

 

[NerdyName1]

As I suspected, despite you saying you had done it, you are bound to the sellingname field and as far as I can see, you do not have a primary key

Assuming your table does have a primary key (autonumber) called say BAcUPK then modify your rowsource to

 SELECT [COLOR=red]tblBAcU.BAcUPK[/COLOR], tblBAcU.SellingName, tblBAcU.OfficialName......

and set the following

column widths :0; 5cm;5.755cm;3cm;0cm;0cm;3cm;3cm;3cm;2cm
bound column: 1
column count: 10

Note that if you are referencing the columns in your combobox from elsewhere (another control or vba code), then the column number used will need to be incremented by 1 since they are all moved over by one.

If you don't have a primary key, you will have to add one

If your primary key is there, but just not identifiable to anyone bar your self, then change the bound column to the column it is in - for example, if it is your system field, then the bound column needs to be 9.

Hello, thank you so much for this and your patience I do have a primary key and it is the Business_AcU field does this need to be the first instance here; feel I am almost there with it.

Thanks once again!

 

[CJ_London]

 Hello, thank you so much for this and your patience [IMG]http://images.access-programmers.co.uk/forums/images/smilies/smile.gif[/IMG] I do have a primary key and it is the Business_AcU

in that case, since it is included in your rowsource all you need to do is change the bound column from 1 to 3.

 

[NerdyName1]

Thank you!

I have done this and I am now getting a Run Time error on my VB 800200009 you must enter a value in the business_acu field

I know I have loads of questions I am very grateful for your help

Thanks

 

[CJ_London]

you said

I do have a primary key and it is the Business_AcU

primary keys

a) are usually an autonumber field and

b) they cannot be null

ergo it is not a primary key

Before you continue further, you need to do some research on table design, normalisation and relationships.

 

[NerdyName1]

Thanks for all your help