Solved

Custom Search Form

Posted on 2014-11-28
77
162 Views
Last Modified: 2014-12-04
I'm trying to recreate a search form (or something similar) that I used in a database i did years ago.  The functionality I am after is having a search form with multiple optional criteria fields.  When the user clicks "search" the detail section of the form would show the corresponding records.  The user could then click on the individual record to open the details screen about that record.  As I mentioned, I did one years ago that was very useful but it has since been modified heavily, and no longer works.  I used "strSQL" as a variable to build a statement that would filter the records as desribed based on the users inputs but there have been too many changes to figure out what my original code was.
0
Comment
Question by:yoducati
  • 37
  • 22
  • 17
  • +1
77 Comments
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
The idea remains the same.
A filter is a valid SQL WHERE statement without the word WHERE as a string
You compose the string and then apply it

Dim TheString as string
TheString = "SomeFieldName = " & me.SomeNumericControlValue
TheString = TheString & " AND SomeOtherField = " & chr(34) & Me.SomeStringControlValue & chr(34)
'That one was an AND condition
TheString = TheString & " OR SomeOtherField = #" & Me.SomeDateControlValue & "#"
'That one was an OR condition
Me.Filter = TheString
Me.FilterOn = true
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
multiple optional criteria fields
You then build the code so that if a control has a value it gets concatenated into the string with the appropriate fieldname.
If a control does not have a valid value, then it gets left out.

When I built mine, I did it with comboboxes.
The combobox's AfterUpdate Event would feed in the name of the combobox and its value to a string building sub.
The sub would build, alter, or trash the filter.
0
 
LVL 84
Comment Utility
Helen Feddema has a nice one. She has an few images, and a link to a download at her site, here: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26211623.html.

I also do very much what Nick mentions, except I use the Click event of the button to run a routine that looks at the various controls on the form and builds it up.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
I needed to do exactly the same thing quite recently.
I have attached the test database I used when designing the main project.
It does exactly what you want.

Just enter your search criteria into the three fields at the top and click SEARCH.
If you want to narrow your search by date just enter FROM and TO dates.
Press CLEAR to show all records and start again.

Click ALL DATA in the subform to see full details for a particular item

The only code used is behind the buttons.
The searches are done through the qSEARCH_DEMOsubform query where criteria like this ...
Like "*" & [FORMS]![SEARCH DEMO]![TextNAME_SEARCH] & "*"

Open in new window

refers to unbound text boxes in the main form.
Planets.accdb
0
 

Author Comment

by:yoducati
Comment Utility
Hi everyone.  Thanks for the responses.  Sorry for the delay, I was out of the office for the holiday.  It looks like Helen's form does what I am trying to incorporate so I'll start playing with that and see if I can modify it for mine easily.  Eirman, I wasn't able to open your sample database.  When I click on it I get a pop up window with a bunch of crazy text.  I'm wondering if its because its not zipped?  I opened Helens with no problem.  Let me know if you can get it to work, I'd like to take a look at it too.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Hi yoducati,

Just be sure that there was nothing wrong the attachment I opened it on an old XP machine
with Office 2007 and it works fine.

Please try again.
Its probably best to save it first, then open it in Access 2007/2010/2013.

.... and it really does precisely what you want!
0
 

Author Comment

by:yoducati
Comment Utility
There isn't anything I can save though.  I don't get a prompt or anything when I click on it, just the pop up of crazy text.  If I right click I don't get any options to save it.
0
 

Author Comment

by:yoducati
Comment Utility
With regard to Helens search form right now it does have one issue which I think is a non-starter for me although I'm not sure yet.  I don't want the form to open a bunch of records and then filter them.  I want the detail section of the form to be blank until the user "clicks search".  At that point the criteria they have selected would be applied and the results returned in the detail section.  Then the user can select the record they want and open the detailed form for that record.  How can I modify Helens search form to have that behavior without breaking it?
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
This is an access 2003 version
Planets.mdb
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Here's both of them zipped up
Planets.zip
0
 

Author Comment

by:yoducati
Comment Utility
The 2003 version opened right up.  This seems to do what I want but I have two issues.  First, as I stated above I need to be able to have the details open up blank and populate after the user clicks search.  Second, I can't figure out how your form works.  I'm looking at the code and I see what makes the navigation buttons work but I'm not sure where the code for the search fields is.  It seems much simpler than the other one, or maybe I'm missing something.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
I'm not sure where the code for the search fields is
That because there in NO code which make is much simpler and easy to amend.

The searches are done through the qSEARCH_DEMOsubform query where criteria like this ...
Like "*" & [FORMS]![SEARCH DEMO]![TextNAME_SEARCH] & "*":

Open in new window

refers to unbound text boxes in the main form.

Please read my first post in this thread

(What version of Access do you have)

If you start with blank dates, no records will show.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
To show no records, remove this from the OnLoad event of the form

    TextFROMDATE_SEARCH = #1/1/2000#
    TextTODATE_SEARCH = Date

You need to go into the design view of qSEARCH_DEMOsubform
to see how the searches work.
0
 

Author Comment

by:yoducati
Comment Utility
That makes it open with no records but then the search button doesnt work.  I see having the criteria in the query but its not working on my form.  When I try to filter by zip code for example, the filter happens before the user clicks search, and doesnt find records exactly matching the entry.  When I run it in the query alone Im not being prompted for the value of the form field like I should be.  The query runs and returns less records like its being filtered somehow but without criteria to do so Im not sure what its doing.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
As a quick solution my above suggestion was not good.

I'll upload a new version later today
You have to have something in the dates fields for the search to work

What's your access version?
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Before I make changes,
can you confirm that if you click on "default dates" and search for a number-A
that it works.
0
 

Author Comment

by:yoducati
Comment Utility
It doesnt work at all now that I removed that code from the on load event.  Im using access 2010.  I had a great search form built years ago using sql

strSQL = "select [Record Number], [Street Address], [City], [State], [Zip Code], [APN] from tblProperties where "
   
   If Len(strStreet) > 0 Then
      strSQL = strSQL + "[Street Address] like '*" & strStreet & "*' "
      bCont = True
   End If
   
   If Len(strCity) > 0 Then
      strSQL = strSQL + "and [City] like '*" & strCity & "*' "
      bCont = True
   End If
   
   If Len(strState) > 0 Then
      strSQL = strSQL + "and [State] = '" & strState & "' "
      bCont = True
   End If
   
   If Len(strZip) > 0 Then
      strSQL = strSQL + "and [Zip Code] = '" & strZip & "' "
      bCont = True
   End If
     
   If Len(strAPN) > 0 Then
      strSQL = strSQL + "and [APN] like '*" & strAPN & "*' "
      bCont = True
   End If


This is just the part that I remember but there has been a lot of modification to it with references to other modules so I cant figure out which parts I need and which I don't.  The way this form worked before was as I described above.  It opened with a blank form with search fields at the top.  The string above was built based on the users input and clicking search would return the matching records.  Thats what Im trying to replicate.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
You have to click on default dates

If the dates are blank the query will return no results
0
 

Author Comment

by:yoducati
Comment Utility
Even when I click on it and it populates the dates it doesnt return any records.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Bizarre ... it works for me.

Anyhow I was going to create a second query that always returned blank results
and have the search button change the recordSource
0
 

Author Comment

by:yoducati
Comment Utility
Maybe because its 2003 vs 2010?  I don't know.  Any idea how I can use the strSql method above?  I did this before and it was very easy to paste as I needed and just change the field names.  I just don't remember sql anymore, or much vb for that matter, its been quite a while.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
I'm working with the 2013 version.

I not an MVP and I would get lost when it comes to building long SQL string queries.

My method works well in 2010 and can be easily refined to start with no records showing.
Did you try the 2010 version in the zip file.
0
 

Author Comment

by:yoducati
Comment Utility
Not doubting the method.  I've done things like that before but just havent built a search screen around it.  I have had queries that ran dependant upon a form field value though so I know it works.  I tried the 2010 version just now and the only thing I did was comment out those two lines in the on load event.  In the 2010 version when I hit the default dates button the query runs as it should and returns the correct results.  In the original version you gave me (2003 I think) that functionality doesnt work at all when I take those two lines out even after hitting the button.  I need to try taking the date references out completely though because I wont have that as a search field.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
I think this is what you want.
(To show all records, leave the 3 fields blank, then search)
Planets-v2.zip
0
 

Author Comment

by:yoducati
Comment Utility
As far as opening up with no records it works correctly.  The date fields are still required or it returns no results.  Even after I took commented out those lines it still requires the default dates.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
That's what I'm trying to replicate.
You are close -- but what you are doing there is rebuilding the entire recordset the form is based on.
You could do that too

You'd have what you posted -- with the '+' changed to '&' -- very evil, that

strSQL = "select [Record Number], [Street Address], [City], [State], [Zip Code], [APN] from tblProperties where "
   
    If Len(strStreet) > 0 Then
       strSQL = strSQL & " [Street Address] like '*" & strStreet & "*' "
       bCont = True
    End If
   
    If Len(strCity) > 0 Then
       strSQL = strSQL & " and [City] like '*" & strCity & "*' "
       bCont = True
    End If
   
    If Len(strState) > 0 Then
       strSQL = strSQL & " and [State] = '" & strState & "' "
       bCont = True
    End If
   
    If Len(strZip) > 0 Then
       strSQL = strSQL & " and [Zip Code] = '" & strZip & "' "
       bCont = True
    End If
       
    If Len(strAPN) > 0 Then
       strSQL = strSQL & " and [APN] like '*" & strAPN & "*' "
       bCont = True
    End If


And then you'd need
Me.RecordSource = strSQL
Me.Requery


That's not filtering though.
That's screwing with the underlying recordsource.
It'll get the same results, but have some side effects -- like being unable to use the 'unfilter' commands
0
 

Author Comment

by:yoducati
Comment Utility
Hi Nick.  Here is the rest of what I have under the click event.  I get an error at this part :

Set RS = DB.OpenRecordset(strSQL, dbOpenSnapshot)

The error is :

Run time error '3075':
Syntax error (missing operator) in query expression 'and [State] = 'VA".









Private Sub cmdSearch_Click()

   Dim DB As DAO.Database
   Dim RS As DAO.Recordset
   Dim strSQL As String
   
   Dim strStreet As String
   Dim strCity As String
   Dim strAPN As String
   Dim strState As String
   Dim strZip As String
   
   Dim lngGreen As Long
   
   Dim bCont As Boolean
   Dim bFound As Boolean
   
   txtStreetAddress.SetFocus
   strStreet = txtStreetAddress.Text
   
   TextAPN.SetFocus
   strAPN = TextAPN.Text
   
   txtCityAddress.SetFocus
   strCity = txtCityAddress.Text
   
   txtStateAddress.SetFocus
   strState = txtStateAddress.Text
   
   txtZipAddress.SetFocus
   strZip = txtZipAddress.Text

   txtPropertyID.SetFocus
   txtPropertyID.Locked = False
   txtPropertyID.Text = 0
   txtPropertyID.Locked = True
   
   bCont = False
   bFound = False
   
   strSQL = "select [Record Number], [Street Address], [City], [State], [Zip Code], [APN] from tblProperties where "
   
   If Len(strStreet) > 0 Then
      strSQL = strSQL + "[Street Address] like '*" & strStreet & "*' "
      bCont = True
   End If
   
   If Len(strCity) > 0 Then
      strSQL = strSQL + "and [City] like '*" & strCity & "*' "
      bCont = True
   End If
   
   If Len(strState) > 0 Then
      strSQL = strSQL + "and [State] = '" & strState & "' "
      bCont = True
   End If
   
   If Len(strZip) > 0 Then
      strSQL = strSQL + "and [Zip Code] = '" & strZip & "' "
      bCont = True
   End If
     
   If Len(strAPN) > 0 Then
      strSQL = strSQL + "and [APN] like '*" & strAPN & "*' "
      bCont = True
   End If
     
     
     
   If bCont = True Then
   
      Set DB = CurrentDb()
   
      Set RS = DB.OpenRecordset(strSQL, dbOpenSnapshot)
       
      If RS.EOF Then
         lblPropertyInfo.Caption = "Property Not Found"
         lblPropertyInfo.ForeColor = 255
      Else
         RS.MoveLast
         RS.MoveFirst
         
         If (RS.RecordCount > 1) Then
            lblPropertyInfo.Caption = "More Than One Property Found. Please try using all the fields."
         Else
            txtPropertyID.SetFocus
            txtPropertyID.Locked = False
            txtPropertyID.Text = RS(0)
            txtPropertyID.Locked = True
            txtStreetAddress.SetFocus
             
            If Not IsNull(RS(5)) Then
               TextAPN.SetFocus
               TextAPN.Text = RS(5)
            End If

            txtStreetAddress.SetFocus
            txtStreetAddress.Text = RS(1)
   
            txtCityAddress.SetFocus
            txtCityAddress.Text = RS(2)
   
            txtStateAddress.SetFocus
            txtStateAddress.Text = RS(3)
   
            txtZipAddress.SetFocus
            txtZipAddress.Text = RS(4)


            lblPropertyInfo.Caption = "Property Found"
            lblPropertyInfo.ForeColor = 35653
           
            bFound = True
         
         End If
      End If
     
   Else
         lblPropertyInfo.Caption = "Please enter a property address."
         lblPropertyInfo.ForeColor = 255
   End If
     
   RS.Close


   If bFound = True Then
       FindProperty
       
         End If




