Not Null calculation not working as expected

[TMullins]

fields like Class_Hours, Oil_Hours, Heavy_Hours should be changed to an HourValue & HourType field.

Could be normalized like CourseName , CourseHours

CDL, 80

Heavy, 160

Oil, 40

Where these would all be separate records, and I could make the CourseName a separate table. My question here is, how could I change my form so that the end user would be able to easily see how many hours this student currently has in any of these areas? I've attached a picture of my current form.

 

[isladogs]

Replace the 3 textboxes with a 3 column listbox StudentID, CourseName, CourseHours with StudentID as the bound column.
Set the width of the first column to 0 so its hidden

 

[TMullins]

Replace the 3 textboxes with a 3 column listbox StudentID, CourseName, CourseHours with StudentID as the bound column.
Set the width of the first column to 0 so its hidden

I've tried this, ( at least I think so... maybe went overboard a tad ?)
Once again, I've attached pictures. It seems as though it does show CDL hours and Oil hours, but it shows the same hours for every student and the only way I can edit these numbers is directly in the table it's self. Obviously, I'm doing something wrong, but I think I'm going down the right path ??


I have not removed the "offending", or otherwise, "unnormalized" fields from the students table in hopes of salvaging this data once everything is properly normalized.

 

[isladogs]

I've tried this, ( at least I think so... maybe went overboard a tad ?)
Once again, I've attached pictures. It seems as though it does show CDL hours and Oil hours, but it shows the same hours for every student and the only way I can edit these numbers is directly in the table it's self. Obviously, I'm doing something wrong, but I think I'm going down the right path ??


I have not removed the "offending", or otherwise, "unnormalized" fields from the students table in hopes of salvaging this data once everything is properly normalized.

You are selecting the whole table so it is just showing the first record.
You need to filter the row source for the listbox for the student id used in the current record of the form.
For clarity, suggest you rename your SystemId field as StudentID and rename the two ID fields prefixing with the table names

 

[TMullins]

You are selecting the whole table so it is just showing the first record.
You need to filter the row source for the listbox for the student id used in the current record of the form.
For clarity, suggest you rename your SystemId field as StudentID and rename the two ID fields prefixing with the table names

At our school, we all ready have an established FAME ID and Student ID, which is why I came up with the term System_ID. For clarity, I changed the name ID in the students table to System_ID , which I do believe makes things easier to understand. I had an error when I made this change, so I renamed the Student table ID to Main_ID

My query SQL that I'm using is:
SELECT Student_Hours.System_ID, Student_Hours.CourseName, Student_Hours.CourseHours, Students.System_ID
FROM Students INNER JOIN Student_Hours ON Students.[System_ID] = Student_Hours.System_ID;

The above mentioned change, also changed my SQL to : SELECT Student_Hours.System_ID, Student_Hours.CourseName, Student_Hours.CourseHours, Students.Main_ID
FROM Students INNER JOIN Student_Hours ON Students.[Main_ID] = Student_Hours.System_ID;
When I add the list box, I choose the query, Course_Hours and follow the prompts for the columns as you suggested, but when I'm finished the textbox still says it's unbound.

 

[isladogs]

At our school, we all ready have an established FAME ID and Student ID, which is why I came up with the term System_ID. For clarity, I changed the name ID in the students table to System_ID , which I do believe makes things easier to understand.

My query SQL that I'm using is:
SELECT Student_Hours.System_ID, Student_Hours.CourseName, Student_Hours.CourseHours, Students.System_ID
FROM Students INNER JOIN Student_Hours ON Students.[System_ID] = Student_Hours.System_ID;

When I add the list box, I choose the query, Course_Hours and follow the prompts for the columns as you suggested, but when I'm finished the textbox still says it's unbound.

OK if you're happy with the naming that's fine by me

The row source gives you the list - does it now filter correctly with the added code?

Its an unbound listbox because the control source isn't set to anything
If you want to bind it, you would set the control source to a suitable field
But in this case I thought you wanted an unbound control.

 

[TMullins]

OK if you're happy with the naming that's fine by me

The row source gives you the list - does it now filter correctly with the added code?

Its an unbound listbox because the control source isn't set to anything
If you want to bind it, you would set the control source to a suitable field
But in this case I thought you wanted an unbound control.

I think that I'm misunderstanding when you said 'bind' -- I see where it's bound to column 1 in the properties box. When I add the list box, I get these two ( pictures included) questions that I don't know how to respond to that I think could be the source of my issues ?

 

[isladogs]

Sorry - I don't follow what you're saying
Are the screenshots identical?

I'll try & explain again ...
Apologies if you already know what follows
Textboxes, listboxes & combos can be unbound or bound to a field.
Listboxes & combos have control sources which determine what is shown

I rarely use the wizard but if you do so, you can choose to bind the control to a field (option 2) or as you've done choose option 1 to keep the selected output for later use elsewhere in your form (or code)

As I believe you just want to display the list but not do anything with it, you've chosen the correct option & if you want, you can also set the listbox locked so no selection can be made.

HTH rather than confuse you further

 

[TMullins]

Sorry - I don't follow what you're saying
Are the screenshots identical?

I'll try & explain again ...
Apologies if you already know what follows
Textboxes, listboxes & combos can be unbound or bound to a field.
Listboxes & combos have control sources which determine what is shown

I rarely use the wizard but if you do so, you can choose to bind the control to a field (option 2) or as you've done choose option 1 to keep the selected output for later use elsewhere in your form (or code)

As I believe you just want to display the list but not do anything with it, you've chosen the correct option & if you want, you can also set the listbox locked so no selection can be made.

HTH rather than confuse you further

Thank you very much for your additional explaination... I did this again without the wizard, and this is what I've come up with so far. I believe that my current problem is how to tie (( filter )) the course hours from student 23 to the course hours for only student 23. It's still showing the entire table instead of just the record for the student record that I'm currently working with.

I've also rewritten the rowsource as

SELECT Course_Hours.CourseName, Course_Hours.CourseHours FROM Course_Hours WHERE (([Main_ID]=[System_ID])) GROUP BY Course_Hours.System_ID, Course_Hours.CourseName, Course_Hours.CourseHours;

but that hasn't changed my results in a favorable way.

 

[isladogs]

I'm beginning to get a little confused by table & field names....

Your latest SQL is incorrect - in fact I'm surprised Access didn't object to it as the structure is wrong. You were nearer with the previous attempt

Your listbox row source should be similar to this:

SELECT Student_Hours.System_ID, Student_Hours.CourseName, Student_Hours.CourseHours
FROM Student_Hours
WHERE Student_Hours.System_ID = Forms![COLOR="Red"]YourFormName.StudentID[/COLOR]

Replace YourFormName with ... the name of your form
Replace StudentID with the name of the control for the student ID field

This will filter the Student_Hours table to the records where the System_ID is equal to the equivalent value on your form.
Make sense?

Now set the width of the first column to zero as you don't want to see that field.

BTW when I said I don't use the wizard, that didn't mean you shouldn't do so! If you find the wizard helpful - use it!