Problem with row source. Pulling column 0 rather than 1! 1

[Nate1749]

The three tables I have are tblMain -> tblMonth -> tblYear.

tblMonth has a lookup field for the Year and it doesn't show the first column (column 0) which is the id, only shows the year #. So if I open tblMonth I see January with a field next to it being a drop down for the year.

in tblMain I am pulling both Month and Year fields from tblMonth. Rather than getting January, then a pull down of the year, I'm getting a pull down of the ID#.

This is the row source code...


SELECT [tblReviewMonth].[MonthID], [tblReviewMonth].[ReviewMonth], [tblReviewMonth].[ReviewYear] FROM tblReviewMonth;


Oddly enough, when I go to the query builder for that line it shows it just as I want it (except month id), but in the drop down on the actual table & form, it is showing the id field for the year. I increased it show 4 columns on the form and make sure each one had 1 inch and it is only showing monthid, monthname, yearid, and then a blank space. I know this can't be hard to fix, but I can't figure it out.

-Nate

 

[BobJacksonNCI]

Nate,

It is very hard to determine what the question is...

If you're working on a combo box in a form, the syntax to refer to a column is:

Forms!FormName!ControlName.Column(1)

The digit represents the column number you want to work with and it starts with 0 being the first column.

If that's not what you're looking for, please post again. Be as specific as you can about what you want to happend.

Bob

My suggestions come with an UNCONDITIONAL 30/30 Guarantee!
(30 seconds/30 feet - whichever is first.)

Please view Thread181-473997 for helpful hints regarding this site.

 

[willir]

Nate1749

I think your problem is in the configuration of the combo box. In the form design mode, look at the properties for the combo box, specifically, the "format" tab.

Check

"Column count"
- the number should match the number of fields being retrieved. In your case, 3 -- MonthID, ReviewMonth and ReviewYear.

"Column width"...
Look for zero width columns. A zero width column will hide it.
0";1";0.7"
Will hide the MonthID column. This will hide the column both in the drop down view when selecting a record and in the displayed record.

"List width" -- no biggie, just controls how wide the drop down list is. For the aforementioned example, 1.7", or auto.

Also, check your SQL query to make sure the columns match to what you expect, and that the correct field is being bound or referenced.


Still not working??

Look at the table in design mode. Perchance , the "LookUp" setup for the field is causing your frustration.

for example, the lookup combo box in the table design view could be set to...

SELECT [tblReviewMonth].[ReviewMonth], [tblReviewMonth].[ReviewYear], [tblReviewMonth].[MonthID] FROM tblReviewMonth

and the bound column is set to 3 (for the MonthID).

Hope this "shotgun" approach helped.
Richard

 

[Nate1749]

tblYear
--------------
YearID
Year

tblMonth
--------------
MonthID
Month
ReviewYear ------> Is a lookup for field Year in tblYear; YearID is pulled with it, but is hidden


Now I have 12 rows in tblMonth, in which I have to type in the Month in field name Month, and use the drop down to select the year (list goes from july 03 - june 04).



tblMain
---------------------
ID
ReviewMonthYear ----> Is a lookup to tblMonth. From tblMonth I select it to pull the following MonthID (hidden), Month, & ReviewYear.

Right when I'm making the lookup I only see ReviewYearID, not the actual field ReviewYear. This is the problem I'm having. If this still doesn't make sense let me know.

-Nate

 

[willir]

Nate

I now understand. You are pulling your information from two tables. There are two ways to resolve the issue.

The simplest is to change the query used for the combo box. Add the "Year" field. If you inset the Year field infront of the YearID field. If the tblYear table is not included in the query, just add it by right-click - add table.

Another way is at the table design, for the LookUp for tblMonth, field ReviewYear. Change the Display from Text box to Combo box. Setup your Row source to look at tblYear. And here is the tirck... For the query, place Year in the 1st column and YearID in the second column. BUT make sure the bound column is number 2, and the column count is 2. This way, you will the Year value but Access will be using the YearID.

ONE IMPORTANT Commnet...
Year is a reserved word. I strongly recommend that you change the field "year" in tblYear to something else. Any references to the field "year" in your current application will also have to be changed.

Richard

 

[Nate1749]

Thanks Richard, works now.

Additionally, thanks for the heads up on the reservered word, I didn't know Year was one of them.

-Nate