Solved

MS Access Help

Posted on 2016-10-08
11
70 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 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 

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 36

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 36

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 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