Add new record to table with form

[sarah.s]

Hi guys, I am very new to access but have managed to work out most things myself. I am trying to create a form that allows me to add new records to a pre-existing table. What I want to do is choose the title of the assessment I would like to add via a drop-box then type the date completed etc into the other boxes. So for I have been able to add information that is typed into the associated text box, however, it doesn't add it as a new record, it just changes the information under a different assessment title.

E.g: I want to add an assessment called Calculations level - 2 but the completed and date last completed data changes under the ascribe assessment rather than adding a completely new record to the table.



I hope I have described myself clearly. Any help would be a huge help. Thanks

Assessment Title	Completed	Date Last Completed
Ascribe	1	28/02/2015

[ranman256]

Not clear.
ANY form connected to a table will allow additions.
if the form is attached to a query, note that SOME queries don't allow additions due to the joins.
if this is your case, then you must change the form query.

[sarah.s]

not attached to query. Any info typed will add but to existing record even though I've changed the assessment I want to add via the drop box. Its the drop box that's causing the problem but I don't want to type out the assessment every time I add a new one

[Homegrownandy]

Open the form in add mode. I'm sure ill be criticised for mentioning macros BUT here we go anyway.

If you don't have a menu form, make one. Make a button and make that button open your form that you are currently working on. Go into the on click event and change the forms mode to " ADD"

any questions let us know...

As a note. Not that it makes a huge difference. The assessment title should be stored in its own table with information ONLY relating to that. Look at some basic listbox tutorials to be able to pick the one you want and how to store that.

Rather than you entering the date last completed every time. That information should be provided via a query. You should record every date complete. If you wish to just view the last then have a query to show the latest one. Its still important to record all the dates.

so basically... you have a table for assessment details only. such as title/description or anything else. then have a table that you can add completed dates to. Each date would relate to an assessment via the ID/key.

[sarah.s]

How would I achieve this with 50+ staff members all completing numerous assessments. End goal is to be able to add any new assessments to the correct member of staff and run a query to find out what assessments (and for who) are out of date and needing to be completed again. I initially wanted a table per staff member with 2 forms you could fill out. One for adding any new assessments that staff member has completed (choose staff member via drop box, choose assessment via drop box) but is this possible as its choosing a table opposing to a field?

The other form would be to update the database (choose staff member via drop box, choose assessment via drop box, enter date assessment completed.)

We have hard copies of all assessments completed so I think the most recent should be sufficient. This database is just for back up and to alert us to upcoming assessments in need of completion.

[sarah.s]

Name: (dropbox) Joe Bloggs
Assessment: (dropbox) Calculations level 2
Date completed: 03/01/2017

When I'm clicking the 'add' button its changing the assessment and date completed information but to the same record. Its not creating a new record under the same name. I want to add multiple records under the same name, different assessments and different dates, so that the all the staff members assessments are listed. This seems like a simple thing to want to achieve but for the life of me I cant seem to work it out. I have tried different record operation buttons including add record, duplicate record and more but none of them seem to do the thing I want them to do! Argh!

Please help!

[Homegrownandy]

what you're doing now is selecting something to edit. you need somewhere to store the selected values key. If you look up junction tables and listboxes it would help you I think. But here's my attempt at explaining.

you will have a table which is used for storing the dates completed. In that table you have a relationship to the... member of staff or whoever it is, (Name is a reserved word, don't call it that.) these two tables are related via the primary key of the staff.

on your form you have a listbox which uses the staff list of names as its source. Then you set it to save the selection in a textbox on that form that is linked to the staff_ID field on your new table.

if you youtube some of what im saying it will explain it far better than me.

[sarah.s]

Okay thanks for your help.

I literally have no idea what you're talking about but I will give it a go.

Thanks again.

[Homegrownandy]

do you know what a primary key and a foreign key is? do you know what relationships are between tables?

I'm terrible at explaining especially when I don't know how much detail you will understand.

[andy49]

Sarah could you post your database in here?


Sent from my iPhone using Tapatalk