Solved

MS Access Help

Posted on 2016-10-08
11
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 40

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 40

Expert Comment

by:als315
ID: 41836373
Look at sample
DBLunch.accdb
0
 
LVL 37

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 40

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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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 37

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 40

Expert Comment

by:als315
ID: 41875253
Solution was given
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 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