Help With database problem

[Larry]

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.

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.

Your help would be greatly appreciated. Thanks in advance!

 

[Steve Schapel]

Larry,

You mentioned in your post that you have an InterviewrID field to relate
the two tables, but this does not show in your list of fields. I assume
your Interviewee table really looks like this....
Interviewee Table:
ID (Autonumber)
InterviewerID
IntervieweeLastName
IntervieweeFirstName
IntervieweeMiddle
IntervieweeEmail
IntervieweePhone
followed by 12 questions in Memo format.

If it doesn't, then it needs to, so in other words you have a field in
the Interviewee table where you can put the ID of the Interviewer.

 

[Larry]

Steve,

Yes I have InterviewID which is in both the tables. Also
have the ID field (autonumber).

But it just does not save the data on the interviewee
table. I can't figure it out. I tried using relationship
of 1 to 1 but it seems to replace the previous data entry.

 

[Larry]

What I eventually want to see is this: Bob Jones with his
company orofile 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.

 

[Steve Schapel]

Larry,

How have you got the forms set up? Is the Interviewee form a subform on
the Interviewer form, as your post would suggest by your use of the word
"under"? Or is the Interviewee form a separate form? If separate, what
method are you using to enter the InterviewerID on this form?

 

[Guest]

Larry,

The interviewee is another form that is drawing from the
Interviewee Table. I dont have a clue how to create
subforms. In entering the InterviewerID, I use a drop down
button w/ the Interviewer's initial. I have no idea how to
relate them. w/ 1 to 1, the records are not getting saved.

Thanks again for your continuing help!

 

[Larry]

Steve,

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.

 

[Steve Schapel]

Larry,

Hmmm. I can't really see any purpose in some of this code, and a bit
puzzled what you are trying to do. What is InterviewID? You haven't
mentioned anything about this before, but the code suggests it is a text
data type. Right? But to open the frm_Interviewee at a specified
record implies that the record already exists... which, if I understand
you correctly, is not the case, because you are entering new records.
Hope that makes sense. I suggest changing the code to this...

Private Sub Command12_Click()
DoCmd.OpenForm "frm_Interviewee"
DoCmd.GoToRecord , ,acNewRec
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Command42_Click()
DoCmd.Close acForm, Me.Name
End Sub

But in any case, this code is related to navigation-type functionality,
and none of it has got anything to do with data entry or data integrity.
This has to come from your form and table design. In this case, if
you have a combobox on your frm_Interviewee form, which is bound to the
InterviewerID field in the Interviewee table, then this should be all
that is required. Just enter the Interviewee data as required for the
interviewee, and select the applicable interviewer from the combobox.

If I am off the mark here, please post back with an explanation of what
you think the code you showed should be doing, and what you are really
trying to achieve.

 

[Guest]

Steve,

You're exactly right. The code itself is for navigating in
the form. I wanted to have the button that the user will
can click and update the database once the form closes.
For example, in the form frm_Interviewer, the user will
enter in their information as well as their "InterviewerID"
from the combo box. Once they do this, they will then
click on the button "To Interviews" which suppose to update
the tbl_Interviewer, close the form, and open the form
frm_Interviewee. In the frm_Interviewee, the Interviewer
will enter in the Interviewee's information and answers to
the questions. They will then click on the "Close" button
and this suppose to update the tbl_Interviews, close the
form and put them back to the Start up page.

The tbl_Interviews should be linked to the tbl_Interviewer
so that I know who interviewed who. The problem I am
coming across is that I can't get it to update both table.

Thanks for helping me with this problem.
Larry

-----Original Message-----
Larry,

Hmmm. I can't really see any purpose in some of this code, and a bit
puzzled what you are trying to do. What is InterviewID? You haven't
mentioned anything about this before, but the code suggests it is a text
data type. Right? But to open the frm_Interviewee at a specified
record implies that the record already exists... which, if I understand
you correctly, is not the case, because you are entering new records.
Hope that makes sense. I suggest changing the code to this...

Private Sub Command12_Click()
DoCmd.OpenForm "frm_Interviewee"
DoCmd.GoToRecord , ,acNewRec
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Command42_Click()
DoCmd.Close acForm, Me.Name
End Sub

But in any case, this code is related to navigation-type functionality,
and none of it has got anything to do with data entry or data integrity.
This has to come from your form and table design. In this case, if
you have a combobox on your frm_Interviewee form, which is bound to the
InterviewerID field in the Interviewee table, then this should be all
that is required. Just enter the Interviewee data as required for the
interviewee, and select the applicable interviewer from the combobox.

If I am off the mark here, please post back with an explanation of what
you think the code you showed should be doing, and what you are really
trying to achieve.

--
Steve Schapel, Microsoft Access MVP

Steve,

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.

.

 

[Steve Schapel]

Ok, Larry, I am starting to get a bit clearer now about what is going
on. A few comments about what you have written...
1. I don't think the interviewer will enter the InterviewerID on
frm_Interviewer form with a combobox. This is not possible. First of
all, for their record to be seen in the combobox, it must already exist
in the table, which will not be the case at the time when they are
entering the record, if you see what I mean. But in any case, you told
me the InterviewerID is an autonumber field, so there's no entering it,
it just happens!
2. Any data entered or edited on the frm_Interviewer form will
automatically be saved to the tbl_Interviewer when the form is closed,
or if you move on the frm_Interviewer to another interviewer record.
There is generally no need to explicitly do this, but if you do need to,
the applicable code is...
DoCmd.RunCommand acCmdSaveRecord
3. Relationships between tables don't actually "do" anything. The way
you know who interviewed whom is by the entry in the InterviewerID field
in the Interviewee table. That's it, no more, no less.
4. The only functionality that is required when you click the 'To
Interviews' button, is to open the frm_Interviewee at a new record. The
code I gave you before will do this...
Private Sub Command12_Click()
DoCmd.OpenForm "frm_Interviewee"
DoCmd.GoToRecord , ,acNewRec
DoCmd.Close acForm, Me.Name
End Sub
This supposes that you will manually enter the InterviewerID on the
frm_Interviewee form via a combobox list of interviewers. If you want
the InterviewerID to be automatically entered for you, based on the
current record on the frm_Interviewer form, then you would extend the
code like this...
Private Sub Command12_Click()
DoCmd.OpenForm "frm_Interviewee"
DoCmd.GoToRecord , ,acNewRec
Forms!frm_Interviewee!InterviewerID = Me.InterviewerID
DoCmd.Close acForm, Me.Name
End Sub

Hope this might move us forward.

 

[Larry]

Steve,

That seems to work! The only thing I'm seeing in the forms
is that I now have to enter the InterviewerID twice. For
example. In my frm_Interviewer, the fields are:
InterviewID(manual entry), InterviewerID (taken from the
combo box), LastName, FirstName, Mid, Email, Phone, ID
(autonumber). Once I enter in the records, this forms
closes and opens up the frm_Interviewee. In the
frm_Interviewer, the fields are:
InterviewID (this is now being automatically field from the
records in frm_Interviewer), InterviewerID (I still have to
select from the combo box. Must match what interviewerID
selected from the frm_Interviewer), ID (autonumber),
LastName, FirstName, Mid, and so forth... I would like to
have to be able the InterviewerID to show up on the
frm_Interviewee as well.

As the reports I want to be able to get out is who
interview who and what was the responses.

Is it possible for me to show u my sample database and see
if I'm missing something? Thanks again for all your help!

Larry

 

[Steve Schapel]

Larry,

I would be willing to look at the database if necessary, but I don't
think it's quite come to that yet Let's discuss another couple of
things first...

It has suddenly occurred to me that you might be entering the
Interviewer data in for each interview. Is this right? This is the
only way I can understand the idea that you are entering InterviewerID
in a combobox on the frm_Interviewer form. Didn't you originally say
that each interviewer can interview more than one interviewee? But that
each interviewee is only interviewed once? I asked before, but you
didn't respond: What is InterviewID? Doesn't make sense to me. Your
frm_Interviewer should be based on your Interviewer table. At last
count, this to me looks like these fields...
InterviewerID (autonumber)
LastName
FirstName
Mid
Email
Phone
nothing else!!!!!!!!!!!

Your frm_Interviewee should be based on your Interviewee table. At last
count, this to me looks like these fields...
IntervieweeID (autonumber)
InterviewerID (number)
LastName
FirstName
Mid
Email
Phone
12 Answers
nothing else!!!!!!!!!!

To be honest, the interview responses to the 12 questions should be in a
separate table, but I didn't want to complicate the issue when you are
struggling with another step in the process. Just try it like I said.
I think you are trying to make it much more complicated than it really
is. Just enter the record for each interviewer *once* on the
frm_Interviewers form. Enter the record, including their interview
responses, for each interviewee on the frm_Interviewees form. And on
the frm_Interviewees form, as I said before, use your combobox to enter
the interviewer that interviewed them. That's it. Nothing else!

 

[Larry]

Steve,

The InterviewID field is the # the corresponds to the
interviewee. I wanted this to be autonumber but they want
it to be related to an excel table which they already made.
In a sense, what I need is to have the Interviewee
anonymous and just be associated w/ an item say A1 and so
forth. I am entering interviewer's data on each interview.
The first form is where the interviewer enters in their
information and their initial (InterviewerID) which is not
an autonumber. There is an ID field which is an
autonumber. The second is where the interviewee's data and
responses are entered. I did thought about seperating the
answers but I would be more lost than I am now. Does this
makes sense? It would be nice if I can just take down the
Interviewer's initial from InterviewerID and have it
already populated but i think that requires more work and
also me knowing who the interviewer are. Better to have
them just fill it out.

 

[Steve Schapel]

Larry,

Ok, so the InterviewID field is related to the interviewee, and it is
not an autonumber, which is fine. But it should *absolutely not* be in
the Interviewer table. It has got *absolutely nothing* to do with the
interviewer. "I am entering interviewer's data on each interview" is
*incorrect*. As I have mentioned before, you should *not* do it this
way. This is the major contributing factor to the problems you are
experiencing. Set up your tables the way I showed you before! (except
replace IntervieweeID (autonumber) with InterviewID (from Excel data) in
the Interviewee table).

 

[Guest]

Steve,

In the interviewer table, these are my fields:
InterviewerID (AutoNumber)
LastName
FirstName
Middle
Email
Phone

I would like to know how do I relate this table to the
interviewee table?

Here are the fields in the Interviewee table:
InterviewID (Text, No Duplicates)
ID (Autonumber)
InterviewerID (Text)(this should be autonumber though i
believe)
FirstName
lastName
Middle
email
phone
12 questions

I wanted to use the Interviewer's initial as the
InterviewerID using the drop down button. I have a table
w/ the interviewer's initial and was using that for the
InterviewerID.

Thanks again!

 

[larry]

Steve,

Also when I try doing that w/ the relationship, I'm
getting an error of "Invalid field
definition 'InterviewerID' in definition of index or
relationship.

Larry

 

[Douglas J. Steele]

If InterviewerID is an AutoNumber in the Interviewer table, then it should
be a Long Integer in any table where it's a foreign key pointing to an entry
in the Interviewer table.

 

[Guest]

Doug,

Thanks. That's what I did. The relationship is now 1 to
many. which is good. The problem I now see is that when
I look at the tbl_Interviewer, I see the interviewer's
name multiple times and underneath, is a link to their
interviews. What I was hoping for was to see the
interviewer's name one time, and see the interviews under
their name. Is it something w/n the VB code? I tried
putting in a save record button, but all it did was save
the record but didn't clear the form.

Thanks again!

 

[Steve Schapel]

Larry,

As mentioned by Doug, if the InterviewerID in the Interviewer table is
an Autonumber, it needs to be a Number (Long Integer) in the Interviewee
table. It should be set as the primary key in the Interviewer table,
and it should *not* be set as the primary key in the Interviewee table.
The core concept of how this works is that the tables are related
based on "data in common" in the linking field. That means you can't
use the interviewer's initials as the InterviewerID in the Interviewee
table. It has to be a value that matches the InterviewerID in the
Interviewer table, which is a number. However, when you make your
combobox to go on the Interviewee form, you can set it up so that it
*displays* the interviewer's initials, if you like, even though the
actual data is still the InterviewerID... but that's another topic.

 

[Steve Schapel]

Larry,

Presumably this is because of the data you already entered using the
previous design? Each interviewer should only be entered once in the
tbl_Interviewer table. You will need to delete all duplicates from this
table. Then you will need to enter the correct InterviewerID for all
records in the Interviewee table.