Results 1 to 9 of 9
  1. #1
    HarryScofs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    16

    Compile error: Variable not defined

    I am trying to create a a form with a subform that can search through a table of Construction Subcontractors based on Company Name, POC Name, Type, Division, and Entity. Information is entered into the form and then the results are displayed in the subform. I have copied and pasted a form from a template on Office.com called Issues. The Issues database is in Access 2003 and I am working with Access 2010. I put the form and subform into my database and tried to change the neccessary field names. However, I get a "Compile error: Varible not defined" every time i try to run the search. Here is the code behind the search command button:

    Option Compare Database


    Option Explicit
    Private Sub Clear_Click()
    DoCmd.Close
    DoCmd.OpenForm "Search Issues"
    End Sub
    Private Sub Search_Click()

    strWhere = "1=1"

    ' If Division
    If Not IsNull(Me.Division) Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Subcontractors.[Divsion] = " & Me.Division & ""
    End If

    ' If Entity
    If Nz(Me.Entity) <> "" Then
    'Add it to the predicate - exact match
    strWhere = strWhere & " AND " & "Subcontractors.Entity = '" & Me.Entity & "'"
    End If

    ' If Company Name
    If Nz(Me.CompanyName) <> "" Then
    ' Add it to the predicate - match on leading characters
    strWhere = strWhere & " AND " & "Subcontractors.[Company Name] Like '*" & Me.CompanyName & "*'"
    End If

    ' If POC Name
    If Nz(Me.POCName) <> "" Then
    ' Add it to the predicate - match on leading characters
    strWhere = strWhere & " AND " & "Subcontractors.[POC Name] Like '*" & Me.POCName & "*'"
    End If

    ' If Type
    If Nz(Me.Type) <> "" Then
    ' Add it to the predicate - match on leading characters
    strWhere = strWhere & " AND " & "Subcontractors.Type Like '*" & Me.Type & "*'"
    End If

    Else
    'DoCmd.OpenForm "Browse All Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
    If Not Me.FormFooter.Visible Then
    Me.FormFooter.Visible = True
    DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
    End If
    Me.Browse_All_Issues.Form.Filter = strWhere
    Me.Browse_All_Issues.Form.FilterOn = True
    End If
    End Sub


    The Debugger goes to the Private Sub Search_Click() line. Is the problem coming from the previous database being in Access 2003?? Or do I have some other problem within my code or form?? I am a newb at access and I know very little about writing code, so any help would be greatly appreciated.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The Option Explicit you have makes sure that you declare (Dim) all variables you use.
    You have:
    strWhere = "1=1"
    without declaring strWhere. Throw in a:
    Dim strWhere As String
    before the strWhere = "1=1" and it should work

  3. #3
    HarryScofs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    16
    Yes! Works Great! You da Man with the Access plan!!

    One more thing, For some reason the search on Divisions won't work even though all of the other ones will. Also whenever I open the form, a parameters dialog box appears asking me for "Subcontractors.Division". When I enter a Division number into the dialog box I get nothing as my result.

    Is there something wrong with my code or could there be a problem with the control source in the form?

    I apologize for my incompetencies.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    A popup means Access cannot find what you're pointing to. Make sure it is spelled correctly and case sensitive.

    If it is, do a debug.print and see what the string actually comes out to then use it as a WHERE clause in a simple query just to make sure it's pulling what you want.

  5. #5
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Take the red part off:

    strWhere = strWhere & " AND " & "Subcontractors.[Divsion] = " & Me.Division & ""


    it should be:

    strWhere = strWhere & " AND " & "Subcontractors.[Divsion] = " & Me.Division

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In my experience the extra bit at the end doesn't hurt anything (though is certainly not necessary). I suspect the prompt comes from the misspelling of division (as Shabz implied):

    Subcontractors.[Divsion]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    What sucks is when the misspelling is in the table/field name. You sit there looking at perfectly good code of a great deal of time only to realize you screwed up at the start.

  8. #8
    HarryScofs is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    16
    Great! The Search Function works perfect now. Thanks for all your help!

    Another Question... I want to be able to print selected records based on a True/False field within the data named PrintLabel. I would like to be able to both export the selected records to excel and create a report.

    Right now I have a =Sum([PrintLabel]) text box named "txtCount" within the subform with a command button in the form with the following code:

    Private Sub cmdPrint_Click()
    'Send selected records to label report.

    Me.Recalc
    If Me!txtCount = 0 Then
    MsgBox "Please select a record to print.", vbOKOnly, "Error"
    Else
    DoCmd.OpenReport "rptCustomerLabels", acViewPreview
    End If
    Exit Sub

    End Sub

    However, the program cannot find the "txtCount" because it is in the subform. Also, the subform needs to be in datasheet view so I cannot use the Header to put the command button in. How can I fix this problem?

    Again, I'm a big time access newb so I would love any other suggestions on how to do this. Thanks.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Same syntax, replacing "Forms" and "Form" with "Reports" and "Report".

    Forms Refer to Form and Subform properties and controls

    Not sure about the button. I always use continuous view rather than datasheet, in part because of that type of limitation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DSum criteria using a variable that has been defined
    By beanhead0321 in forum Programming
    Replies: 5
    Last Post: 07-24-2011, 09:57 PM
  2. Application-defined or object-defined error
    By hawkins in forum Access
    Replies: 6
    Last Post: 07-01-2011, 01:57 PM
  3. Compile error. Sub of function not defined
    By plavookins in forum Reports
    Replies: 7
    Last Post: 04-22-2011, 10:15 AM
  4. MakeTable Query with Variable user defined Name
    By Dinzdale40 in forum Programming
    Replies: 1
    Last Post: 03-09-2011, 11:26 AM
  5. Replies: 23
    Last Post: 03-26-2009, 06:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums