Combobox displays wrong value

[die_laffin via AccessMonster.com]

Hi

I'm in the process of trying to update a database. At present they are
storing the name of the Division selected form a table (Divisions) into
another table (Main). I'm trying to change this so that only the Division ID
is stored in the 2nd/Main table.

But when looking at or updating a record it shows the wrong value

eg:
Division Table:
ID Value
5 Balliol

Main Table:
Division
5

When I look at the record in a update form the combobox is set to:
ID Value
6 Cumbria

The combobox is based on:
Control Source: (Main Table) Division
Row Source Type: Table/Query
Bound Column: 0

SQL is:

SELECT DISTINCT Divisions.Division, Divisions.ID
FROM Divisions INNER JOIN Main ON Divisions.ID = Main.Division
WHERE (((Divisions.ID)=[Main].[Division]));

Any help would be appreciated

Thanks
die_laffin

 

[Jeff Boyce]

I couldn't tell from your description if the main table is using a lookup
field for Division. If so, expect difficulties (see the tablesdbdesign
newsgroup and mvps.org/access for concerns).

To trouble-shoot, consider (temporarily) modifying the combo box in your
form to display the key field rather than the text field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[die_laffin via AccessMonster.com]

Hi Jeff

Thanks for the reply.

I've changed the Division field in the Main table from text to a number field.

The combobox is currently displaying both the ID & ID fields.

I've temp added a textbox to check the current value of main.division and
whatever number that displays, the combobox displays that +1
eg
textbox = 5
combobox = 6

Most confusing

Is that any further help (I've managed to get all other parts of the db
working correctly after changing the way the Division is stored, just this
"little" problem to go!)

Thanks

die_laffin

I couldn't tell from your description if the main table is using a lookup
field for Division. If so, expect difficulties (see the tablesdbdesign
newsgroup and mvps.org/access for concerns).

To trouble-shoot, consider (temporarily) modifying the combo box in your
form to display the key field rather than the text field.

Hi

[quoted text clipped - 33 lines]

Thanks
die_laffin

 

[die_laffin via AccessMonster.com]

I really should learn to type!

The combobox is currently displaying both the ID & ID fields.

Should be:
The combobox is currently displaying both the ID & Text fields.


No - I'm not using lookup field

Thanks again

die_laffin

 

[Amy Blankenship]

Did you a any code to the combobox to make it set the Division ID, or did
you just use what the wizard made?

 

[die_laffin via AccessMonster.com]

When I got the db, the combobox was set to the value stored in the Main Table.


I've changed the combobox on the data-entry form so that it stores the
Division ID in the Main table instead of the Division Name. This is ok &
works.

Problem is when trying to get the correct Division Name from the Division ID.
For some reason the combobox displays the NEXT Division.

Division should be 1, but it displays Division 2 etc.

Any thoughts

Thanks

die_laffin

Did you a any code to the combobox to make it set the Division ID, or did
you just use what the wizard made?

I really should learn to type!

[quoted text clipped - 11 lines]

die_laffin

 

[Jeff Boyce]

I am baffled. I am not familiar with a mechanism that would tell Access to
disregard the item selected and go to the "next" item. It seems, though,
that Access would have to have some way of knowing what the "next" item in
sequence should be...

Good luck!

Jeff Boyce

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

When I got the db, the combobox was set to the value stored in the Main Table.


I've changed the combobox on the data-entry form so that it stores the
Division ID in the Main table instead of the Division Name. This is ok &
works.

Problem is when trying to get the correct Division Name from the Division ID.
For some reason the combobox displays the NEXT Division.

Division should be 1, but it displays Division 2 etc.

Any thoughts

Thanks

die_laffin

Did you a any code to the combobox to make it set the Division ID, or did
you just use what the wizard made?

I really should learn to type!

[quoted text clipped - 11 lines]

die_laffin

 

[die_laffin via AccessMonster.com]

Hi Jeff

Its a new day & I've looked at this again (with a fresh pair of eyes and a
slightly hungover brain).

The problem was the Bound Column. It was set at 0. Changed that to 2 & it
works correctly now.

Thanks for your time & interest!

die_laffin

I am baffled. I am not familiar with a mechanism that would tell Access to
disregard the item selected and go to the "next" item. It seems, though,
that Access would have to have some way of knowing what the "next" item in
sequence should be...

Good luck!

Jeff Boyce

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

When I got the db, the combobox was set to the value stored in the Main Table.

[quoted text clipped - 21 lines]