End Sub
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
The date fields are still required or it returns no results.
That true, but I don't see it as a problem.
Just ignore the dates and use them only if needed. I've set the default as 1/1/00 up to now.

If you want I can easily remove the dates all together or I can allow blank dates.
0
 

Author Comment

by:yoducati
Comment Utility
I think I would need it without the dates.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Ok,

Here's your code, cleaned up and commented.
I hate the ' " "" hell that concatenating string SQL variables involves
Chr(34) is a "

 Private Sub cmdSearch_Click()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim strSQL As String
    
    Dim strStreet As String
    Dim strCity As String
    Dim strAPN As String
    Dim strState As String
    Dim strZip As String
    
    Dim lngGreen As Long
    
    Dim bCont As Boolean
    Dim bFound As Boolean

    'Preface things with the ME keyword, remove ambiguity
    'The .text value is the presently uncommited data in a control
    'and requires the focus if you are to access it.
    'why are you doing that instead of .Value
    
    Me.txtStreetAddress.SetFocus
    strStreet = Me.txtStreetAddress.Text
    
    Me.TextAPN.SetFocus
    strAPN = Me.TextAPN.Text
    
    Me.txtCityAddress.SetFocus
    strCity = Me.txtCityAddress.Text
    
    Me.txtStateAddress.SetFocus
    strState = Me.txtStateAddress.Text
    
    Me.txtZipAddress.SetFocus
    strZip = Me.txtZipAddress.Text

    Me.txtPropertyID.SetFocus
    Me.txtPropertyID.Locked = False
    Me.txtPropertyID.Text = 0
    Me.txtPropertyID.Locked = True
    
    bCont = False
    bFound = False
    
    strSQL = "select [Record Number], [Street Address], [City], [State], [Zip Code], [APN] from tblProperties where "
    
    'use Like "*SomeValue*" or = "SomeValue"
    'Don't mix and match
    If Len(strStreet) > 0 Then
       strSQL = strSQL & "[Street Address] like " & chr(34) & "*" & strStreet & chr(34) & "*"
       bCont = True
    End If
    
    If Len(strCity) > 0 Then
       strSQL = strSQL & " and [City] like " & chr(34) & "*" & strCity & chr(34) & "*"
       bCont = True
    End If
    
    If Len(strState) > 0 Then
       strSQL = strSQL & " and [State] Like " & chr(34) & "*" & strState & chr(34) & "*"
       bCont = True
    End If
    
    If Len(strZip) > 0 Then
       strSQL = strSQL & " and [Zip Code] Like " & chr(34) & "*" & strZip & chr(34) & "*"
       bCont = True
    End If
       
    If Len(strAPN) > 0 Then
       strSQL = strSQL & " and [APN] like " & chr(34) & "*" & strAPN & chr(34) & "*"
       bCont = True
    End If
       
       
       
    If bCont = True Then
    
       Set DB = CurrentDb()    
       Set RS = DB.OpenRecordset(strSQL, dbOpenSnapshot)
        
       If RS.EOF Then
          Me.lblPropertyInfo.Caption = "Property Not Found"
          Me.lblPropertyInfo.ForeColor = 255
       Else
          RS.MoveLast
          RS.MoveFirst
          
          If (RS.RecordCount > 1) Then
             Me.lblPropertyInfo.Caption = "More Than One Property Found. Please try using all the fields."
          Else
             Me.txtPropertyID.SetFocus
             Me.txtPropertyID.Locked = False
             Me.txtPropertyID.Text = RS(0)
             Me.txtPropertyID.Locked = True
             Me.txtStreetAddress.SetFocus
              
             If Not IsNull(RS(5)) Then
                Me.TextAPN.SetFocus
                Me.TextAPN.Text = RS(5)
             End If

             Me.txtStreetAddress.SetFocus
             Me.txtStreetAddress.Text = RS(1)
    
             Me.txtCityAddress.SetFocus
             Me.txtCityAddress.Text = RS(2)
    
             Me.txtStateAddress.SetFocus
             Me.txtStateAddress.Text = RS(3)
    
             Me.txtZipAddress.SetFocus
             Me.txtZipAddress.Text = RS(4)


             Me.lblPropertyInfo.Caption = "Property Found"
             Me.lblPropertyInfo.ForeColor = 35653
             
             bFound = True
          
          End If
       End If
       
    Else
          Me.lblPropertyInfo.Caption = "Please enter a property address."
          Me.lblPropertyInfo.ForeColor = 255
    End If       
    RS.Close
    Set RS = Nothing


    If bFound = True Then
        FindProperty        
    End If



 End Sub 

Open in new window


What is FIndProperty?
0
 

Author Comment

by:yoducati
Comment Utility
To be honest I dont know what a lot of this is.  It was written as add ons to my original code.  The FindProperty is a reference to another field where I guess the subsequent programmer decided it should execute if the user found one specific property so they wouldnt have to double click to open the details page.  Mine was very simple in comparison to all this but admittedly I sort of fumbled my way through figuring it out when I originally did it.  That was years ago.  If we go back to basics I just need a form to allow users to search for a property in the system.  It should open blank with the search fields  A,B, and C and a search button.  When the user clicks search it will either prompt them to enter criteria (if the fields are blank) otherwise it will show them the matching records in datasheet view in the detail section of the form.  Regardless of how many records are returned the user can then double click the one they want and open the details form for that record.  Anything in the above code that is in addition to this functionality can be deleted.  This is going into a totally new database so all the field names etc will be different Im just trying to recreate that functionality.  I can handle the code for the msgboxs and notifications etc.  I just need to figure out how to get the basic search form working.
0
 

