Tables correct (1 Viewer)

Hope · Feb 11, 2008

db

If you go to forms, click command button for Classes Taken, Hope appears and I want it to be blank so I can fill in and employee name and all the classes they have taken, then when you click on coarse name it says field cannot be updated

CraigDolphin · Feb 11, 2008

Here you go.

The problem was that the recordsource for your subform was drawing information from two tables at once without providing the key field information necessary to identify which record in the courses table the new record in the employeecourses table should be linked to.

To deal with this, I removed the course table from your recordsource entirely. The information you need to store is the courseID, not the course name. You make the courseid human-readable by showing it in a combo control that stores the courseid but shows the coursename (which it looks up in a separate rowsource query).

Now, I'm not sure how familiar you are with combo controls but in case your next question is to ask how you would add 'new' courses to the list, then I would recommend you search/read up on the not in list event of combo controls. There are many examples on this forum and via google that should point you in the right direction there. If you get stuck with that, feel free to ask back about it.

Hope · Feb 13, 2008

hello

Craig, I understand the combo box deal, thanks, I actually used that in the Add New Employee form now.

Required Classes by Title I won't be able to have a form to enter, that will have to be generated by a query to get the information. Everything should pull correctly on what classes are required by title through the tables, Correct. And somewhere in there I need to show how frequent the class needs to be taken, I thought would be here.

Also, After creating my forms my queries don't work/pull correctly. ALL confused, nothing new haha.

I need different forms like one for entering in the employees name and the classes they took.
One for, example if you are a Diesetter you need to take the following classes.
One for entering in a new employee
One for entering a new title
One for adding service providers and there classes

I believe I have all the forms I need created now working on the queries to work, still looking and trying.

Hope · Feb 18, 2008

getting there

Ok, I think I have all the forms setup that I will need and most of the queries to pull out my information, all but the required class query is stumping me. I need to be able to type in a employees name and have a list of required classes populate. If I get that query to work I should be able to get the courses not completed list then by using that required query. Any suggestions would be great.

Thanks

CraigDolphin · Feb 18, 2008

Hope, take a look at this. I've modified your form(s) so you can Enter Class taken information for each employee, as well as see whether each class that was taken by the employee was actually required or not. I've also set up a subform to show you all the required classes for the employees Jobtitle, which can be used to populate your required classes table, and also shows whether the employee has met the requirements (passed) for each class in the required list.

You hadn't populated anything in the required classes list so I added in a couple of required classes for the operator job title. The last couple of people in you employees table have that job title so you'll have to view one of them to see the list in action until you populate the required classes data for other job titles.

Hope · Feb 21, 2008

hello

Hello Craig, first I wanted to say thanks for helping and modifying my forms to help me out. Haven't worked on this in days because its end of month and I have other projects I have to get done first. Anyways, I looked at it today and it looks great now I have to investage whats going on because I found a couple of problems so far.

I opened Add new position and I entered in New just to see if it worked and then I was looking around and noticed it saved in the Coarse table under coarse name and that isn't right. Maybe I'm pulling the wrong fields in my forms or the wrong fields are in the tables.

Also, When using the add a coarse form, I added Tooling as a coarse and it saved it in the table, but isn't there when you reopen add a coarse form and some are doublicating, for example, Hands on.

Have to do some playing around and see whats going on.

CraigDolphin · Feb 21, 2008

Hope,

here's another copy of the db....I hadn't looked at your other forms before. I was unable to replicate the issue you described when cretaing a new position and having it saved into the courses table.

The courses 'doubling' issue was caused by using a poorly designed query as the recordsource. I've re-bound the form to the course table and also fixed one of your relationship/table design errors to do with frequency so that it doesn't create more headaches.

I've also gone through and tidied up some of your other forms so that they work better.

Hope · Feb 22, 2008

thanks

Thank you Craig for all your help.

Hope · Mar 4, 2008

hello

Question: Looking at the main form, then going to Classes Taken is there a way so that page opens empty? I already have the form set to Data Entry-Yes but it is not working, I also looked at the subforms to make them data entry also but no good yet? I didn't want to use the scroll buttons to get to the last record or search the scroll buttons to find the employee I want to add a new course too. I hope this is making since? I guess maybe my solution is to use a drop down box on employee name and have that pull the information?

CraigDolphin · Mar 4, 2008

Look in the code under the command button on your main form. The code there specifies that the form opens in EDIT mode which overides the dataentry property of the form even though you may have specified that the form is in data entry mode.

ie...

Code:

DoCmd.OpenForm "fmEmployeeTraining", , , ,[B] acFormEdit[/B]

to get it to open in data entry mode you could change the code in the button to...

Code:

DoCmd.OpenForm "fmEmployeeTraining", , , , acFormAdd

Hope · Mar 4, 2008

thanks

That was it, thank you again for your help

CraigDolphin · Mar 4, 2008

No prob

Hope · Mar 4, 2008

question

I've been playing around with my query for required classes and in there you have Met Requirement: and the query says -1 or 0, is there a way to have T for taken and R for Required or something like that instead of -1 and 0. I have been playing with that but no luck so far.

CraigDolphin · Mar 4, 2008

Well, the purpose of that field is to be the source of information for the checkbox on the form. Checkboxes interpret 0 as false and anything else as true. So, if you want to change the values to either T or R, you will also need to change the checkbox on the form to a textbox.

The field expression would be

Met_Requirement: IIf(Not IsNull(DLookUp("EmployeeID","[tbl_EmployeeCourses]","[EmployeeID]=" & [tbl_Employee]![EmployeeID] & " AND [CourseID]=" & [tbl_CourseRequired]![CourseID] & " AND [Passed]=-1")),"T","R")

Hope · Mar 4, 2008

Great, thanks

Hope · Mar 7, 2008

hello

Craig, I was looking at your queries on your Db, and for required classes for Scrooge McDuck he should take Intro Writing, Intro Reading, and Basic Logic. He did take Intro Writing and Reading, so your query CoursesnotCompleted should say Basic Logic and it doesn't. Anyways I noticed this because I can't get my qry for courselistnotcompleted to work correctly, and I believe its because I am using my other quieres to pull this informaton and I have criterias in the queries so its asking for that information. I have a copy of the db posted so far. Any ideas please let me know.
Thanks

CraigDolphin · Mar 7, 2008

Hi Hope. Hmm. Looks like I forgot the other join in that example db to make that query do as it's supposed to.

Never trust A McDuck!

In any case, your db is attached and the query should be working as intended.

Hope · Mar 17, 2008

hi

Thanks again for your help

Tables correct (1 Viewer)

Registered User.

GrumpyOldMan in Training

Attachments

Registered User.

Registered User.

GrumpyOldMan in Training

Attachments

Registered User.

GrumpyOldMan in Training

Attachments

Registered User.

Registered User.

GrumpyOldMan in Training

Registered User.

GrumpyOldMan in Training

Registered User.

GrumpyOldMan in Training

Registered User.

Registered User.

GrumpyOldMan in Training

Attachments

Registered User.

Similar threads

Users who are viewing this thread