linked subform question

[jeremycod]

Hi,

I'm newby in MS Access and VB.
I have created two simple tables (as an example of the problem), student and mark. I created two forms corresponding to the tables, form1 and form2, respectively. I have connected those forms with Open form button in form1, which opens form2 for the selected student. It's works fine, but I want to add new mark for selected student, but studentId (foreign key) is always set to 0. I can do it manually, but I want hide this field from end user, and set this to works automatically.

Here is an example

 

[ajetrumpet]

You have a one to many relationship using the autonumber as both the PK and the FK. Probably shouldn't do this. You certainly won't get a relationship out of it. Try setting up a combo on your form with a record source of the student ID field. You can then pick from that list on your "mark" form, but still be able to input all the other data into the "mark" table that belongs there. Regardless, do not use two autonumbers in a one to many. As of right now, you cannot get the student ID to populate automatically becuse there is no data from the "marks" table that is related to the data in the "student" table. You could populate the first and last names on the "marks" form, or the ID number of student if you entered one or other in a control of somekind, like a combo box.

Check out this link....

http://support.microsoft.com/kb/319482/en-us

 

[jeremycod]

Try setting up a combo on your form with a record source of the student ID field. You can then pick from that list on your "mark" form, but still be able to input all the other data into the "mark" table that belongs there.

This could work, but what I want is when choosing a students' record and click button, another form with his marks should be opened. And if I put some new records there that marks should be added for that student, without the need to add his ID, name or something similar again. This ID should be added automatically.

Regardless, do not use two autonumbers in a one to many.

I'm a little confused with your answer. Did you mean that PK and FK are both autonumbers. If so, they are not. In student table PK is ID (autonumber) and in mark table studentID (Long Integer) is FK. These two are connected, and mark.ID (autonumber) is PK in this table.

As of right now, you cannot get the student ID to populate automatically becuse there is no data from the "marks" table that is related to the data in the "student" table. You could populate the first and last names on the "marks" form, or the ID number of student if you entered one or other in a control of somekind, like a combo box.

As I stated above those two tables are connected, and I do not see any reason why marks table could not be automatically populated with studentID. This works fine if I use nested forms instead of linked forms, but in my case I can not use that because I have more than two forms that should be connected in this way, so it could be robust if I nest them all.

 

[ajetrumpet]

Jeremy,

I think you're going to have to choose another data type or a combination of fields, like First Name/Last Name for a PK in the student table. What you're asking is to open Form 2 and enter new marks based on the student ID that is in the active record of Form 1, correct??

I don't think you can do this, because you would have to reference another form object, and I don't believe Access allows that. It only makes sense though, because a control value on another form is not a set value.

 

[ansentry]

jeremycod,

Have a look at my attached sample, I think it will do what you want.

 

[jeremycod]

I think that this will help me. I have to take some work to resolve ansentry's code, because I don't know anything about VB, but I think I will make it.

Thank you everybody for your help.