Combo box issue (1 Viewer)

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
Hello,

I have a form that is used to populate JROTC Cadet data. This form uses combo boxes to select a predetermined value. I have 5 different combo boxes and all of them are working fine except for the last one.

This combo box allows selection of the number of years a cadet has been in the program...1st, 2nd, 3rd or 4th. The ID for each of these records happens to correspond to the numerical value...i.e. 1 = 1st, 2 = 2nd and so on.

The issue is I am getting the ID number instead of the desired 1st, 2nd, 3rd or 4th text entry.

Here is the current set up from the property sheet:

Selection type: Combo Box
cboYearInProgram

Data Tab

Control Source: Year in Corps(this is the table name for where the data is that I want)

Row Source: SELECT [Year in Corps].[ID], [Year in Corps].[Year In Corps] FROM [Year in Corps];

Row Source Type: Table/Query

Bound Column: 1

Format Tab:

Column count: 2
Column widths: 1",2" (have changed this to reflect 0",2" with no change)

So needless to say I am stumped...

For comparison here is another setup that is working perfectly on the same form...

cboPositionInCorps

Data Tab:

Selection type: Combo Box
cboPositionInCorps

Control Source: Position in Corps

Row Source: SELECT Position_In_Corps.Position FROM Position_In_Corps;
Row Source Type: Table/Query
Bound Column: 1

Format Tab:

Column count: 2
Column widths: 1",2"

This one works great...:(
 

isladogs

MVP / VIP
Local time
Today, 06:02
Joined
Jan 14, 2017
Messages
18,186
Do you have a lookup field at table level?
(effectively, a combo box in the table field)
If so, that's your problem
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
No I do not...the table is just 2 columns and only 4 records

ID column(auto generated)
1
2
3
4

Year in Program
1st
2nd
3rd
4th
 

isladogs

MVP / VIP
Local time
Today, 06:02
Joined
Jan 14, 2017
Messages
18,186
In your first post, you have a field with the same name as your table.
That will cause problems.
Change one or other.
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
Colin,

I have tried renaming the field and get the "Reserved word caution" I proceeded and then got the Expression Error (). So I added parentheses around the table name that contains the data and has spaces in the name.

still the same issue. I know it is small since ID #1 = 1st and so on but I can't give up just yet!!!
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
UPDATE:

Row Source...SELECT Year_In_Corps.Year FROM Year_In_Corps;

Now I am getting this error:

Invalid use of '.', '!', or '()'. in query expression 'Year_In_Corps'

The only thing I can think of is the title of the table which now reads.."Year_In_Corps"
 

isladogs

MVP / VIP
Local time
Today, 06:02
Joined
Jan 14, 2017
Messages
18,186
Colin,

I have tried renaming the field and get the "Reserved word caution" I proceeded and then got the Expression Error (). So I added parentheses around the table name that contains the data and has spaces in the name.

still the same issue. I know it is small since ID #1 = 1st and so on but I can't give up just yet!!!

You presumably tried to use Year as your field name which is reserved as it is a function name. Try CorpsYear or something meaningful.

Do not use special characters like () in table or field names. Although it is allowed, it will cause you more grief later. Recommend letters and underscores only - no spaces.

Also the control source of the combos MUST be field names and NOT table names. Another reason why names need to be different
Once you have fixed your naming, the problem should also be fixed

EDIT
Didn't see your last post earlier.
Try table name YearCorps and field name CorpsYear. Not ideal but much better than you have now.
If it still doesn't work, there's something else going on
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 22:02
Joined
Mar 9, 2014
Messages
5,424
cboPositionInCorps RowSource pulls only 1 field so why set ColumnCount to 2 and multiple ColumnWidths? It will work but why show blank column?
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
Colin,

THANKS FOR THE ASSIST!

I will rename the table and fields to remove the spaces...

Afterwards, I can just delete the Row Source code and redo right, or should I delete that combobox and start over after changing the names?
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
June7,
I originally had it set to 1 and had the same issue, so setting it to 2 was a shot in the dark. Once I get this stupid thing to work like I want it to, I will reset it back to 1 and see if it still works.

Thanks for the question, I hadn't thought it all the way through at that point. Your statement makes perfect sense...why do something that isn't needed, it may cause me problems later!
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
Changed the Table name to...YearCorps
Changed the Field name to...CorpsYear

I have the control source as...YearCorps and I have the "Invalid Control Source" error???

So here is the setup now

Control Source...YearCorps
Row Source...SELECT YearCorps.CorpsYear FROM YearCorps;
 

isladogs

MVP / VIP
Local time
Today, 06:02
Joined
Jan 14, 2017
Messages
18,186
NO! The control source must be the field name which is CorpsYear

Also it had 2 columns before - so the row source should now be
Code:
SELECT YearCorps.ID, YearCorps.CorpsYear FROM YearCorps;

That should have column width 0;2 or similar so the ID column is present but hidden

I hadn't even noticed the issue with extra column on the other combo :D
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:02
Joined
Feb 19, 2002
Messages
42,981
Column widths: 1",2" (have changed this to reflect 0",2" with no change)

Adjusting the Column Width is the solution. It needs to be 0 to hide the bound column. Check your RowSource again to be sure that the first column is the ID and the second column is the text value. The bound column would be 1. The column count would be 2.
 

Jonathan Suthard

Registered User.
Local time
Yesterday, 23:02
Joined
May 17, 2018
Messages
12
TO all, thank you very much...I have the error fixed now, but I still have the same issue, the table that the form is inputting data to still shows the ID column even after making sure column width for column 1 is set to 0.

Work in progress!!!
 

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
It will as that is the data you are storing, which is correct. If id 1 = John Smith , you store 1 Not John Smith it the table.

If John Smith changes his name you simply change it in the originating table and your data remains accurate.
 

June7

AWF VIP
Local time
Yesterday, 22:02
Joined
Mar 9, 2014
Messages
5,424
When you want to view the related descriptive text in report, build query that joins tables.
 

Users who are viewing this thread

Top Bottom