queries relationship to form data? 2 Q's

  • Thread starter Thread starter Bent
  • Start date Start date
B

Bent

Sorry about the length, please have patience. I have
written a small database for a driving school. Containing
the tables; Instructors, Students, Appointments and
Messages. The appointments table appears as a subform in
both the Students and Instructors tables, and the messages
table is a second subform in the instructors table.

I have form for my instructors, e.g. instructor's name,
id, address, phone number, etc - with the primary key
being instructor id. And a sub-form called messages,
linked one-to-many using the instructor id, that contains
a text field for messages and a check box for the
field 'MessageDelivered'. If I want to have a button that
runs a query on the form to list all messages for each
instructor that have not been delivered (unchecked), how
do I address the form? To be a little more clear sometimes
I see joins written like (I'll use my fields) 'WHERE
((Instructors.InstructorID) = (Messages.InstructorID)); as
opposed to 'WHERE (Instructors.InstructorID) = [Forms].
[Messages].[MessagesDeleivered]'

What is the correct syntax if I want to run a query given
the information on the form in front of the user, as
opposes to just running it off a table? I.e. make the
query use the values in the current record.

My second question builds on the first I guess. Given I
have an appointments subform on both the students and
instructors forms that shows all appointments for the
currently displayed student or instructor, how would I go
about having a query that would list all the relevant
details of the student (name address etc) given the
instructors form that contains the current appointments
for that instructor. In other words, I have an instructor
selected, it shows three appointments for example, given
the time and date etc, but this does not show the students
details, so if a receptionist wants to know, just by
looking at the instructors form, the details of the
student from the currently selected appointment, how would
I go about looking this up?

I realize these are possibly both very open-ended and at
the same time quite simple questions, but I am desperate
for assistance, and I am finding this really quite fun :)
(maybe I'm sick?)

Here is some table info that might help - please excuse
the length, and many, many thanks

Students

StudentID - autonumber - primary key
StuFirstname
StuLastname
StuAddress
StuHPhone
StuWPhone - etc

Instructors


InstructorID - integer - primary key
InsFirstame
InsLastname
InsPhone - etc

Appointments

AppTime
AppDate
AppointmentID - autonumber
Cost
InsructorID
StudentID

Messages

MessageID - autonumber primary key
InstructorID
Message
MessageDelivered - checkbox
 
Bent,

First point, you want to show all undelivered messages for the
instructor currently selected on the Instructors form... did I interpret
you correctly? Well, I suppose you will want to show this information
on a new form or report, and that this form or report will be based ona
query. The SQL of the query might look like this...
SELECT Message FROM Messages
WHERE MessageDelivered = 0
AND InstructorID = Forms!Instructors!InstructorID

Similarly with the second question... you need to have a command button
or some such on the Appointments subform, which will open a second form,
showing student details, based on the Students table. Just to change
the approach, use code something like this...
DoCmd.OpenForm "Students",,, "[StudentID]=" & Me.StudentID

--
Steve Schapel, Microsoft Access MVP

Sorry about the length, please have patience. I have
written a small database for a driving school. Containing
the tables; Instructors, Students, Appointments and
Messages. The appointments table appears as a subform in
both the Students and Instructors tables, and the messages
table is a second subform in the instructors table.

I have form for my instructors, e.g. instructor's name,
id, address, phone number, etc - with the primary key
being instructor id. And a sub-form called messages,
linked one-to-many using the instructor id, that contains
a text field for messages and a check box for the
field 'MessageDelivered'. If I want to have a button that
runs a query on the form to list all messages for each
instructor that have not been delivered (unchecked), how
do I address the form? To be a little more clear sometimes
I see joins written like (I'll use my fields) 'WHERE
((Instructors.InstructorID) = (Messages.InstructorID)); as
opposed to 'WHERE (Instructors.InstructorID) = [Forms].
[Messages].[MessagesDeleivered]'

What is the correct syntax if I want to run a query given
the information on the form in front of the user, as
opposes to just running it off a table? I.e. make the
query use the values in the current record.

My second question builds on the first I guess. Given I
have an appointments subform on both the students and
instructors forms that shows all appointments for the
currently displayed student or instructor, how would I go
about having a query that would list all the relevant
details of the student (name address etc) given the
instructors form that contains the current appointments
for that instructor. In other words, I have an instructor
selected, it shows three appointments for example, given
the time and date etc, but this does not show the students
details, so if a receptionist wants to know, just by
looking at the instructors form, the details of the
student from the currently selected appointment, how would
I go about looking this up?

I realize these are possibly both very open-ended and at
the same time quite simple questions, but I am desperate
for assistance, and I am finding this really quite fun :)
(maybe I'm sick?)

Here is some table info that might help - please excuse
the length, and many, many thanks

Students

StudentID - autonumber - primary key
StuFirstname
StuLastname
StuAddress
StuHPhone
StuWPhone - etc

Instructors


InstructorID - integer - primary key
InsFirstame
InsLastname
InsPhone - etc

Appointments

AppTime
AppDate
AppointmentID - autonumber
Cost
InsructorID
StudentID

Messages

MessageID - autonumber primary key
InstructorID
Message
MessageDelivered - checkbox
Click to expand...
 
Thanks for the reply Steve, the first answer does seem
straight forward, it seems from what you have written
thati can now identify the syntax needed to address
information in the forms fields i.e. your 'InstructorID =
Forms!Instructors!InstructorID' line. I am assuming
(havent tried it yet) that i can address all kinds of
fields with that syntax easily.

What i know about VB however you can write on a pinhead,
i will try and familiarize myself with it, one other
question though, i have read through a lot of posts here
and i see statements like '"[StudentID]=" & Me.StudentID'
regularly, more specifically, what does the 'Me.' part
mean? does 'Me' mean the value in the currently displayed
field?

Thanks again

-----Original Message-----
Bent,

First point, you want to show all undelivered messages for the
instructor currently selected on the Instructors form... did I interpret
you correctly? Well, I suppose you will want to show this information
on a new form or report, and that this form or report will be based ona
query. The SQL of the query might look like this...
SELECT Message FROM Messages
WHERE MessageDelivered = 0
AND InstructorID = Forms!Instructors!InstructorID

Similarly with the second question... you need to have a command button
or some such on the Appointments subform, which will open a second form,
showing student details, based on the Students table. Just to change
the approach, use code something like this...
DoCmd.OpenForm "Students",,, "[StudentID]=" & Me.StudentID

--
Steve Schapel, Microsoft Access MVP

Sorry about the length, please have patience. I have
written a small database for a driving school. Containing
the tables; Instructors, Students, Appointments and
Messages. The appointments table appears as a subform in
both the Students and Instructors tables, and the messages
table is a second subform in the instructors table.

I have form for my instructors, e.g. instructor's name,
id, address, phone number, etc - with the primary key
being instructor id. And a sub-form called messages,
linked one-to-many using the instructor id, that contains
a text field for messages and a check box for the
field 'MessageDelivered'. If I want to have a button that
runs a query on the form to list all messages for each
instructor that have not been delivered (unchecked), how
do I address the form? To be a little more clear sometimes
I see joins written like (I'll use my fields) 'WHERE
((Instructors.InstructorID) = (Messages.InstructorID)); as
opposed to 'WHERE (Instructors.InstructorID) = [Forms].
[Messages].[MessagesDeleivered]'

What is the correct syntax if I want to run a query given
the information on the form in front of the user, as
opposes to just running it off a table? I.e. make the
query use the values in the current record.

My second question builds on the first I guess. Given I
have an appointments subform on both the students and
instructors forms that shows all appointments for the
currently displayed student or instructor, how would I go
about having a query that would list all the relevant
details of the student (name address etc) given the
instructors form that contains the current appointments
for that instructor. In other words, I have an instructor
selected, it shows three appointments for example, given
the time and date etc, but this does not show the students
details, so if a receptionist wants to know, just by
looking at the instructors form, the details of the
student from the currently selected appointment, how would
I go about looking this up?

I realize these are possibly both very open-ended and at
the same time quite simple questions, but I am desperate
for assistance, and I am finding this really quite fun :)
(maybe I'm sick?)

Here is some table info that might help - please excuse
the length, and many, many thanks

Students

StudentID - autonumber - primary key
StuFirstname
StuLastname
StuAddress
StuHPhone
StuWPhone - etc

Instructors


InstructorID - integer - primary key
InsFirstame
InsLastname
InsPhone - etc

Appointments

AppTime
AppDate
AppointmentID - autonumber
Cost
InsructorID
StudentID

Messages

MessageID - autonumber primary key
InstructorID
Message
MessageDelivered - checkbox
Click to expand...
.
Click to expand...
 
Bent,

If you are referring to a field or a control on a form or report, or a
property of such, the Me is simply a shortcut to refer to the form or
report itself. If I want to refer in code to a control on another form,
I have to refer to it using syntax such as
Forms!NameOfOtherForm!NameOfControl whereas if the control is on the
form where the code is located, I can use Me!NameOfControl. So the line
of code I gave you...
DoCmd.OpenForm "Students",,, "[StudentID]=" & Me.StudentID
translates to "open the Students form at the record where the value of
the StudentID field is the same as the value if the StudentID field in
the currently selected record on this form".
 
Oh, and one other question, i just tried your first query,
and it works fine, thanks, however it prompts the user to
enter the instructor id even though its on the form, if i
enter it it does display the relevant data, is there
anyway to avoid user input?
 
My friend, you should have a medal!!

I can learn in five mins here what i cannot in days by
myslef, thank you again Steve!
 
Back
Top