Solved

MS Access Help

Posted on 2016-10-08
11
52 Views
Last Modified: 2016-11-05
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
Comment
Question by:Mal
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41835350
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
 
LVL 39

Expert Comment

by:als315
ID: 41835351
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41835362
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
 

Author Comment

by:Mal
ID: 41836263
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
 
LVL 39

Expert Comment

by:als315
ID: 41836373
Look at sample
DBLunch.accdb
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Expert Comment

by:PatHartman
ID: 41837415
@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
 
LVL 39

Expert Comment

by:als315
ID: 41837834
@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
 

Author Comment

by:Mal
ID: 41842950
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points (awarded by participants)
ID: 41843004
here try this, click the button Add Student first before adding a record
Robison-2nd-Breakfast.accdb
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points (awarded by participants)
ID: 41843767
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
 
LVL 39

Expert Comment

by:als315
ID: 41875253
Solution was given
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem Description:   Couple of months ago we upgraded the ADSL line at our branch office from Home to Business line. The purpose of transforming the service to have static public IP’s. We were in need for public IP’s to publish our web resour…
Getting hacked is no longer a matter or "if you get hacked" — the 2016 cyber threat landscape is now titled "when you get hacked." When it happens — will you be proactive, or reactive?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now