Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access Help

Posted on 2016-10-08
11
Medium Priority
?
90 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 40

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 1000 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 40

Accepted Solution

by:
PatHartman earned 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

963 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