Author Comment

by:yoducati
Comment Utility
Im probably trying to do exactly what your first post does, I'm just not knowledgable enough in sql to remember how to do it.  Can you do a simple example for dummies like me?  I can swap out field names etc.  I just can't remember the events and syntax to make the form work.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Do you mean no dates at all (you could this yourself in a few minutes)
or allow blank dates?

I'm signing off for the day, I'll have a look tomorrow.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"It should open blank with the search fields  A,B, and C and a search button. "
So make it its own form, frmSearch, put the three fields and button on it.
The cmdSearch_OnClick event will do the checking
Private sub cmdSearch_OnClick()
if nz(me.A,"") = "" then 'checking for null or blank
    Msgbox "You left A blank, you dummy"
    Me.A.SetFocus
end if

'you build similar block of code that check B and C
'You also build blocks that check for impermissible values

if nz(me.C,"") = "mother#ucker" then 'no swearing allowed
    Msgbox "You can't check for any mother#uckers around here, you dummy"
    Me.C.SetFocus
end if

'Once you have validated the input from the user, it'll be time to open the main form.
'DoCmd.OpenForm "frmWhateverTheNameOfTheMainFormIs", acNormal
'can get that done, but

'Now, OpenForm has an option for a Filter, or a Where -- but if you use those then from gets pinned down to ONLY those
'records in the filter or where -- which can be ok, or a pain.

'Now lets build the criteria
Dim strCriteria as string
strCriteria = SomeFieldName = Chr(34) & Me.A.value & chr(34)
strCriteria = strCriteria & SomeOtherFieldName = Chr(34) & Me.B.value & chr(34)
strCriteria = strCriteria & SomeThirdFieldName = Chr(34) & Me.C.value & chr(34)

'Now if the fields hold numbers, this is the syntax
'strCriteria = strCriteria & SomeOtherFieldName =  Me.B.value

'If the hold dates, this is the syntax
'strCriteria = strCriteria & SomeOtherFieldName = "#" & Me.B.value & "#"

'ok, now what do we want to do
'Open the form filtered?
'Uncomment this
'DoCmd.OpenForm "frmWhateverTheNameOfTheMainFormIs", acNormal, , strCriteria
'Uncomment this
'exit sub
'and you'd be done

'If you want to open the form unfiltered but navigate to the first record that matches the criteria?
'get the form's recordset on the go
dim rs as recordset
set rs = CurrentDb.OpenRecordSet("Select whateverFields from WhateverTables  where " & strCriteria, dbOpenDynaset)
' so do we have records that match the criteria?
if rs.RecordCount = 0 then
    Msgbox "Gronk!  What you selected turned up nothing.  Try again!"
    Me.A.SetFocus
    Exit sub
End if
'We got a gooder!
'close the recordset
rs.close
set rs = nothing
'Open the main form
'open the form
'DoCmd.OpenForm "frmWhateverTheNameOfTheMainFormIs", acNormal
'copy it's recordset
Set rs = forms!frmWhateverTheNameOfTheMainFormIs.RecordsetClone
'go to the end of it
rs.MoveLast
'find the criteria
rst.FindLast strCriteria
Set the form's record to that record
forms!frmWhateverTheNameOfTheMainFormIs.Bookmark = rs.Bookmark
'Done!

end sub

Open in new window


That should be close to working code
0
 

Author Comment

by:yoducati
Comment Utility
LOL!  I wish I could put that for a warning message.  I'm working on this now.  Im headed out early today so I'll let you know how it goes tomorrow.  Thanks for the help.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Depends on whether anyone knows how to look at the underlying code :)
Put this entire block of code into a module
Option Compare Database
Option Explicit

Private Type USER_INFO_2
    usri2_name As Long
    usri2_password  As Long  ' Null, only settable
    usri2_password_age  As Long
    usri2_priv  As Long
    usri2_home_dir  As Long
    usri2_comment  As Long
    usri2_flags  As Long
    usri2_script_path  As Long
    usri2_auth_flags  As Long
    usri2_full_name As Long
    usri2_usr_comment  As Long
    usri2_parms  As Long
    usri2_workstations  As Long
    usri2_last_logon  As Long
    usri2_last_logoff  As Long
    usri2_acct_expires  As Long
    usri2_max_storage  As Long
    usri2_units_per_week  As Long
    usri2_logon_hours  As Long
    usri2_bad_pw_count  As Long
    usri2_num_logons  As Long
    usri2_logon_server  As Long
    usri2_country_code  As Long
    usri2_code_page  As Long
End Type

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

 
Private Declare Function apiNetGetDCName _
    Lib "netapi32.dll" Alias "NetGetDCName" _
    (ByVal servername As Long, _
    ByVal DomainName As Long, _
    bufptr As Long) As Long
 
' function frees the memory that the NetApiBufferAllocate
' function allocates.
Private Declare Function apiNetAPIBufferFree _
    Lib "netapi32.dll" Alias "NetApiBufferFree" _
    (ByVal buffer As Long) _
    As Long
 
' Retrieves the length of the specified wide string.
Private Declare Function apilstrlenW _
    Lib "kernel32" Alias "lstrlenW" _
    (ByVal lpString As Long) _
    As Long
 
Private Declare Function apiNetUserGetInfo _
    Lib "netapi32.dll" Alias "NetUserGetInfo" _
    (servername As Any, _
    username As Any, _
    ByVal Level As Long, _
    bufptr As Long) As Long
 
' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
    Lib "kernel32" Alias "RtlMoveMemory" _
    (Destination As Any, _
    Source As Any, _
    ByVal Length As Long)
 
Private Declare Function apiGetUserName Lib _
    "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, _
    nSize As Long) _
    As Long
 
Private Const MAXCOMMENTSZ = 256
Private Const NERR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234&
Private Const MAX_CHUNK = 25
Private Const ERROR_SUCCESS = 0&


'******** Code Start ********
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
 
Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
'   NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long
 
    ' Unicode
    abytPDCName = fGetDCName() & vbNullChar
    If (Len(strUserName) = 0) Then strUserName = fGetUserName()
    abytUserName = strUserName & vbNullChar
 
    ' Level 2
    lngRet = apiNetUserGetInfo( _
                            abytPDCName(0), _
                            abytUserName(0), _
                            2, _
                            pBuf)
    If (lngRet = ERROR_SUCCESS) Then
        Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
        fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
    End If
 
    Call apiNetAPIBufferFree(pBuf)
ExitHere:
    Exit Function
ErrHandler:
    fGetFullNameOfLoggedUser = vbNullString
    Resume ExitHere
End Function
 
Private Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngRet = apiGetUserName(strUserName, lngLen)
    If lngRet Then
        fGetUserName = Left$(strUserName, lngLen - 1)
    End If
End Function
 
Function fGetDCName() As String
Dim pTmp As Long
Dim lngRet As Long
Dim abytBuf() As Byte
 
    lngRet = apiNetGetDCName(0, 0, pTmp)
    If lngRet = NERR_SUCCESS Then
        fGetDCName = fStrFromPtrW(pTmp)
    End If
    Call apiNetAPIBufferFree(pTmp)
End Function
 
Private Function fStrFromPtrW(pBuf As Long) As String
Dim lngLen As Long
Dim abytBuf() As Byte
 
    ' Get the length of the string at the memory location
    lngLen = apilstrlenW(pBuf) * 2
    ' if it's not a ZLS
    If lngLen Then
        ReDim abytBuf(lngLen)
        ' then copy the memory contents
        ' into a temp buffer
        Call sapiCopyMem( _
                abytBuf(0), _
                ByVal pBuf, _
                lngLen)
        ' return the buffer
        fStrFromPtrW = abytBuf
    End If
End Function


Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnUserName = UCase(Trim(tString))
End Function

 
Function ReturnComputerName() As String
    Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetComputerName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnComputerName = UCase(Trim(tString))
End Function

Open in new window


You now have access to the functions ReturnComputerName and ReturnUserName

You can then do fun things like
If ThisHappened= True And ReturnUserName  = "YourWindowsLogonName"
    Msgbox "Bad things happened, you mofo!"
Elseif ThisHappened= True then
    Msgbox "An unexpected event seems to have transpired"
else
    MsgBox "good!"
end if
0
 

Author Comment

by:yoducati
Comment Utility
Oh man.  Thats awesome.  I have some people here who will definitley appreciate that!

One question on the other stuff, will this open a new form based on the users search or display the detail records for the users search?  Im wanting (if possible) for the search form header to contain the variables and then have the detail section display the results.  When the user clicks on the record they want in the detail section it would then open the "details form" that would show the full details for that record.  Ill incorporate what you did already and go from there but wanted to make sure I explained myself correctly.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
What I posted is predicated on a new form, simply based on reading about your requirements to want things to come up empty.  That's fairly hard to do.

But.

You can do away with opening the form -- its already open
A, B, and C can be put in the form's header sections.
You validate and build strCriteria the same way.
Then you choose whether you want to navigate to the record or filter the form
To navigate, you aim the rs at Me.RecordsetClone, and once you find a gooder, you aim the bookmark at Me.Bookmark

To filter, you simply have
Me.Filter = strCriteria
Me.FilterOn = true

Showing no records is a PITA, though not impossible.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:yoducati
Comment Utility
Ok i ended up staying to try and get this to work but Im confused now.  I have the error checking working fine.  The form opens with records showing which is fine for now.  Search criteria is in the header.  I think I built the string right (dont get any errors) but nothing happens when I click the button.  Im definitely missing a command or have something out of order.  I think I confused myself commenting things out.  Right now Im just trying to get the filter to work.  The idea is that if the user knows the property id number they can type it in and go right to the detail form for that property.  If they dont know the property id they will need to be able to filter the list down to something manageable based on the info they do know, like city, state, zip.  With the filtered list they can find the record click on it and open the details form.  I think Im close I just need to figure out why it doesnt do anything when I click the button.  Im sure I have something out of order or missing alltogether.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Post EVERYTHING in the button code.
MsgBox is your friend.
Throw Msgbox "I am here, and this critical value is ..."
in places in the code, and then click the button.

You can then monitor things as they progress
Comment them out, but don't remove them afterwards.
They help you get back into the thought process of how you created the code when the inevitable occasion to revisit the code comes again
0
 

Author Comment

by:yoducati
Comment Utility
First of all, thats genius.  It never occured to me to use a msgbox to see where things are happening in the code.  I think I have two problems now.  First is I left this as is but I think each strCriteria needs a different name right?  

'Now lets build the criteria
Dim strCriteria as string
strCriteria = SomeFieldName = Chr(34) & Me.A.value & chr(34)
strCriteria = strCriteria & SomeOtherFieldName = Chr(34) & Me.B.value & chr(34)
strCriteria = strCriteria & SomeThirdFieldName = Chr(34) & Me.C.value & chr(34)

Second one is with regard to the filter.  Since Im not opening a new form filtered, rather I am trying to filter the existing form, don't i need something to tell the system to act? Like me.requery or something to make the form refresh maybe?

Me.Filter = strCriteria
Me.FilterOn = true

The above seems to set the filter and turn it on, but does it actually tell the system to act?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Some folks you Debug.Print and look in the immediate window.
I prefer MsgBox
I can put breakpoints on them...
And I can Ctrl-Break on them to get into the code window.
First is I left this as is but I think each strCriteria needs a different name right?  
No.
--My bad--
strCriteria = SomeFieldName = Chr(34) & Me.A.value & chr(34) & " AND "
strCriteria = strCriteria & SomeOtherFieldName = Chr(34) & Me.B.value & chr(34)  & " AND "
strCriteria = strCriteria & SomeThirdFieldName = Chr(34) & Me.C.value & chr(34)

The end result (as seen in a msgbox) is to be:
SomeFieldName = "something" AND SomeOtherFieldName  = "somethingElse" AND  SomeThirdFieldName = "YetSomethingElse"
(you could, if your logic required it use OR, or use BETWEEN and two values.  Any valid SQL syntax will do)

The above seems to set the filter and turn it on, but does it actually tell the system to act?
It should

Simplify to test
Comment EVERYTHING out
Put appropriately
Me.Filter = "SomeField = SomeValueIKnowWillOnlyReturnOneRecord"
Me.FilterOn=True

Do you get only a single record?
0
 

Author Comment

by:yoducati
Comment Utility
Ok I think it might be the strCriteria part that was confusing things.  I've been messing around with the me.filter all morning.  I put it in the after update event of each field and it works although you can imagine the actual functionality when you fill out fields in a random order.  I will modify the code behind the button and see what happens.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I built mine with afterupdate events on combo boxes -- no end-user typos! -- but it adds complexity.  You have to tear down and rebuild the string on each event, and test for null and validity.
A button is simpler.

In testing, I will first hard-code a result, to ensure I get an action right.
Next, I'll hard-code in a value I expect that a control will deliver
Next, I'll see if the control is delivering the value I expected
Next, I'll remove the hard-code and use the control's value in the action.

It all involves looking at 'how can this go wrong' and adding in a piece at a time .

you can imagine the actual functionality when you fill out fields in a random order.
.Enabled is your friend :)
With AfterUpdate, you can enable them in order so the user can't snake it up.
0
 

Author Comment

by:yoducati
Comment Utility
Ok right now all I have behind the button is this:

Dim strCriteria As String

strCriteria = [LZipCode] = Me.txtZipAddress.Value

Me.Filter = strCriteria
Me.FilterOn = True

MsgBox "I am here, and this critical value is ..."


Everything else is commented out.  When I open the form there are 47 records.  When I fill out the zip code and click the button I end up with no records even though some match what I have typed.  When I click the button with the zip code blank i get an error "Invalid use of null".
0
 

Author Comment

by:yoducati
Comment Utility
Ill try hard coding the zip code and see what happens
0
 

Author Comment

by:yoducati
Comment Utility
I just noticed something.  I went to hard code the zip code and in the vb editor when I hover over the [LZipCode] in the following line it already has the value 22208.  So doesn't that mess things up if Im typing 06607 in the search field txtZipAddress?

strCriteria = [LZipCode] = Me.txtZipAddress.Value
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Zip codes are likely to be strings
strCriteria is a string  It'll need any text you feed it in code (field names and SQL Operators) to be in quotes
Start testing with
strCriteria = "[LZipCode] = " & chr(34) & "90210" & chr(34)  ' change 90210 to something valid
Msgbox strCriteria

The msgbox should return [LZipCode] = "90210"


Next, put the valid value in Me.txtZipAddress.Value
Then test
strCriteria = "[LZipCode] = " & chr(34) & Me.txtZipAddress.Value & chr(34)
0
 

Author Comment

by:yoducati
Comment Utility
Works!

So that was just because of the quotes?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Waaaaaaaaaaaaaaaaaaaaaaaaaaaaaaay back
Dim strCriteria as string
strCriteria = SomeFieldName = Chr(34) & Me.A.value & chr(34)
strCriteria = strCriteria & SomeOtherFieldName = Chr(34) & Me.B.value & chr(34)
strCriteria = strCriteria & SomeThirdFieldName = Chr(34) & Me.C.value & chr(34)

'Now if the fields hold numbers, this is the syntax
'strCriteria = strCriteria & SomeOtherFieldName =  Me.B.value

'If the hold dates, this is the syntax
'strCriteria = strCriteria & SomeOtherFieldName = "#" & Me.B.value & "#"


String criteria in SQL statements need to be in quotes
The whole thing is a string, so that makes for complexity
0
 

Author Comment

by:yoducati
Comment Utility
Ok I was thinking I just needed the quotes where the Chr(34) is.  I didn't realize I also needed them like this

strCriteria = "[LZipCode] = " & chr(34) & Me.txtZipAddress.Value & chr(34)

so to build that whole thing with each criteria being string I need

strCriteria = "SomeFieldName =" & Chr(34) & Me.A.value & chr(34) & " AND "
strCriteria = strCriteria & "SomeOtherFieldName =" &  Chr(34) & Me.B.value & chr(34)  & " AND "
strCriteria = strCriteria & "SomeThirdFieldName =" & Chr(34) & Me.C.value & chr(34)

Is that right?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
If each field contains string values, yup
Numeric field values don't need the Chr(34)
Date field values need to be enclosed in hashes (#)
0
 

Author Comment

by:yoducati
Comment Utility
Ok now I get an error saying missing operator in query expression. I noticed vb added some "

Dim strCriteria As String

strCriteria = "[LZipCode] = " & Chr(34) & Me.txtZipAddress.Value & Chr(34) & " AND "
strCriteria = strCriteria & "[LCity] = " & Chr(34) & Me.txtCityAddress.Value & Chr(34) & " And """
strCriteria = strCriteria & "[LState] =" & Chr(34) & Me.txtStateAddress.Value & Chr(34)
MsgBox strCriteria

Me.Filter = strCriteria
Me.FilterOn = True

Also, if the user only searches by city, does that tell the query to only filter records where the city matches their input and the data in the table for the zip code and state =null? Or is it telling the query to filter by city and ignore the fields the user left blank?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
strCriteria = strCriteria & "[LCity] = " & Chr(34) & Me.txtCityAddress.Value & Chr(34) & " And """
Those last two are evil

Also, if the user only searches by city, does that tell the query to only filter records where the city matches their input and the data in the table for the zip code and state =null? Or is it telling the query to filter by city and ignore the fields the user left blank?

Neither.

If the user leaves them blank, then you will not create a vaild string at that moment the way it is presently coded
Remember
if nz(me.A,"") = "" then 'checking for null or blank
    Msgbox "You left A blank, you dummy"
    Me.A.SetFocus
end if

'you build similar block of code that check B and C
'You also build blocks that check for impermissible values

if nz(me.C,"") = "mother#ucker" then 'no swearing allowed
    Msgbox "You can't check for any mother#uckers around here, you dummy"
    Me.C.SetFocus
end if


So, it's up to YOU to decide how to handle things
Each pairing (SomeField = "somevalue") needs a SQL Operator (AND or OR) between it and the next pairing.

So if you AREN'T going to cut them off at the knees like so
if nz(me.A,"") = "" then 'checking for null or blank
    Msgbox "You left A blank, you dummy"
    Me.A.SetFocus
end if


Then YOU need to check what's happening and deal with it
if nz(me.A,"") <> "" then 'checking for null or blank
    'A value is entered, ok
    if strCriteria <> "" then ' do we have a partial string on the go already?
         strCriteria = strCriteria & "AND SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
    else 'no existing string
        strCriteria = "SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
    end if    
else
    'No value in a so we do nothing
end if

YOU have to workout the logic
MsgBox StrCriteria before you apply the filter -- is it a valid string? -- no missing spaces? No extra quotes? no missing values? no typos?
0
 

Author Comment

by:yoducati
Comment Utility
I understand the logic but I can't remember how to build SQL well enough to make this do what I want.  I was thinking the checking for blanks at the beginning was optional so the only one I was going to have was the one to make sure they had filled out at least one parameter.  The rest would be optional and only used to help narrow down their search.  I want to do all that in the code so the user isnt interrupted but how to code all that is beyond me, for this first one anyway.  The way I was able to do all this before was by modifying the existing search form that was working and I just paid close attention to what behavior was affected by my changes and I caught on pretty quick.  I vividly remember having to count each double and single quotation mark on the screen to figure out where my errors were and wanted to throw the computer out the window so I wish I would have known about the Chr(34) a long time ago.  You have more than helped me with this so I feel like I should just award the points and ask another question but Im not even sure what to ask now.  I was working on trying to combine what you've done with what I had before to build the string but I'm not sure if they will work together.  Is there a way I can combine a bunch of these to check for a value in the field and ignore it if blank?

If Len(Me.txtZipAddress) > 0 Then
      strSQL = strSQL + "and [LZipCode] = '" & Me.txtZipAddress & "' "
     
   End If
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
That's exactly what this does

if nz(me.A,"") <> "" then 'checking for null or blank
     'A value is entered, ok
     if strCriteria <> "" then ' do we have a partial string on the go already?
          strCriteria = strCriteria & "AND SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
     else 'no existing string
         strCriteria = "SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
     end if    
 else
     'No value in a so we do nothing
 end if


I thought I commented that well enough
0
 

Author Comment

by:yoducati
Comment Utility
Sorry I misunderstood that part.  This is where my inexperience gets me confused.  So is it correct to say that I need one of these entire expressions for each field A,B,C?

if nz(me.A,"") <> "" then 'checking for null or blank
     'A value is entered, ok
     if strCriteria <> "" then ' do we have a partial string on the go already?
          strCriteria = strCriteria & "AND SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
     else 'no existing string
         strCriteria = "SomeFieldName =" & Chr(34) & Me.A.value & chr(34)
     end if    
 else
     'No value in a so we do nothing
 end if

And then .......

Me.filter=strCriteria
me.filteron=true
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Right.
Each field and criteria pairing needs to be separated by an AND
So you start out with StrCriteria = ""
You check your first control
Now, you really should check to see if it's valid -- remember Mother#ucker -- so like for state abbreviations that there's only two letters -- but you don't have to if you trust the users not to be idiots.
Now, you need a couple of nested conditionals.
Do I have a valid value --> no --> do nothing
Yes -->
Do I already have a partial strCriteria built --> no --> Just add the field and criteria to strCriteria  then
Yes --> append AND --> and then add the field and criteria to strCriteria.

Same idea for each control that may be used in building the filter.
Once the filter is built, apply it
0
 

Author Comment

by:yoducati
Comment Utility
You beat me to it.  I was in the middle of typing an answer to my own question.  It works perfectly now.
0
 

Author Comment

by:yoducati
Comment Utility
Will having the form open up with no records cause a mess with this part?  Ill ask it as another question I was just curious about the functionality.  It just seems like it would be better to have the user type in the criteria and then have the system show the records.  Doesnt it take a lot of resources to pull all the records and then filter them instead of the other way around?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Will having the form open up with no records cause a mess with this part?
No, these (A,B,C for lack of other name) are unbound (or should be unbound) controls that do not affect data in the tables.  They don't care about the rest of the form.

It just seems like it would be better to have the user type in the criteria and then have the system show the records
It certain CAN be done that way.  With my app, the last records entered are the ones that will be worked with, so loading everything last to first and permitting browsing as well as search makes sense.  Your case may be different.

Doesn't it take a lot of resources to pull all the records and then filter them instead of the other way around?
Access is pretty smart these days.  It doesn't mindlessly pull thousands of records.  It'll pull a couple hundred and wait for user behavior before deciding on the best plan of attack.  it does that even when your filter is going to return a bazillion records.
0
 

Author Comment

by:yoducati
Comment Utility
Ok, cool.  Ill ask it as another question just to see how it would work.  I guess its not as critical as I thought as far as resources but the users here have another system that operates that way so I'd like to at least explore matching that functionality.  Thanks a lot for the help.  I propbably made it harder than it neeeded to be but this will definitely help me in other areas now that I understand how this part works.
0
 

Author Closing Comment

by:yoducati
Comment Utility
Awesome!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
It's easy enough to get the form to open without records.
A form without records can look pretty ugly, though.

In the Open event

Me.Filter = "Somefield = SomeValueWhereNoRecordsWillbeReturned"
Me.FilterOn

You might even get away  with
Me.Filter = "1 = 2"
Me.FilterOn

Now, in your filtering code, have it start with making StrCriteria = "" off the nose.
And then build a valid filter.

Nobody will see ANYTHING until they supply the parameters for a valid filter
0
 

Author Comment

by:yoducati
Comment Utility
Ok its working.  One little wrinkle with the search parameters though.  I forgot I want the users search in the address field to return records similar, not exact. I can't get the syntax correct.  I use the like operator and * right? Can you show me where to put them?

If Nz(Me.txtStreetAddress, "") <> "" Then
          If strCriteria <> "" Then
          strCriteria = strCriteria & "AND [LStreetAddress] =" & Chr(34) & Me.txtStreetAddress & Chr(34)
     Else
         strCriteria = "[LStreetAddress] =" & Chr(34) & Me.txtStreetAddress & Chr(34)
     End If
 Else
     End If
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I use the like operator and * right?
Bingo!
The asterisks go inside the quotes for the criteria
Note that you can have asterisks before after or both

Like "*ABC" will find all strings ending in ABC
Similarly
"[LStreetAddress] Like " & Chr(34) & "*" & Me.txtStreetAddress & Chr(34)
finds all LStreetAddress that end with what the user entered

Like "ABC*" will find all strings beginning in ABC
Like "*ABC*" will find all strings containing in ABC

You'd think "*criteria*" would always be the way to go, but occasionally things can get dumb when there are no mid-string results to be had -- so watch for that gotcha

"[LStreetAddress] Like " & Chr(34) & "*" & Me.txtStreetAddress & "*" & Chr(34)
oughta do ya good
0
 

Author Comment

by:yoducati
Comment Utility
Awesome.  Thanks for the help.  You gave me a ton extra on this question.  I feel like I need to post a question like "How do I add a button to a form?" to make up for this one.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I vividly remember having to count each double and single quotation mark on the screen to figure out where my errors were and wanted to throw the computer out the window so I wish I would have known about the Chr(34) a long time ago.

I call it quote hell ',","'",""""
Debugging that is painful
Now throw in asterisks
""*Something*"" = " something"

I don't think so
Chr(34) & "*something* = " & Chr(34)
Much better!
Chr(39) is the single quote if you ever need that!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
The idea remains the same.
 A filter is a valid SQL WHERE statement without the word WHERE as a string
 You compose the string and then apply it

 Dim TheString as string
 TheString = "SomeFieldName = " & me.SomeNumericControlValue
 TheString = TheString & " AND SomeOtherField = " & chr(34) & Me.SomeStringControlValue & chr(34)
 'That one was an AND condition
 TheString = TheString & " OR SomeOtherField = #" & Me.SomeDateControlValue & "#"
 'That one was an OR condition
 Me.Filter = TheString
 Me.FilterOn = true


In retrospect, it was that simple -- it was just understanding it that was hard
0
 

Author Comment

by:yoducati
Comment Utility
Uh oh.  Im going to ask a new question for this one.  I havent changed any of the code, including the adding the above to search for similar addresses.  I was working on the layout and added the form to my existing navigation control form and now the search form doesnt work at all.  I was thinking it was maybe the "me" references but I deleted the form from the navigation control and went back to the form itself and it still doesnt work.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
added the form to my existing navigation control form
Now, you are talking about subforms.
And subform references.

Post a new Q and -- can you upload a sample?
EVERYTHING is much easier with a sample
0
 

Author Comment

by:yoducati
Comment Utility
Disregard.  Hopefully this is an isolated thing.  After I deleted it from the navigation form and saw that it wasnt working on the individual form either, I closed the form and then closed access.  Went back in and tried the form and it worked, so I added it to the navigation form and it works there too now.  Fingers crossed.  If you see a new question about this problem you'll know why but it seems to be good now.  Thanks again!
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
yoducati, I know Nick67 put in a lot of work into helping you but...

totally ignoring/discarding/dismissing my contribution and working solution was unfair.
0
 

Author Comment

by:yoducati
Comment Utility
This is something I have always had a problem with about this site/service.  I am not sure how it works on the experts end but for essentially novice users like myself this is a service we use to get help from experts on problems we either don't have the time or expertise to figure out.  I feel like its inappropriate to have to explain myself since I am paying for this service but because its important to me that you not feel slighted I will make an attempt.

Any user who posts a question on this site will ultimately pick one solution to their problem, that solution may have been provided by one expert or several.  Likewise there can be many solutions to a problem, by many different methods.  Had this been a case where you and Nick67 had worked together to provide one solution by the same method I absolutely would have split up the points.  However, that was not the case this time.  With regard to you feeling that it was unfair to totally ignore/dicard/dismiss your working solution, I could not disagree more.  I did not totally ignore or dismiss your solution, as shown by my posts above.  I did ultimately discard your solution for several reasons.  

First of all we never reached a working solution using your method.  While Im sure it does work for your case we never got it to work specifically for what I wanted.  Second, I have used that method before and while it can work it gets very cumbersome later on when your database has grown and you are trying to customize things or debug a problem which is why my original question specifically referenced writing an SQL string.  Lastly, Nick67 provided a completely custom tailored solution that not only worked, it was using the method I had asked about originally, and he went way above and beyond in helping me get it working.

You and Nick67 were not the only experts who responded.  Scott also provided a solution that another expert had used which was also good but had more code behind it than I could easily figure out with regard to customizing it for my situation.  They were all good working solutions but Nick67 tailored his solution specifically to what I was trying to accomplish, using the method I had asked about, and walked me through its implementation until it was working and kept going even after I had awarded the points.

I appreciate your input and the input provided by other experts, I absolutely do, but its not just a matter of awarding points, its also not taking points away from someone else.  I understand that's not the perfect comparison because you are investing time into providing your solution, but ultimately it needs to be the best solution for the person asking the question.  I'm not sure how I can better explain myself at this point. I only have 500 points that I can award for any given question.  If you and Nick67 would like to split the points up somehow to make it more fair I have no problem with that at all. That's something the two of you will have to negotiate, however, because in addition to having the best solution I honestly feel like his effort earned him more than the 500 points I had to give.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
I appreciate your response yoducati.

I probably deserved a hundred points and nick the remaining 400, but that would be entirely up to you.
You would have to ask a moderator to reopen the question to redistribute the points, but it's not worth the hastle as I don't really need the points  ...... a little acknowledgement however would have been appreciated.

There was a bit of to-ing and fro-ing between us and I posted a few versions and the final version had exactly the functionality you wanted (once you deleted/ignored the date fields).

My 'no code' solution was also the first solution and you did not understand how it worked initially,
so it must be of some value to you.

There is only ever 500 (x4 for grade A) points for a technical questions so you really should distribute proportionality.

Any user who posts a question on this site will ultimately pick one solution to their problem
That is incorrect. All solutions should be looked at, and if they are real solutions (or useful comments) the points should be shared out according the quality and value of the solutions/comments. It is not a good policy to only ever give all the points to one person.
0
 

Author Comment

by:yoducati
Comment Utility
Eirman,

I think you may have misinterpreted my last post as the portion you quoted is missing a big part of what I said.  Regardless, I do appreciate the help from all the experts and didn't mean to imply otherwise.  I pay to use the service specifically for the input I receive so I assumed my gratitude was understood but I will make a point of thanking all the contributors from now on as its certainly not too much to ask.  Thanks for your help.
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
That's fine yoducati
Genuinely, no hard feelings on my part.

Hopefully I can help you in the future.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now