microsoft.public.access.forms

Looking at the code you presented 'way back when, that shouldn't be the
case. You're only adding to the Where clause when there's a value in a
particular field. If there's no value, everything should be returned. Did
you change that logic?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"tmdrake" wrote in message
news:A4B3895E-4AE1-4B63-92E6-80B0BFCC831F@microsoft.com...
> Okay Doug, it appears we're getting closer.
>
> This is what's happening now. I have to select something from the
> ProjectID
> field (once this done all the records for this field appear). However, in
> order to choose for any of the other fields (DisciplineName,
> SectionNumber,
> LastName) I have to choose from the ProjectID field also.
>
> In other words I can either choose the ProjectID individually or in any
> combination with the other fields and the correct records will appear.
>
> Now the problem is I need for the other fields to work individually also.
> If this can be accomplished, how do I make this happen?
>
> Thanks
> --
> tmdrake
>
>
> "Douglas J. Steele" wrote:
>
>> Your query has a field [Staff Last Name] in it, but your condition is
>> providing a value for [LastName]. Which is correct? You mentioned that
>> one
>> of the prompts is first name. Your query has a field [Staff First Name]
>> in
>> it. Is that the correct name?
>>
>> If fixing those don't solve the problem, copy the string from the
>> Immediate
>> window to the clipboard. Go create a new query. When prompted to select
>> what
>> tables you want in the query, don't select anything. Switch the query to
>> the
>> SQL view (it's on the View menu). Paste that SQL into the query's SQL
>> view
>> and try to run it. Are the error messages you get when trying to run the
>> query any help in resolving what's wrong with the query?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "tmdrake" wrote in message
>> news:8A780B51-7AA4-4278-9EA6-0741E80449FB@microsoft.com...
>> > Doug,
>> >
>> > This is what is in the immediate:
>> >
>> > SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
>> > Resources].DisciplineName, [tblProject Staffing
>> > Resources].SectionNumber,
>> > [tblProject Staffing Resources].[Staff Last Name], [tblProject Staffing
>> > Resources].[Staff First Name], [tblProject Staffing
>> > Resources].[Discipline
>> > Lead], [tblProject Staffing Resources].[Est Project Start Date],
>> > [tblProject
>> > Staffing Resources].[Est Project End Date], [tblProject Staffing
>> > Resources].EmployeeID FROM [tblProject Staffing Resources] WHERE True
>> > AND
>> > [DisciplineName] = 'Thermal' AND [SectionNumber] = '4470' AND
>> > [LastName] =
>> > 'Brown'
>> >
>> > I'm assuming somehting in this string is causing the problem. Please
>> > help
>> > me to understand how to fix it.
>> >
>> > As always you help is greatly appreciated.
>> >
>> > Thanks
>> > --
>> > tmdrake
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> Sorry, I don't take databases from others.
>> >>
>> >> Just to make sure that the Debug.Print is firing, change it to
>> >>
>> >> Debug.Print "strSQL = " & strSQL
>> >>
>> >> If it's working and strSQL is actually empty, you should at least see
>> >>
>> >> strSQL =
>> >>
>> >> in the Immediate window.
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "tmdrake" wrote in message
>> >> news:AEDEA9D7-B9E4-4426-9FBB-736E970B0081@microsoft.com...
>> >> > There's nothing there. I've place the Debug.Print line immediately
>> >> > in
>> >> > front
>> >> > of the Me. string and also tried it on the line above the Me.
>> >> > string.
>> >> > Nothing's there. Is there anyway I can send this to you and you
>> >> > take a
>> >> > look
>> >> > and tell me what I'm doing wrong?
>> >> > --
>> >> > tmdrake
>> >> >
>> >> >
>> >> > "Douglas J. Steele" wrote:
>> >> >
>> >> >> What do you mean nothing's there? You've got all that code that's
>> >> >> assigning
>> >> >> a string to strSQL. There must be something printed out by the
>> >> >> Debug.Print
>> >> >> line!
>> >> >>
>> >> >> --
>> >> >> Doug Steele, Microsoft Access MVP
>> >> >> http://I.Am/DougSteele
>> >> >> (no private e-mails, please)
>> >> >>
>> >> >>
>> >> >> "tmdrake" wrote in message
>> >> >> news:2587584C-0AB5-4334-9B84-B7E16C68FA3F@microsoft.com...
>> >> >> > Doug,
>> >> >> >
>> >> >> > Nothing's there.
>> >> >> > --
>> >> >> > tmdrake
>> >> >> >
>> >> >> >
>> >> >> > "Douglas J. Steele" wrote:
>> >> >> >
>> >> >> >> So exactly what's in strSQL when you're assigning it as the
>> >> >> >> RecordSource?
>> >> >> >> Put
>> >> >> >>
>> >> >> >> Debug.Print strSQL
>> >> >> >>
>> >> >> >> in your code directly in front of the line
>> >> >> >>
>> >> >> >> Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL
>> >> >> >>
>> >> >> >> then go to the Immediate window (Ctrl-G) to see what's there.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> http://I.Am/DougSteele
>> >> >> >> (no e-mails, please!)
>> >> >> >>
>> >> >> >>
>> >> >> >> "tmdrake" wrote in message
>> >> >> >> news:D1A89B7D-1A50-4D70-AD1A-8A88D8D05A83@microsoft.com...
>> >> >> >> > Doug,
>> >> >> >> >
>> >> >> >> > Now that I've made the change (inserting the space), when I
>> >> >> >> > hit
>> >> >> >> > the
>> >> >> >> > Select
>> >> >> >> > button, two parameter boxes popup, one asking for the last
>> >> >> >> > name
>> >> >> >> > and
>> >> >> >> > the
>> >> >> >> > another asking for the first name. Once this information is
>> >> >> >> > entered, I
>> >> >> >> > get a
>> >> >> >> > error message stating "You canceled the previous operation".
>> >> >> >> >
>> >> >> >> > How do I fix this?
>> >> >> >> >
>> >> >> >> > Thanks
>> >> >> >> > --
>> >> >> >> > tmdrake
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Douglas J. Steele" wrote:
>> >> >> >> >
>> >> >> >> >> The code
>> >> >> >> >>
>> >> >> >> >> & "[tblProject Staffing Resources].EmployeeID" _
>> >> >> >> >> & "FROM [tblProject Staffing Resources] WHERE True"
>> >> >> >> >>
>> >> >> >> >> means that the resulting SQL will be:
>> >> >> >> >>
>> >> >> >> >> [tblProject Staffing Resources].EmployeeIDFROM [tblProject
>> >> >> >> >> Staffing
>> >> >> >> >> Resources] WHERE True"
>> >> >> >> >>
>> >> >> >> >> It needs to be
>> >> >> >> >>
>> >> >> >> >> [tblProject Staffing Resources].EmployeeID FROM [tblProject
>> >> >> >> >> Staffing
>> >> >> >> >> Resources] WHERE True"
>> >> >> >> >>
>> >> >> >> >> which means that your code needs to be
>> >> >> >> >>
>> >> >> >> >> & "[tblProject Staffing Resources].EmployeeID " _
>> >> >> >> >> & "FROM [tblProject Staffing Resources] WHERE True"
>> >> >> >> >>
>> >> >> >> >> (Note the space between the D and the closing quote in the
>> >> >> >> >> first
>> >> >> >> >> line)
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> >> http://I.Am/DougSteele
>> >> >> >> >> (no e-mails, please!)
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "tmdrake" wrote in message
>> >> >> >> >> news:05A19574-6750-453F-8BFE-A97243C4FA35@microsoft.com...
>> >> >> >> >> > Hi Doug,
>> >> >> >> >> >
>> >> >> >> >> > I'm not sure what you mean. I tried inserting a space
>> >> >> >> >> > after
>> >> >> >> >> > the
>> >> >> >> >> > last
>> >> >> >> >> > field
>> >> >> >> >> > name, however I still get the same error message. Please
>> >> >> >> >> > explain.
>> >> >> >> >> >
>> >> >> >> >> > Thanks
>> >> >> >> >> > --
>> >> >> >> >> > tmdrake
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >> > "Douglas J. Steele" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> You're missing a space between the last field name
>> >> >> >> >> >> ([tblProject
>> >> >> >> >> >> Staffing
>> >> >> >> >> >> Resources].EmployeeID) and the keyword FROM.
>> >> >> >> >> >>
>> >> >> >> >> >> --
>> >> >> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> >> >> http://I.Am/DougSteele
>> >> >> >> >> >> (no e-mails, please!)
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> "tmdrake" wrote in message
>> >> >> >> >> >> news:364DFA0C-0FA9-4C95-B831-0B589EA3FFD4@microsoft.com...
>> >> >> >> >> >> > John,
>> >> >> >> >> >> >
>> >> >> >> >> >> > Here is the code:
>> >> >> >> >> >> >
>> >> >> >> >> >> > Private Sub Select_Click()
>> >> >> >> >> >> > Dim strSQL As String
>> >> >> >> >> >> >
>> >> >> >> >> >> > strSQL = "SELECT [tblProject Staffing
>> >> >> >> >> >> > Resources].ProjectID,
>> >> >> >> >> >> > "
>> >> >> >> >> >> > _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].DisciplineName, " _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].SectionNumber, " _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].[Staff Last Name], "
>> >> >> >> >> >> > _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].[Staff First Name], "
>> >> >> >> >> >> > _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].[Discipline Lead], "
>> >> >> >> >> >> > _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].[Est Project Start
>> >> >> >> >> >> > Date], "
>> >> >> >> >> >> > _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].[Est Project End
>> >> >> >> >> >> > Date],
>> >> >> >> >> >> > " _
>> >> >> >> >> >> > & "[tblProject Staffing Resources].EmployeeID" _
>> >> >> >> >> >> > & "FROM [tblProject Staffing Resources] WHERE True"
>> >> >> >> >> >> > If Not IsNull(Me![DisciplineName]) Then
>> >> >> >> >> >> > strSQL = strSQL & " AND [DisciplineName] = '" &
>> >> >> >> >> >> > Me![DisciplineName]
>> >> >> >> >> >> > &
>> >> >> >> >> >> > "'"
>> >> >> >> >> >> > End If
>> >> >> >> >> >> > If Not IsNull(Me![SectionNumber]) Then
>> >> >> >> >> >> > strSQL = strSQL & " AND [SectionNumber] = '" &
>> >> >> >> >> >> > Me![SectionNumber]
>> >> >> >> >> >> > &
>> >> >> >> >> >> > "'"
>> >> >> >> >> >> > End If
>> >> >> >> >> >> > If Not IsNull(Me![ProjectID]) Then
>> >> >> >> >> >> > strSQL = strSQL & " AND [ProjectId] = '" &
>> >> >> >> >> >> > Me![ProjectID] &
>> >> >> >> >> >> > "'"
>> >> >> >> >> >> > End If
>> >> >> >> >> >> > If Not IsNull(Me![LastName]) Then
>> >> >> >> >> >> > strSQL = strSQL & " AND [LastName] = '" & Me![LastName]
>> >> >> >> >> >> > &
>> >> >> >> >> >> > "'"
>> >> >> >> >> >> > End If
>> >> >> >> >> >> >
>> >> >> >> >> >> > CREATING THE ERROR MESSAGE:
>> >> >> >> >> >> >
>> >> >> >> >> >> > Me.Project_Staffing_Resources_subform.Form.RecordSource
>> >> >> >> >> >> > =
>> >> >> >> >> >> > strSQL
>> >> >> >> >> >> >
>> >> >> >> >> >> > Thanks
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>