Losing data when I close a form

[davegri]

NCR_Employees - 12-24 -C-davegri-v01.zip

Have a look at this. Took the SQL string from the form recordsource and made a named query, and used the query as the recordsource instead.
Modified the query joins (both joins, only one shown) to include all records from NCR_Personnel. (Right click on the join line to get the join property popup).

[Pennguin]

Oh wow, this is great. This procedure certainly isn't in the "dummies" book that I've been reading.

Well since this is now new to me, can I go back to the table view and change a parameter? (One of the combo drop down boxes was programmed wrong. )

I cant seem to modify or add/delete fields within the personnel table whatsoever

And then how did you create this query?

[davegri]

How To Make an Access Query from SQL text - Bing video
will show how. Use the query recordsource for the sql text and after the query is finalized and saved with a name, use that named query as the recordsource instead of the original text.
The video doesn't include the link join modification.

Yes, you can make changes to the table design, but if you add or delete fields, you might need to change the query to reflect that. The original query text would also have to be modified if you were still using it.

I cant seem to modify or add/delete fields within the personnel table whatsoever

Using the form works fine for me to add records.

[Pennguin]

The employment status drop down wasnt functioning correctly. I had to go back to the table and correct the Resource Type to Field list, it was looking for a table or query to pull the data from. Once I corrected that, I tested it in the qry field you created and it worked, then I went to my NCR_Personnel form and it didnt work. So I went into design view of the form. Deleted out that entry, and pulled the corrected entry field from the table once again. And now it seems to work. So that part was fixed.

When I mentioned I couldnt modify the table, that was an error on my side, the database was in read-only at that point, and I couldnt modify it until the database was enabled. Its been a long week with this crazy problem.

I will be watching the video you sent. Thanks for all your help.

[davegri]

Glad to help. Good luck with the project. Thanks for the star.

[ssanfu]

There are several issues that I think you should fix before moving forward.

Field names should be only Letters and/or numbers (exception is the underscore).
Do not begin an object name with a number.



The main problem is the relationships and table designs are wrong. It looks like you are "Committing Spreadsheet" with regards to the table designs.
You have the tables linked in a 1 to 1 relationship and the PK fields of all four tables are Autonumbers. It is extremely rare to have tables in a 1 to 1 relationship.

The "States" table and the "NCR_Employment_Jobs" table are designed wrong (IMHO). The state names are DATA and shouldn't be field names. There should be 51 records, NOT 51 fields in the table. (including Washington DC )
Same goes for the "NCR_Employment_Jobs" table. Every "Job" is DATA. Because you have linked the tables by the EmployeeNumber (1 to 1 relationships) you cannot add more than one record per employee number. To add another time sheet, each time you will have to add each employee AGAIN in the "NCR_Personnel" table.

BTW, there are 4 states missing from the "States" table.



What if you wanted to add 1 or more new jobs? You would have to change the table design, then change any queries, then change forms and reports, not to mention having to modify the VBA code.


My $0.02 ........


Edit - should have attached the modified dB.
Note: I did not change the structure of the rest of the tables, but all tables should be reviewed.. Also, nothing in this post is a judgment. I did the same type of table designs when I started with Access. (at least 5 dBs )

[davegri]

My $0.02 ........

HTH
-----

Steve

Yes, I agree. When I was evaluating the downloaded DB I was troubled with the 1-to-1 table setup so I Added PKs and FKs to all the tables and re-arranged the relationship links. Problem then was the 3 table query which was the form recordsource became un-updatable. That could be fixed by setting up a main form with subforms, each sourced to one table. All that was a major change that I did not undertake. The OP was happy with the results of the simple query update provided so I let it go, supposing that if new problems were encountered he would post another request. Time will tell.

[mike60smart]

EDIT**** Sorry did not see there was a second page to this thread

Well I must say the relationships between all tables is wrong.

Tables are related as follows- Main Table has a Primary Key - Autonumber

Any Related table has it's own Primary Key together with a Foreign Key.

NCR Personnel is your Main table with a Primary Key of EmployeeNumber (Would suggest you change the name to read EmployeeID)

Your Related table NCR_Employment_Jobs should have a Primary Key named JobsID and a Foreign Key called EmployeeID

You would then link EmployeeID from NCR_Personnel with the NCR_Employment_Jobs table on the ForeignKey EmployeeID

The same apples to the other tables

[Pennguin]

I was sleeping when you sent this out, you are correct, I will be posting more questions to you should things become difficult again. Hey this is Access, its a given. Im reading over the the thread on the FK to PK issue now. Since our last discussion, I noticed the data within the NCR_Schedule_Availabilty and NCR_Employment_Jobs did not line up correctly with the NCR_Personnel data. In short what I was discovering was employee 1 was being aligned with employee 64 in the Schedule and employee 120 inside Jobs.

This database is still quite new in the development phase. I can still modify this to being more correct, as it should save on headaches in the future. Would modifying the Schedule and Jobs with FK back to the PK fix these issues?

[orange]

I agree totally with Steve's comments post #21.
The tutorials from RogersAccessLibrary identified in the Database Planning and Design link in my signature will lead you through a procedure to identify tables and relationships that you can use with any database. They're a good refresher and learning experience if you take some time to work through 1 or 2. There are many other links to articles that you may find helpful as you design and develop your database. Getting a data model to represent your tables and relationships, and then testing and vetting these with sample data and sample scenarios will get you a blueprint for the database design. Often more efficient than development by trial and error. Much easier to adjust a data model than a physical database.
Good luck with your project.

[davegri]

NCR_Employees-Pennguin-davegri-v03.zip

See attached above. DB greatly normalized with Many-to-Many relationship tables set up for jobs.
Added subforms in tabs to allow unlimited job entries.
I didn't alter 1-to-1 relationship for schedule availability as it seems to logically be OK that way.
Moved the Comments field from the jobs table to the employee table.



View of Form