Subform shows wrong record

[BVM]

Hello there, I will try to explain this well even though I'm a little
confused...
-I have a form with a seach list and a subform
-The search list is linked the subform (Licenses) by the student ID which is
a unique key
-When I select names from my list I often get the correct license record
except when two record in the search list have the same last name. I've
noticed a pattern that the second record (i.e. data entered after the already
existing record) assumes the same license info as the prior record.

I don't know where to go from here. I also have a main table (member
personal data) which contains the ID number, names etc and I created a one 2
one relationship with the Student ID field in the license table. I can open
the license table and the correct information appears for every record when I
expand the subdatasheet. The problem seems to be in the form. Does any one
have any idea of what I should do? Hopefully it's something simple. I have
other tables linked to my main 'member personal data' table and I'm nervous
about messing it up. Help!

 

[Jeanette Cunningham]

Hi BVM,
This line-->
I've > noticed a pattern that the second record (i.e. data entered after the
already

existing record) assumes the same license info as the prior record.

-->
suggests that the same student can have more than one license record.
Does it make sense for your database that any student can have more than one
license record?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BVM]

Thanks Jeanette, but I'm sure that the students don't have more than one
license record. I've even checked the tables and it shows me the correct
record when I open from tables so I know the correct info is there. The
problem is when I open the form. Does that make sense? Maybe the search list
wants to have a more specific criteria to search by, but each student has a
unique ID so I don't know why it gets confused with some last names :-( Any
ideas?
Thanks.
BVM

 

[Jeanette Cunningham]

It's most likely that the problem is related to the way the tables are set
up.

Would you post the names of the tables, the primary key and foreign key for
the table and the relationships between the tables.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BVM]

It's most likely that the problem is related to the way the tables are set
up.

Would you post the names of the tables, the primary key and foreign key for
the table and the relationships between the tables.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BVM]

For some reason my last post only recorded your entry. Let's try again.
First of all, I don't know what a foreign key is, but I'll tell you what I
have set up.
Main table: Member personal data (primary key = student id)

Linked table: Drivers license (one to one relationship with full referential
integrity enforced). it is linked by the student ID number. In this table I
also have student ID as a primary key.

The form Licenses Record has a search box with the following fields from
Member personal data:
student id - hidden, but linked
last name, first name and middle name

This form has a subform which is based on the info in Drivers table.

Does that help? I'm not an expert at this as you see so I'm not sure what
other info I can give to help you :-(

 

[Larry Linson]

The form Licenses Record has a search box with the

following fields from Member personal data:
student id - hidden, but linked
last name, first name and middle name

What, pray tell, is a "search box"? Do you mean a Combo Box from which the
user can choose a student to search?

If the StudentID is hidden, but is actually what you are using to retrieve
the record, how would you know which "Smith, John Peter" you _ought_ to be
retrieving, if there are two or more "John Peter Smiths" in the table of
students?

A foreign key is a value in a table used to link to the primary key in
another table to retrieve information that is related.

If, in your case, you have tblStudent, with a Primary Key of StudentID, and
another tblActivityCard, which has a Primary Key of ActivityCardID, but has
a field called (not surprisingly) StudentID which will have the value of the
StudentID in tblStudent who owns the activity card, then the field StudentID
in tblActivityCard is a foreign key to tblStudent.

Questions for which we have good luck providing correct answers tend to be
precise, concise, and detailed.

Larry Linson
Microsoft Office Access MVP

 

[BVM]

Good points, Larry. You actually helped me ask myself the right questions to
figure it out.

What, pray tell, is a "search box"? List Box

Do you mean a Combo Box from which the user can choose a student to search?
Yes, Select student from list box


If the StudentID is hidden, but is actually what you are using to retrieve

the record, how would you know which "Smith, John Peter" you _ought_ to be
retrieving, if there are two or more "John Peter Smiths" in the table of
students?

The bound column is the student ID, but those who use the database prefer to
search by last name (we're a small organization). I reduced the size of that
column to 0 thereby hiding it, yet I sorted the list by last name. People
could then scroll down the list to find who they wanted.
Here's where I messed up some time ago:
- I wanted them to be able to type the first letter of the last name to jump
to that record. I had sorted the lists alphabetically but it was still a
search based on the student ID.
- Unknowingly when I sorted it by last name I changed the search criteria.
The list box then only searched for the drivers record based on last name and
not student ID as I had thought.

Cheers.

 

[Jeanette Cunningham]

Yes, access does allow you to relate tables one to one, however it is quite
a lot more difficult to build forms for this and not recommended for a
beginner.

There are 2 options for you-->
1. Put a student's drivers license details in the same table as the member
personal data. Choose this option if you are sure that you will only have
one license for each student.

2. Make a one to many relationship between the member personal data table
and the Drivers license table. This allows the possibility for each student
to have more than one license.
Here, the foreign key is the field studentID in the Drivers license table.


See if this helps you to get the form working. If not, post back with
additional questions.

 

[BVM]

Thank you Jeanette, we must have posted the last messages close to the same
time. I was able to resolve it because of some comments made by Larry Linson
(please see last 2 posts). Thank you anyway.
BVM