Student Notes

[Blakey300]

Hi

I am using Access 2007 and have the following scenerio in my DB

I have a notes table linked to my students table

My note table has an ID, Date Student ID and Note (Memo) Fields

On my students form I have a Continous subform for the notes so that it only
displays the notes for the current student.

I want to be able to double click on a note to show that note in a new full
page form using the dblclick event, however it always shows the 1st note for
that student and not the note i double clicked on, am guessing it is a
filtering issue but am struggling any advice would be gratefully received.

Dave

 

[Allen Browne]

Set the On DblClick property of your command button to:
[Event Procedure]

Then click the Build button (...) beside this.
Access opens the code window.
Set up the code as below.
Substitute the name of the form to be opened for Form2.

Private Sub cmdShowNote_DblClick(Cancel As Integer)
Dim strWhere As String
Const strcTargetForm = "Form2"

If Me.Dirty The Me.Dirty = False
If Me.NewRecord Then
MsgBox "Select a note to view."
Else
strWhere = "ID = " & Me.ID
If CurrentProject.AllForms(strcTargetForm ).IsLoaded Then
DoCmd.Close acForm,strcTargetForm
DoCmd.OpenForm strcTargetForm, WhereCondition:=strWhere
End If
End If
End Sub

Notes:
1. Some versions of Access will still give you concurrency issues with memo
fields, even though the code ensures your original record is not dirty
before opening the target one.

2. The code closes the form if it is already open, since the WhereCondition
would not be applied otherwise.

3. You might like to avoid using field names that are reserved words, such
as Note and Date. For example, there are contexts where Access will
misunderstand Date to mean the current system date instead of the contents
of the field named Date, and you don't want those kinds of ambiguities
messing up your data.

Here's a list of the names to avoid:
http://allenbrowne.com/AppIssueBadWord.html
Refer to it when creating tables.

 

[Bob Larson]

Here's a list of the names to avoid:

http://allenbrowne.com/AppIssueBadWord.html
Refer to it when creating tables.

OR when creating ANY objects actually.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Blakey300]

Thanks for your response Allen but unfortunatly I couldn't get it to work
(probably me being thick) however I have restructured my tables and forms and
managed to acheive what I require using queries and macros.

Regards

Dave

Set the On DblClick property of your command button to:
[Event Procedure]

Then click the Build button (...) beside this.
Access opens the code window.
Set up the code as below.
Substitute the name of the form to be opened for Form2.

Private Sub cmdShowNote_DblClick(Cancel As Integer)
Dim strWhere As String
Const strcTargetForm = "Form2"

If Me.Dirty The Me.Dirty = False
If Me.NewRecord Then
MsgBox "Select a note to view."
Else
strWhere = "ID = " & Me.ID
If CurrentProject.AllForms(strcTargetForm ).IsLoaded Then
DoCmd.Close acForm,strcTargetForm
DoCmd.OpenForm strcTargetForm, WhereCondition:=strWhere
End If
End If
End Sub

Notes:
1. Some versions of Access will still give you concurrency issues with memo
fields, even though the code ensures your original record is not dirty
before opening the target one.

2. The code closes the form if it is already open, since the WhereCondition
would not be applied otherwise.

3. You might like to avoid using field names that are reserved words, such
as Note and Date. For example, there are contexts where Access will
misunderstand Date to mean the current system date instead of the contents
of the field named Date, and you don't want those kinds of ambiguities
messing up your data.

Here's a list of the names to avoid:
http://allenbrowne.com/AppIssueBadWord.html
Refer to it when creating tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

I am using Access 2007 and have the following scenerio in my DB

I have a notes table linked to my students table

My note table has an ID, Date Student ID and Note (Memo) Fields

On my students form I have a Continous subform for the notes so that it
only
displays the notes for the current student.

I want to be able to double click on a note to show that note in a new
full
page form using the dblclick event, however it always shows the 1st note
for
that student and not the note i double clicked on, am guessing it is a
filtering issue but am struggling any advice would be gratefully received.

Dave