Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    23

    Unanswered: Problem searching Records

    I am trying to search a table using any of the the following criteria from a table called tblCustomers - CFirst, CLast,ClicenseNo, CSiteNo, CEmail and CBusinessName. But when I search using any of the criteria I will get the following message. " There was a problem with search. Please close the window and try again.

    Please I need your help. What AM I doing wrong

    Here is the code that I am using.

    Option Compare Database
    Option Explicit

    Private Sub cmdClose_Click()
    ' Close me
    DoCmd.Close acForm, Me.Name
    End Sub

    Private Sub cmdOpen_Click()
    ' User wants to open a customer
    ' Hide me
    Me.Visible = False
    ' Make sure we have a customer ID
    If IsNothing(Me.CustomerID) Then Exit Sub
    ' If we were called from the cases form,
    If Me.OpenArgs = "Cases" Then
    ' It should be open,
    If Not IsFormLoaded("frmCases") Then
    ' .. but make sure.
    DoCmd.OpenForm "frmCases"
    End If
    ' Put the selected customer ID on the
    ' cases form
    Form_frmCases.txtCustomerID = Me.CustomerID
    ' Set the customer name
    Form_frmCases.txtCustomerName = Me.CustomerName
    ' Call that form's "dirty" function
    Form_frmCases.fctDirty
    ' Make sure the focus is on cases
    Form_frmCases.SetFocus
    Else
    ' Regular customer search
    ' If the customer form is open,
    If IsFormLoaded("frmCustomers") Then
    ' Call that form's check & save function
    ' - Reply is "OK" if an error and user
    ' wants to try to fix it.
    If Form_frmCustomers.fctCheckSaved <> "Ok" Then
    ' Set the customer ID
    Form_frmCustomers.txtCustomerID = Me.CustomerID
    ' .. and tell the form to load it
    Form_frmCustomers.fctReLoadRecord
    ' Put the focus there
    Form_frmCustomers.SetFocus
    End If
    Else
    ' Not open yet - just open & pass parameter
    DoCmd.OpenForm "frmCustomers", OpenArgs:=Me.CustomerID
    End If
    End If
    ' Close me
    DoCmd.Close acForm, Me.Name
    End Sub

    Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim strSQL As String, varSQLWhere As Variant, intRCount As Integer
    ' User wants to search for customers
    ' Clear the Where clause
    varSQLWhere = Null
    ' If entered a customer first name,
    If Not IsNothing(Me.txtCFirst) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = "(C.CFirst Like '" & Me.txtCFirst & "*')"
    End If
    ' If entered a customer last name,
    If Not IsNothing(Me.txtCLast) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.CLast Like '" & Me.txtCLast & "*')"
    End If
    ' If entered a Provider License number
    If Not IsNothing(Me.txtLicenseNo) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.LicenseNo Like '" & Me.txtLicenseNo & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo Like '" & Me.txtSiteNo & "*')"
    End If
    ' If entered an email address,
    If Not IsNothing(Me.txtEmail) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.Email Like '" & Me.txtEmail & "*')"
    End If
    ' If entered a business name,
    If Not IsNothing(Me.txtBusinessName) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessName Like '" & Me.txtBusinessName & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo = '" & Me.txtSiteNo & "')"
    End If
    ' If entered a business phone number,
    If Not IsNothing(Me.txtBusinessPhone) Then
    ' Create a search on business phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessPhone = '" & Me.txtBusinessPhone & "')"
    End If
    ' Must enter at least one criterion
    If IsNothing(varSQLWhere) Then
    Call CustomError("You must enter at least one search criterion.", _
    OK, "Find Customers")
    Exit Sub
    End If
    ' Build the SQL to insert customers
    strSQL = "INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, " & _
    "DefaultAddress, BusinessAddress1, BusinessCity, BusinessStateOrProvince, " & _
    "LicenseNo,TaxId, ProvNPI, ParentNPI, SiteNo ) " & _
    "SELECT C.CustomerID, C.CFirst, C.CLast, C.DefaultAddress, " & _
    "C.BusinessAddress1, C.BusinessCity, C.BusinessStateOrProvince, C.LicenseNo " & _
    "C.LicenseNo, C.TaxId, C.ProvNPI,C.ParentNPI, C.SiteNo, C.ContactPerson " & _
    "FROM tblCustomers AS C " & _
    "WHERE " & varSQLWhere
    ' Call the function to clear and load the selected customers
    If fctPlugFind(strSQL) = False Then
    ' Got an error - tell the user and exit
    Call CustomError("There was a problem with the search. " & _
    "Please close this window and try again.", , "Search Failure")
    Exit Sub
    End If
    ' Point to this database
    Set db = DBEngine(0)(0)
    ' Refresh TableDefs to get a good count
    db.TableDefs.Refresh
    ' Get the loaded record count
    intRCount = db.TableDefs("tvwCustomers").RecordCount
    ' Set up the form message
    If intRCount <> 1 Then
    Me.RecordCount = intRCount & " Records found."
    Else
    Me.RecordCount = intRCount & " Record found."
    End If
    ' Reveal the result
    Me.RecordCount.Visible = True
    ' Clear the database object
    Set db = Nothing
    ' If 10 or fewer
    If intRCount <= 10 Then
    ' Expand the form to show them all
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (intRCount * Me.Detail.Height)
    Else
    ' Show the first 10
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (10 * Me.Detail.Height)
    End If
    ' Reload my recordset
    Me.Requery
    ' Unhide the detail section
    Me.Detail.Visible = True

    End Sub

    Function fctPlugFind(strSQL As String) As Boolean
    ' Clear the tvwCustomers table and reload using
    ' criteria in the SQL string
    Dim db As DAO.Database
    ' Set an error trap
    On Error GoTo ErrPlugFind
    ' Point to this database
    Set db = DBEngine(0)(0)
    ' Clear out the table
    db.Execute "DELETE * FROM tvwCustomers", dbFailOnError
    ' Load the search rows
    db.Execute strSQL, dbFailOnError
    ' All OK - return success
    fctPlugFind = True

    PlugFindOk:
    Set db = Nothing
    Exit Function

    ErrPlugFind:
    ' Got an error - return failure
    fctPlugFind = False
    Resume PlugFindOk

    End Function

    Private Sub Form_Load()
    ' Put focus on first control in the header
    Me.txtCFirst.SetFocus
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    ' Emulate form icon for Access 2000
    SetFormIcon Me.hWnd, IconPath
    ' If called from frmCases
    If Me.OpenArgs = "Cases" Then
    ' Change the command button caption
    Me.cmdOpen.Caption = "Select"
    Else
    ' .. else make sure it has the default caption
    Me.cmdOpen.Caption = "Open"
    End If
    ' Adjust my height to show now rows
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height
    End Sub

    Private Sub Form_Resize()
    Dim intI As Integer
    ' Find out my current width
    intI = Me.InsideWidth
    ' Adjust the width of the header label
    Me.lblHeader.Width = (intI - 150)
    Me.bxHeader.Width = intI
    ' If the new width hides the close button
    If intI < (Me.txtCurrentUser.Width + Me.cmdClose.Width + 150) Then
    ' Move the button in
    Me.cmdClose.Left = (intI + 75) - Me.cmdClose.Width + Me.txtCurrentUser.Width
    Else
    ' Move the button out so it's at the right edge
    Me.cmdClose.Left = (intI - 75) - Me.cmdClose.Width
    End If
    End Sub

    Private Sub txtBusinessName_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtBusinessName_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub

    Private Sub txtCFirst_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtCFirst_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub

    Private Sub txtCLast_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtCLast_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub

    Private Sub txtEmail_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtEmail_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub

    Private Sub txtLicenseNo_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtLicenseNo_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub

    Private Sub txtSiteNo_GotFocus()
    ' Reveal the helper label info
    Me.lblSearch.Visible = True
    End Sub

    Private Sub txtSiteNo_LostFocus()
    ' Hide the helper label info
    Me.lblSearch.Visible = False
    End Sub __.____._

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Comment out all your LostFocus events and help us to help you by giving us more details on where you get the error (versus copying and pasting all the code and asking us to figure out where the error is.) A little bit more details on where, what you're doing, and exactly what error you get please (and some screen snapshots might help.)
    Last edited by pkstormy; 06-09-08 at 21:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    But Paul, don't you have Crystal Ball 1.6? ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2008
    Posts
    23

    Searching Record using Unbound Form

    I designed a form called frmCustomerFind (with text fields of criterias listed below) unbound to table tblCustomers I want to be able to search record that are stored in the table tblCustomer using any of the the following criteria CFirst, CLast,ClicenseNo, CSiteNo, CEmail and CBusinessName. Once record is found to insert the records into a table called tvwCustomers.But when I search using any of the criteria I will get the following message. " There was a problem with search. The records are there in the table, but the code below is not populating then frmCustomerFind form and tvwCustomers table.

    Please help


    Option Compare Database
    Option Explicit

    Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim strSQL As String, varSQLWhere As Variant, intRCount As Integer
    ' User wants to search for customers
    ' Clear the Where clause
    varSQLWhere = Null
    ' If entered a customer first name,
    If Not IsNothing(Me.txtCFirst) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = "(C.CFirst Like '" & Me.txtCFirst & "*')"
    End If
    ' If entered a customer last name,
    If Not IsNothing(Me.txtCLast) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.CLast Like '" & Me.txtCLast & "*')"
    End If
    ' If entered a Provider License number
    If Not IsNothing(Me.txtLicenseNo) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.LicenseNo Like '" & Me.txtLicenseNo & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo Like '" & Me.txtSiteNo & "*')"
    End If
    ' If entered an email address,
    If Not IsNothing(Me.txtEmail) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.Email Like '" & Me.txtEmail & "*')"
    End If
    ' If entered a business name,
    If Not IsNothing(Me.txtBusinessName) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessName Like '" & Me.txtBusinessName & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo = '" & Me.txtSiteNo & "')"
    End If
    ' If entered a business phone number,
    If Not IsNothing(Me.txtBusinessPhone) Then
    ' Create a search on business phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessPhone = '" & Me.txtBusinessPhone & "')"
    End If
    ' Must enter at least one criterion
    If IsNothing(varSQLWhere) Then
    Call CustomError("You must enter at least one search criterion.", _
    OK, "Find Customers")
    Exit Sub
    End If
    ' Build the SQL to insert customers
    strSQL = "INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, " & _
    "DefaultAddress, BusinessAddress1, BusinessCity, BusinessStateOrProvince, " & _
    "LicenseNo,TaxId, ProvNPI, ParentNPI, SiteNo ) " & _
    "SELECT C.CustomerID, C.CFirst, C.CLast, C.DefaultAddress, " & _
    "C.BusinessAddress1, C.BusinessCity, C.BusinessStateOrProvince, C.LicenseNo " & _
    "C.LicenseNo, C.TaxId, C.ProvNPI,C.ParentNPI, C.SiteNo, C.ContactPerson " & _
    "FROM tblCustomers AS C " & _
    "WHERE " & varSQLWhere
    ' Call the function to clear and load the selected customers
    If fctPlugFind(strSQL) = False Then
    ' Got an error - tell the user and exit
    Call CustomError("There was a problem with the search. " & _
    "Please close this window and try again.", , "Search Failure")
    Exit Sub
    End If
    ' Point to this database
    Set db = DBEngine(0)(0)
    ' Refresh TableDefs to get a good count
    db.TableDefs.Refresh
    ' Get the loaded record count
    intRCount = db.TableDefs("tvwCustomers").RecordCount
    ' Set up the form message
    If intRCount <> 1 Then
    Me.RecordCount = intRCount & " Records found."
    Else
    Me.RecordCount = intRCount & " Record found."
    End If
    ' Reveal the result
    Me.RecordCount.Visible = True
    ' Clear the database object
    Set db = Nothing
    ' If 10 or fewer
    If intRCount <= 10 Then
    ' Expand the form to show them all
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (intRCount * Me.Detail.Height)
    Else
    ' Show the first 10
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (10 * Me.Detail.Height)
    End If
    ' Reload my recordset
    Me.Requery
    ' Unhide the detail section
    Me.Detail.Visible = True

    End Sub

  5. #5
    Join Date
    Jun 2008
    Posts
    23
    I wish I do, Please help me

    Thanks

  6. #6
    Join Date
    Jun 2008
    Posts
    23
    Hello PkStormy,

    Here is more information


    I designed a form called frmCustomerFind (with text fields of criterias listed below) unbound to table tblCustomers I want to be able to search record that are stored in the table tblCustomer using any of the the following criteria CFirst, CLast,ClicenseNo, CSiteNo, CEmail and CBusinessName. Once record is found to insert the records into a table called tvwCustomers.But when I search using any of the criteria I will get the following message. " There was a problem with search. The records are there in the table, but the code below is not populating then frmCustomerFind form and tvwCustomers table.

    Please help


    Option Compare Database
    Option Explicit

    Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim strSQL As String, varSQLWhere As Variant, intRCount As Integer
    ' User wants to search for customers
    ' Clear the Where clause
    varSQLWhere = Null
    ' If entered a customer first name,
    If Not IsNothing(Me.txtCFirst) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = "(C.CFirst Like '" & Me.txtCFirst & "*')"
    End If
    ' If entered a customer last name,
    If Not IsNothing(Me.txtCLast) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.CLast Like '" & Me.txtCLast & "*')"
    End If
    ' If entered a Provider License number
    If Not IsNothing(Me.txtLicenseNo) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.LicenseNo Like '" & Me.txtLicenseNo & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo Like '" & Me.txtSiteNo & "*')"
    End If
    ' If entered an email address,
    If Not IsNothing(Me.txtEmail) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.Email Like '" & Me.txtEmail & "*')"
    End If
    ' If entered a business name,
    If Not IsNothing(Me.txtBusinessName) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessName Like '" & Me.txtBusinessName & "*')"
    End If
    ' If entered a Site number,
    If Not IsNothing(Me.txtSiteNo) Then
    ' Create a search on home phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.SiteNo = '" & Me.txtSiteNo & "')"
    End If
    ' If entered a business phone number,
    If Not IsNothing(Me.txtBusinessPhone) Then
    ' Create a search on business phone
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.BusinessPhone = '" & Me.txtBusinessPhone & "')"
    End If
    ' Must enter at least one criterion
    If IsNothing(varSQLWhere) Then
    Call CustomError("You must enter at least one search criterion.", _
    OK, "Find Customers")
    Exit Sub
    End If
    ' Build the SQL to insert customers
    strSQL = "INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, " & _
    "DefaultAddress, BusinessAddress1, BusinessCity, BusinessStateOrProvince, " & _
    "LicenseNo,TaxId, ProvNPI, ParentNPI, SiteNo ) " & _
    "SELECT C.CustomerID, C.CFirst, C.CLast, C.DefaultAddress, " & _
    "C.BusinessAddress1, C.BusinessCity, C.BusinessStateOrProvince, C.LicenseNo " & _
    "C.LicenseNo, C.TaxId, C.ProvNPI,C.ParentNPI, C.SiteNo, C.ContactPerson " & _
    "FROM tblCustomers AS C " & _
    "WHERE " & varSQLWhere
    ' Call the function to clear and load the selected customers
    If fctPlugFind(strSQL) = False Then
    ' Got an error - tell the user and exit
    Call CustomError("There was a problem with the search. " & _
    "Please close this window and try again.", , "Search Failure")
    Exit Sub
    End If
    ' Point to this database
    Set db = DBEngine(0)(0)
    ' Refresh TableDefs to get a good count
    db.TableDefs.Refresh
    ' Get the loaded record count
    intRCount = db.TableDefs("tvwCustomers").RecordCount
    ' Set up the form message
    If intRCount <> 1 Then
    Me.RecordCount = intRCount & " Records found."
    Else
    Me.RecordCount = intRCount & " Record found."
    End If
    ' Reveal the result
    Me.RecordCount.Visible = True
    ' Clear the database object
    Set db = Nothing
    ' If 10 or fewer
    If intRCount <= 10 Then
    ' Expand the form to show them all
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (intRCount * Me.Detail.Height)
    Else
    ' Show the first 10
    Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
    (10 * Me.Detail.Height)
    End If
    ' Reload my recordset
    Me.Requery
    ' Unhide the detail section
    Me.Detail.Visible = True

    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I admire your attention to commenting what you are doing (or should that read the original devloper where you got this code from), however I think there is no need to comment every statement, but there is no general comment at he front of your code blocks (effectively saying what this block is attempting to d.

    fer instance
    rather than duplicate all the comments I'd put somehtign like
    'run though the controls to see if user has made any selections, and if so build the SQL to retrieve that data

    when building your SQL I'd use a handy trick passed on from Rudy/R937 in these forums

    Code:
    strSQL = "select blah from my table WHERE 1=1"
    this returns all records
    then you have your series of if statements
    Code:
    If Not IsNothing(Me.txtEmail) Then ' Create a wildcard search for leading characters
    strSQL = strSQL & " AND C.Email Like " & chr$(34) Me.txtEmail & "*" & chr$(34)
    End If
    so every time you add a parameter you always add the exisitng SQL statement and start the line with an AND

    I think you are getting the test/string/character delimiters confused SO Id recommend that you use chr$(34) to add a " to your sql line I prefer to use the chr$(34) as it makes it easier in my mind where the quote/text delimiters are. on some screens in some fonts it can be difficult to spot the difference / precendence of '" or "'

    there must be a space between each element of your sql sentence unless there is same other punctuation such as a comma. stylistically I prefer to put it in front eg " AND ProdID=5576", but its just as valid "AND ProdID=5576 ".

    you don't really need the brackets, unless there is more than one term that must be handled to somehting
    eg AND (DoB<1999/01/01 and EmpStatus="blah")
    or
    AND (DoB<1999/01/01 and EmpStatus<>"blah")

    if you are having SQl syntax problems t always helps to display the sql that is having the problems before sending it to the sql engine just to make sure it is vali. ue a msg box. its all to easy to "know" what you are sending to the sql engine, but that may not be what yu are sending.

    as Paul Stormy has already please do not post all our code, as people here have neither the time (or in my case the patience) to read through it all.

    please identify what line the code halts on.we all ellegedly have fulltime jobs, we cannot aford to spend huge amounts of time looking through code, especailly if that code is workng.

    it will also help you as a developer to establish your own problem solving skils if you can zoom in on where the error actually lies. in this case I think the first thing you should do is read though this code and understand what it actaully does. the original developer has been very helpfull in tellingyou what each staement does.. but you probably need to use the help file to fully understand what is being doen.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since the records are showing up in the tvwCustomers table, your SQL seems to be ok, but your function "fctPlugFind(strSQL)" is returning false. I'd look that over, and make sure you're setting it to return TRUE at the right spot
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jun 2008
    Posts
    23

    Code Problem - Very Urgent Please

    Please I need help with this code: I want to INSERT INTO table2 with data from table1, then pull the data from table2 using search criteria


    Private Sub cmdSearch_Click()
    Dim db As DAO.Database
    Dim strSQL As String, varSQLWhere As Variant, intRCount As Integer

    varSQLWhere = Null

    strSQL = "INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, " & _
    "DefaultAddress, BusinessAddress1, BusinessCity, BusinessStateOrProvince, " & _
    "LicenseNo,TaxId, ProvNPI, ParentNPI, SiteNo ) " & _
    "SELECT C.CustomerID, C.CFirst, C.CLast, C.DefaultAddress, " & _
    "C.BusinessAddress1, C.BusinessCity, C.BusinessStateOrProvince, C.LicenseNo " & _
    "C.LicenseNo, C.TaxId, C.ProvNPI,C.ParentNPI, C.SiteNo, C.ContactPerson " & _

    "FROM tblCustomers AS C " & _
    "WHERE " & varSQLWhere

    ' If entered a customer last name,
    If Not IsNothing(Me.txtCLast) Then
    ' Create a wildcard search for leading characters
    varSQLWhere = (varSQLWhere + " AND ") & _
    "(C.CLast Like '" & Me.txtCLast & "*')"
    End If
    End Sub

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're going to have to use two queries. One to insert the data, another to pull out the copied data. You can't do both at the same time. Just .Execute one after the other.

    Also, fyi, you can't set the varSQLWhere variable AFTER you use it. Well, you can, but it does nothing. The end result of your where clause is still "WHERE Null".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •