Combo Box

  • Thread starter Thread starter Jacqueline B
  • Start date Start date
J

Jacqueline B

I am not sure if this is possible or not, as I am fairly
new to MS Access. I am currently running Access 97. Here
is my problem:

I have a form called frm_StudentContact. In this form I
have a combobox called cboStudentID. This cboStudentID
links to another table called tbl_Registrations. Each
time a student becomes involved with a guidance
counsellor, I enter a registration. The problem is, the
same student can be registered with up to 3 different
counsellors at the same time.

When the counsellors enter their day's activity in the
frm_StudentContact, I want to be sure that they are using
the correct registration unique to them. In this
cboClientID box, it indicates the client name,
registration date, and guidance staff. Is there a way
that I can create a msg box that pops up when the staff
pick a student that has multiple registrations? I would
like the msgbox to say something like "This Student has
Multiple Registrations, please ensure you select the
registration unique to you".

Any suggestions? Sorry for this msg being so long. I
would extremely appreciate any help.

Thanks again,
Jacqueline
 
Jackie

Yes. You're note wasn't clear about wether you are or are not tracking if students have multiple guidance counsellors. Does you're application have that information? If so, how does it store it

Regards
Ada

-- Hope you don't mind me calling you Jackie

----- Jacqueline B wrote: ----

I am not sure if this is possible or not, as I am fairly
new to MS Access. I am currently running Access 97. Here
is my problem

I have a form called frm_StudentContact. In this form I
have a combobox called cboStudentID. This cboStudentID
links to another table called tbl_Registrations. Each
time a student becomes involved with a guidance
counsellor, I enter a registration. The problem is, the
same student can be registered with up to 3 different
counsellors at the same time.

When the counsellors enter their day's activity in the
frm_StudentContact, I want to be sure that they are using
the correct registration unique to them. In this
cboClientID box, it indicates the client name,
registration date, and guidance staff. Is there a way
that I can create a msg box that pops up when the staff
pick a student that has multiple registrations? I would
like the msgbox to say something like "This Student has
Multiple Registrations, please ensure you select the
registration unique to you"

Any suggestions? Sorry for this msg being so long. I
would extremely appreciate any help

Thanks again
Jacquelin
 
Thanks for responding, Yes Jackie is fine by me.

As for your question, I am not exactly sure what you are
asking. I do track students who have multiple guidance
counsellors by a query that groups each student (from
tbl_Students), then from the registration table, groups by
registration date (tbl_Registrations) and then shows each
counsellor (tbl_Registrations). Is this the info you are
asking? I apologize for being unclear. I am just a
newbie who is in over her head and swimming with the big
fishes!!! ;)

Thanks Adam,
Jacqueline

-----Original Message-----
Jackie,

Yes. You're note wasn't clear about wether you are or are
Click to expand...
not tracking if students have multiple guidance
counsellors. Does you're application have that
information? If so, how does it store it?
 
What you need is a "junction" table that would look like this:

tblStudentCounselor
tblPKey (primary key - autonumber)
StudentID (composite unique index with CounselorID)
CounselorID (composite unique index with StudentID)


Then use tblPKey field's value as the foreign key in the tbl_Registrations
table to uniquely identify a student - counselor combination for the
records' data.

Your combo box should be based on the tblStudentCounselor table shown above,
filtered for the specific counselor who is using the form. For example, if
you have a control on the form by which the counselor selects his/her name
(and that control is named cboCounselor, and the form is named
frm_StudentContact), you can use a Row Source query similar to this for the
cboStudentID combo box:

SELECT tblPKey, StudentID, CounselorID FROM tblStudentCounselor WHERE
CounselorID = [Forms]![frm_StudentContact]![cboCounselor] ORDER BY
StudentID;

Then you would run code on the After Update event of the cboCounselor combo
box to requery the cboStudentID combo box to show only the students
associated to that counselor:

Private Sub cboCounselor_AfterUpdate()
Me.cboStudentID.Requery
End Sub

I note that you said you are fairly new to ACCESS, so some/all(?) of this
may be a bit "foreign". But look it over and then post back with additional
questions. It's important that you get the table structure correct first
before we get into the forms' setup.
 
Jacqueline said:
I am not sure if this is possible or not, as I am fairly
new to MS Access. I am currently running Access 97. Here
is my problem:

I have a form called frm_StudentContact. In this form I
have a combobox called cboStudentID. This cboStudentID
links to another table called tbl_Registrations. Each
time a student becomes involved with a guidance
counsellor, I enter a registration. The problem is, the
same student can be registered with up to 3 different
counsellors at the same time.

When the counsellors enter their day's activity in the
frm_StudentContact, I want to be sure that they are using
the correct registration unique to them. In this
cboClientID box, it indicates the client name,
registration date, and guidance staff. Is there a way
that I can create a msg box that pops up when the staff
pick a student that has multiple registrations? I would
like the msgbox to say something like "This Student has
Multiple Registrations, please ensure you select the
registration unique to you".
Click to expand...


Instead of a msgbox, why not change the registration text
box to a combo box that only allows for registrations for
the client that has already been selected. This should
minimize the entry of invalid registrations. It's even
pretty easy to fill in the registrations combo box
auotmatically when there's only one registration for the
selected client. A short article about having a combo box's
list depend on the value in another control is described at:
http://www.mvps.org/access/forms/frm0028.htm

You didn't say how new registrations are added so I don't
know if the above idea would interfere or not.
 
Back
Top