Form to Update instead of Add New

[Missus_Nat]

Hi,

I am building a database which tracks students information and training records. We do not keep data on previously held (now expired) courses, but simply update the date, grade, and certificate number as new exams come in.

I am trying to build a form in which adding a 'new' record doesn't actually add new, but instead pulls up the current data as I fill in certain fields (Student ID and Course), so I can update the data without having to search for the Student ID.
Basically, when I have a stack of 100+ exams to enter, I don't want to have to manually search for each student in order to update their training records.

How can I do this?? I have never built a database before, and don't even know what the technical term for this would be!

Thank-you for any help!!


Also, once I've figured this part out, will I still be able to use the key command to copy the data from the previously entered record (Ctrl+')??

Thanks!

 

[Sketchin]

If you already had a form created, you could add a combo box for StudentID. When you start typing in the student ID, it will automatically find the one you want and bring up their information.

It sounds like you still need to set up your tables and forms first though?

 

[Missus_Nat]

Thanks for responding so quickly!!

I do already have my tables set up, and am trying to build the form(s) but got stuck with this...

The Student ID and Course fields are already Combo Boxes... but right now it just pulls up those individual fields when I start typing, not the whole record. I would need it to pull in the Student ID first, and then when I start typing in the Course populate the rest of the fields accordingly.

The courses have a table of their own, as all students are required to take the same 16 courses. Then I have a table containing all the individual training records (so 16 per Student ID). Maybe I just need to change the source for the Course field in my form?? (if yes, how do I do this?? I tried in the property sheet, but couldn't get it to work how I want...)

Sorry if I'm not asking my questions right... I'm new to this!

 

[Sketchin]

If you select course ID, it should pull up all related records.

Something else you could do is have a textbox for course ID, and then in the header create a combo box that is a pulldown for courseID, so that when you select the courseID record, the form updates with the information related to course ID.

You will probably need a separate form for students that will exist as a subform on your main form. That way you can add multiple students to one course ID.

 

[Missus_Nat]

Sorry, I think I'm confused... but I really appreciate your help!!

I already have the following tables:

Courses - Primary Key is Course ID (autonumbered)
- just has each course name
Students - Primary Key is Student ID (student name)
- contains student information such as address, date of hire,
etc.
Training - Primary Key is Training Record (auotnumbered)
- contains all student exam records (16 per student); students take these exams on a rotating three year schedule, so we do not keep old records once the new exam has been written.

I also have a form set up already that displays all Training Records attached to a particular Student ID. This is used to create new students.




The form I'm trying to build is to enter the individual exam records; I used the form wizard, which automatically made the Student ID and Course fields Combo Boxes, and is all sourced from the Training table (which sources the Course names from the Courses table, and the Student IDs from the Students table).

Is there a setting somewhere that opens the form with blank fields instead of populating with all the data from the whole table automatically?? Right now I have to 'add a new record' in order to be able to type without simply editing whatever record I'm currently viewing.
I know there is a setting to not allow additional records to be created in the form (but don't know where). Is there a combination of settings I need to change to achieve what I want??

Sorry again...never done this before. :banghead:

 

[Missus_Nat]

Hi again,

I've been looking through the forums trying to find a solution for myself, and the more I read, the more it looks like I need to do something involving cascading combo boxes and writing some code, which I have NO idea how to do...but at least I'm learning some of the proper terms.

I am totally lost now, any help would be IMMENSELY appreciated.

I am attaching my db...hopefully someone can give me an easy way to make my Training Records form do what I want. *begging & pleading*

Thanks a bunch,
Nat
View attachment Training.zip

 

[Sketchin]

taking a look

 

[Sketchin]

There are a bunch of things I would change before you do anything:

1) Remove spaces from field names
2) If you have a field called CourseID that is an autonumber with a one to many relationship in another table, call the related field CourseID with a field type of number.
3) For clarification, preface table and query names with tblsomething and qrysomething. (thats being picky, but it helps as your database gets larger)
4) Have a lookup table for certificate types with 2 fields...certificateID (autonumber) and certificatedescription (text). that way, you only have those certificates existing in one table, should you ever need to add more. (similar to what you did with courses)
5) Do you need more info for instructors?

I'll attach the database with the changes I made. Let me know what you think.

 

[Missus_Nat]

1) Ok
2) if I do this can I put both CourseID and Course Description in, and then 'hide' the ID column?? Or will this mess things up? Most of the people who will be using this database are even less savvy than me, so the names should always be visible.
3) Ok.
4) *confused* won't this mix up the certificate numbers between courses? i.e. there are currently a bunch of different records that have the same certificate number, but they're each attached to a different student/course taken. Each new exam written is issued a new certificate number.
5) Nope. The Instructor field is just so we now who signed off on the exam; probably 90% are by the same person (my boss).

The reason I am building this database is to replace the one my company built in 2000. It hasn't changed much in that time. The function I'm trying to put in my form is non-existent in the old db; everything is entered through manually searching the student .

