Add Record Problem

[Guest_imported]

I have 2 tables namely SFCLOG and HOUSINGINFO. The SFCLOG table contains a field called "ID" which defines every student in the database. The 2nd table HOUSINGINFO contains housing information for the students in the first table. The foreign key in this second table is called "STUDENTID" which is the same as "ID" from the first table.
I have a form which lists students entered into the system along with their ID's. When the user double clicks on their ID's another form opens up with the fields which pertain to the second table so that they can enter the housing info for that particular student. Since the "ID" and the "STUDENTID" fields are the same, when they double click and open the 2nd form the "ID" is already populated in the "STUDENTID" field.
But when the housing info is filled in, it adds all the fields to the 2nd table except the "STUDENTID" field, which I carry over from the first form. (=[Forms]![FrmHousingInfo]![ID])

How can I add the "STUDENTID" field into the 2nd table along with all the other info?
And btw, it adds a "0" for the "STUDENTID", instead of the "ID" passed.


Thanks for your suggestions!


Here is the code I use for the add function:
--------------------------------------------
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

 

[BlackKnight]

Hi,

Ideally you don't need to open another form (which clutters the desktop,). You can move normally through the student records and their corresponding housing info if any will appear in the subform.

This allows you to add a new student to the student table and then add the student's housing information into the housing table. It also allows this information to be presented on one form for easier viewing.

You can link the main form to your subform by StudentID. I assume StudentID is an autonumber field? If not it should be. <g> It has to be a long integer in your HousingInfo table. A field StudentNumber can be a text field to hold the student's school (student) id#.

Your main form will still be based upon the table SFCLog and your suibform will be based on the table HousingInfo.

Have a good one!
BK

 

[khood]

kash18:

Is &quot;STUDENTID&quot; the name of the field on the second form? Right click on the control & check just to be sure. Also, you may want to set the value of the STUDENTID field in load event of the 2nd form & remove the &quot;(=[Forms]![FrmHousingInfo]![ID])&quot; from the control itself:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'** I am assuming that your 2nd form is named FrmHousingInfo
'** and that your original form is still open
Me!StudentID = Forms!frmHousingInfo!ID

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load

End Sub

You can also assign the value from the first form in the event sub that opens the second form:
'**insert this AFTER the open form statement & don't
'** forget to change &quot;frmFORM1&quot; to your 1st form name
Forms!frmHousingInfo!STUDENTID = Forms!frmFORM1!ID

Hope this helps,

Ken Hood