Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

MS Access Help

Hello,

I am trying to create a DB for a middle school where they track student breakfast participation.
So far the data collection has been easy. I created a form where the only required input is the student number field (It is bar code scanned in). The form auto inserts the date field. The student name is Null (and proving to be my head ache). I wrote a LostFocus event that creates a new record and goes to the Student Numer field when each bar code is scanned.
The problem I am having is the student name must be manually looked up and entered after the fact. The first time is no problem (I mean it is only a couple hundred students). I don't have time to do it every day for the rest of the school year. How do I get the DB to match duplicate Student Numbers to Student Names manually entered in past records and automatically enter the name in the new record?
Thanks for your help,
0
Mal
Asked:
Mal
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
if you have already  a table with the student number and names, all you need to do is to look up the name using the after update event of the control for the student number i.e.

if studentNumber is Number Data type use
me.txtName=dlookup("Name","NameOfTable", "studentNumber=" & me.txtStudentNumber)

if studentNumber is Text Data type use
me.txtName=dlookup("Name","NameOfTable", "studentNumber='" & me.txtStudentNumber & "'")
0
 
als315Commented:
You should not store student name for each record. You should have separate table with unique sudent ID (Number), Name and other parameters. Store only scanned number and date. In query you can get data from other tables.
0
 
Helen FeddemaCommented:
If you have a table of student names and IDs (I hope you do!), there is no need to store the student name -- you can always retrieve it from the link on ID.  The Students table should be linked one-to-many to the StudentLunches table on the ID field.  On the form, you could have a combo box bound to the Students table, displaying both ID and Name, and after the selection is made the name could be displayed in another (locked) control.  That control would have code referencing the 2nd column of the combo box:
=[cboSelectContact].[column](1)

Open in new window


(Column numbers are zero-based on code.)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MalAuthor Commented:
if you have already  a table with the student number and names, all you need to do is to look up the name using the after update event of the control for the student number i.e.

if studentNumber is Number Data type use
me.txtName=dlookup("Name","NameOfTable", "studentNumber=" & me.txtStudentNumber)

if studentNumber is Text Data type use
me.txtName=dlookup("Name","NameOfTable", "studentNumber='" & me.txtStudentNumber & "'")
Right now I just have the one table that is created/updated as each form (record) is filled/submitted.
I tried creating a second table with the student names and numbers to do a DLookup but kept getting an error. I had considered an IF/Then code but was unsure how to write it. I will create a second table, try this code, and let you know how it works.

You should not store student name for each record. You should have separate table with unique sudent ID (Number), Name and other parameters. Store only scanned number and date. In query you can get data from other tables.
The idea of this DB is to record each use of the school lunch system (when and by whom) for the district bean counters. The dean also wants to be able to pull a daily report with all information without having to run a seperate query. So basically the flow is student use, use recorded, and report shows all info for each student who participated that day (number, name, date).

If you have a table of student names and IDs (I hope you do!), there is no need to store the student name -- you can always retrieve it from the link on ID.  The Students table should be linked one-to-many to the StudentLunches table on the ID field.  On the form, you could have a combo box bound to the Students table, displaying both ID and Name, and after the selection is made the name could be displayed in another (locked) control.  That control would have code referencing the 2nd column of the combo box:
=[cboSelectContact].[column](1)
I am not running the DB just writing the code. The dean requests it be as automated as possible so the only input being provided is a scan of the student bar code for their number. Once I manually attach a name to that number, the DB should do it automatically for all future uses (unless it is a brand new student). The dean does not want any other type of input or selection from combo boxes or lists as I am told it is too time consuming keeping students from their class.

Thanks everyone for their help. I was trying to do this all with one table, but as i researched it on my own it was looking more and more like I would have to create a second. I was unsue about the code and I am still a bit hazy about linking and relationships for the tables but I will increase my focus in those areas. I will let you know how it turns out.

Mal
0
 
als315Commented:
Look at sample
DBLunch.accdb
0
 
PatHartmanCommented:
@als315
I would suggest some changes to tblLunch
1. The default for date should be Date() rather than Now().  You only want one per day and using time allows multiples.
2. tblLunch needs a primary key.  I choose a compound key of  LunchDate and StudentID.  See #1
3. The default for StudentID must be null.  You never want a "0" default for a foreign key.
3. The PK for tblStudent should be named StudentID to avoid confusion.

I added a query and a report to show Mal how to get the name for a student without storing it in multiple places.
DBLunchUpdated.accdb
0
 
als315Commented:
@Pat
This sample is only starting direction and the only aim was to show how to use lookup fields.
There was used Now, because in this case you can make report with average hourly quantity of students.
And I absolutely agree with your other comments.
0
 
MalAuthor Commented:
Ok I attached a sample of what I am trying to do. It is a simple form that on first use the student name is recorded manually. However, on subsequent uses the DB should record names automatically. I used Rey Obrero's DLookup code above. The good news is I am no longer getting errors in my form/code. The bad news is it does not record the name. Not sure what's wrong.

Mal

PS  Thanks again for all the help.
Robison-2nd-Breakfast.accdb
0
 
Rey Obrero (Capricorn1)Commented:
here try this, click the button Add Student first before adding a record
Robison-2nd-Breakfast.accdb
0
 
PatHartmanCommented:
I normalized the database so that you do not need to store the names in multiple tables.  I realize that names don't change often but they do change.  What would you do if a child's mother remarried and the new husband adopted the children?  Not only would you need to change the student record but you would also need to change all breakfast records.  That is why we only store data one time.  Also, since this is a 1- many relationship, I made the form a main form with a subform.
Robison-2nd-BreakfastUpdated.accdb
0
 
als315Commented:
Solution was given
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now