Okay, what is it about Access that makes you crazy?!?! You think you have a problem solved, then a few days later the problem is back. The description of the problem is in the bottom section of this posting. Here is the code I'm using to try to get values into a Query so a user can make a selection either by teacher or grade level. (The code for the Grade Level is identical except for the names of the variables, etc.)
By Teacher:
Private Sub cmdContinueACC_Click()
Dim strTName As String
If Me.txtTeacherName <> "" Then
SetName Me.txtTeacherName
DoCmd.OpenForm "frmACCForm", acNormal
DoCmd.Close acForm, "frmSelectByTeacherACC", acSaveNo
Else
MsgBox "Please enter a name.", vbOKOnly, "Teacher Name required"
End If
End Sub
Here is the Function that creates the value passed to the Query:
Option Compare Database
Private strName As String
----------
Public Sub SetName(Value As String)
strName = Value
End Sub
Public Function TName()
TName = strName
End Function
I used the QBE grid to create the Query, but here is the SQL code behind it:
SELECT MasterList.ID, MasterList.LastName, MasterList.FirstName, MasterList.Teacher, MasterList.GradeLevel,
MasterList.MathExtendedTime, MasterList.MathCalculator, MasterList.MathVisualAid,
MasterList.ReadingReadAloud, MasterList.ReadingExtendedTime, MasterList.ReadingVisualAid,
MasterList.SSExtendedTime, MasterList.SSVisualAid,
MasterList.ScienceExtendedTime, MasterList.ScienceVisualAid,
FROM MasterList
WHERE (((MasterList.Teacher)=TName())) OR (((MasterList.GradeLevel)=TGrade()));
________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, July 31, 2012 12:07 PM
Subject: RE: [MS_AccessPros] A Query Problem
Dan,
Feel free to share "some code that takes ..." and the SQL of "this Query to show the results".
Most of us veterans build a WHERE CONDITION to use when opening forms and/or reports.
Duane Hookom
MS Access MVP
----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: amessyguy@yahoo.com
> Date: Tue, 31 Jul 2012 09:02:36 -0700
> Subject: [MS_AccessPros] A Query Problem
>
> I'm helping (a relative term) a friend with a problem passing more than one value to a Query. We want to view student records either by Grade or by Teacher.
>
> I have some code that takes the selected grade from a ComboBox on a "selection Form" and passes it to the Query where the criteria for the GradeLevel field is Grade(). It works perfectly.
>
> I basically duplicated the Grade code, changing things where necessary, except this time for the teacher name. It is on a separate "selection Form" that takes the teacher name entered into a TextBox and passes it along to the same Query where the criteria for the Teacher field is TName().
>
> There is a Form based on this Query to show the results. It is supposed to open up having filtered records based on either of the selected criteria. Everything works just fine--if I only have one criteria in my Query. If I try to add the second criteria to the Query the results will only filter the last entry. In other words, if on my first time through I select Smith for teacher I get the records for Smith's class. I click a button that takes me back and this time I try Jones. When I go to view the selected students I still see Smith's students. If I click on the button that takes me back and this time select "7" for the grade level, I still see Smith's class.
>
> In order to get the TName() criteria to work I have to remove the Grade() criteria and vice versa. Can you help me so I can get both of the criteria to play well together--in the same Query.
>
> Hope this makes sense. As always--thanks!!!
>
> Dan
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar