Help with my database 1

[tiger3p]

Hope someone here could help me with my dilemma.

I'm creating a database which will have a section for Interviewer's info (name, phone, email, etc.) and another section for Interviewer (name, phone, email, answers to 12 questions, etc.).

I created two forms one for the interviewer and another for interviewee. Problem I'm coming across is that the record are not saving when you move from one form to another. Either the interviewer's data gets save and the Interviewer data doesn't and vice versa.

What I would like to have happen is that the Interviwer's data is save and under it the Interviewee's responses. The current relationship I have is on table A the a drop down field called InterviewrID (this has 5 initials) to table B is one to many. Hope I'm making sense.

Your help would be greatly appreciated. Thanks in advance!

 

[KenReay]

Hi

Are you using a main form / sub form contruct to collect your data?, if yes, have you set the parent/child link fields properties correctly ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Rolliee]

Use a combo box filled with the needed fields from the Interviewer table (which is probably rather stable - few changes/ additions). Picking this combobox, use the click event to put the values from this table into the Interviewee's table. You have access to all the fields in the combo box using me.combo.column(?) It is a neat trick (read technique).

rollie@bwsys.net

 

[tiger3p]

Ken,

Thanks for the reply. Not really sure about what you mean by main form / sub form. Nor do i have any idea how to set a parent/child link fields properties.

 

[tiger3p]

These are the fields w/ my two tables:

Interviewer Table:
ID (autonumber)
CompanyID (Interviewer will select their company ID)
LastName
FirstName
Middle
Email
Phone

Interviewee Table:
ID (Autonumbe)
IntervieweeLastName
IntervieweeFirstName
IntervieweeMiddle
IntervieweeEmail
IntervieweePhone
followed by 12 questions in Memo format.

The intervieweer can have multiple Interviewee but Interviewee can only be interviewed once.

Hope this clarifies it.

Thanks again for the help!

 

[KenReay]

Hi

MAinform sub form is a common method of presenting data in a one to many situation such as you have (one inteview, many questions)

but looking at your tables you have no common field, Is the common fiedl meant to be ID ?, if yes it cannot be an autonumber in both tables, in the interviewee table it should be a type long.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tiger3p]

Ken,

With regards to that. I went ahead and created the field name InterviewID (Text, No duplicates)

The relationship I have w/ the two table is 1 - 1 using the InterviewID. But it's still not saving the records. What I eventually want to see i that I have say Bob Jones with his company information. Under Bob Jones, it will have all the people he interviewed with and their respective responses. The forms i have created doesn't seem to save those records. I even went and created just 1 table w/ all the fields in it, then have those two forms but no luck either.

 

[tiger3p]

This is the code behind the Interviewer Form button that is click:
Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Interviewee"

stLinkCriteria = "[InterviewID]=" & "'" & Me![InterviewID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_Interviewer", acSaveYes

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

And the code behind the close button to close the Interviewee Form and save the records:
Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stLinkCriteria As String

stLinkCriteria = "[InterviewID]=" & "'" & Me![InterviewID] & "'"

DoCmd.Close acForm, "frm_Interviewee", acSaveYes

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

But i don't think these codes are working.

 

[KenReay]

Hi

"With regards to that. I went ahead and created the field name InterviewID "

It Needs to be a numeric Long

You cannot have created a relationship between the two id fields if they are not the same type, Autonumber is a numeric long, you say you have created Id column as a text.

THe code you show, is not using Sub forms.

The code

DoCmd.Close acForm, "frm_Interviewee", acSaveYes

closes the form and saves the FORM, not the data

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK