If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subform shows wrong record
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! |
#2
|
|||
|
|||
Subform shows wrong record
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" wrote in message ... 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! |
#3
|
|||
|
|||
Subform shows wrong record
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" wrote: 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" wrote in message ... 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! |
#4
|
|||
|
|||
Subform shows wrong record
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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#5
|
|||
|
|||
Subform shows wrong record
"Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#6
|
|||
|
|||
Subform shows wrong record
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 :-( "Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#7
|
|||
|
|||
Subform shows wrong record
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" wrote in message ... 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 :-( "Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#8
|
|||
|
|||
Subform shows wrong record
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. "Larry Linson" wrote: 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" wrote in message ... 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 :-( "Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#9
|
|||
|
|||
Subform shows wrong record
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" wrote in message ... 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 :-( "Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
#10
|
|||
|
|||
Subform shows wrong record
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 "Jeanette Cunningham" wrote: 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" wrote in message ... 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 :-( "Jeanette Cunningham" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|