Thank-you Thank-you Thank-you Thank-you Thank-you

 

[Sketchin]

2) Just dont include the ID column in the form. Its used in the background by Access to identify which record you're on. You don't need to do anything with it...just leave it alone.
4) Maybe I'm not understanding what the certificates are. Would you have a table with certificate names and associated numbers with them? Such as WHMSCertificate 600?
Or are the certificates tied to course names?
Now I'm confused. Are they just certificate numbers? As in, Steve has achieved his 5th certificate?
5) Once the table structures are correct, then we can make some forms.

 

[Missus_Nat]

2) Okie dokie
4) I think I understand your original note now... but wouldn't it be redundant to 'name' each Certificate as it is already attached to a CourseID / CourseDescription in the Training table? Also, I feel like it would take a lot of work to change my existing data to fit that arrangement...

The certificate numbers are issued sequentially by course; ex. lets say I already have 100 students in the db who have taken Course 1, the next student to write this exam would get #101. The next time that same student takes Course 1, #101 would be replaced by the next available number. The same would be happening simultaneously for Course 2, Course 3, etc.
I merge the student name/course/certificate/date into a certificate template in Word for printing.
Does that make sense? Sorry I confused you!!

Thank-you again for all of this!! Sorry I don't have much knowledge on how this stuff works...

 

[Sketchin]

Aww, ok, I was thinking certificate type, not simply certificate number.

You will still need a one to many from student table to certificate table, as one student can have many certificate#'s.

 

[Sketchin]

Btw, if you could show me how to merge fields from access into word, I would greatly appreciate it.

 

[Missus_Nat]

I was thinking that maybe I don't need a table for the certificate numbers, as they only appear in one table already anyway (the Training table); and the Student table already has a one-to-many relationship with the Training table. Does that make sense??

Most of the work to merge from Access into Word is done in the building of your Word document. I can definitely walk you through!! Do you have specific questions, or do you just want a simple breakdown of the whole process?

 

[Sketchin]

Ya, that makes sense. As long as its only in one location and makes sense, then go for it!

A simple breakdown would be good. I have a database that creates reservations and want data from a reservation table to go into specific places on a contract that is created in word.

Do you use word merge? I have looked into that, but my plan isnt to email the contract, its just to import the data into it, and then save the document.

 

[Missus_Nat]

How did you add CourseID to the Training table?? I tried to add it, but it doesn't have any data, and now I 'can't delete this field'??

 

[Missus_Nat]

I use Word for merge documents a lot.

The easiest way to merge from Access is to make a query in your db that contains all the fields you want to end up in your Word doc. If you already have a query that has all of these fields great! If it has more fields than you want in your merge, don't worry about it, you don't have to include all fields from the query.

In Word, create your document, with formatting, etc.
In the 'Mailings' tab, click 'Select Recipients'. It should give you a small drop-down menu; click 'Use Existing List'.
This should open a window for you to select your data source; double-click on the database you want to merge from.
A box should appear listing all the queriees in your db; select teh query you want to use and click OK.
Now you should have some other buttons available in the Mailings tab.
Place your cursor where you want your first field to appear; click 'Insert Merge Field' in the tab.
This will give you a drop-down of all the fields in your query.
From here, you can place as many or as few fields from yuor query wherever you want them in your doc.

When everything is built to your satisfaction (you can 'Preview Results' if you like), save your merge document.
At the end of the Mailings tab, click 'Finish & Merge'. For your purposes, you want to choose 'Edit Individual Documents' from the drop-down. This should open a new doc with all your merged data.
From here you can save-as or print.

Also, Word will only merge the data according to how your query was last run and saved; i.e. if your query was last run to show only reservations in September, Word will only merge these records, and nothing from other months will show up.

I hope that helps! Let me know if I wasn't clear, or if this doesn't fit what you need.

 

[Sketchin]

I thought I just typed it in. I may have deleted some of your existing data to do it.

Hmm...thanks for the word merge tips. Its exactly what I need to do, except that I was hoping to automate it in VBA somehow.

I have a feeling it could be very difficult and might be easier to just do it in an Access report.

 

[Missus_Nat]

I'm not sure how all the VBA stuff works, but once you've built your foundation merge document, there might be a way to create a button in your database, or some sort of command structure to automate the merge... I really don't know if that's even possible though.

So I just tried to 'modify' my look-up field (the course description column) in my Training table, and it showed me both the CourseID and CourseDesc columns in the wizard (or whatever it's called), and it recommended I hide the Key column (CourseID), so I said yes. But if I try to 'unhide' fields CourseID isn't there..
Does this mean it is connected in the background, and I just can't see it?? Or is CourseID still not part of my table?

 

[Sketchin]

yes, all that means is that you can't see it. You should never really use autonumbers for anything other than placeholders in the background of your form. Some people try to use them as invoice numbers or something like that, which is a terrible terrible idea.