Results 1 to 3 of 3

Thread: Adodc Help

  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Adodc Help

    I have a form set up to search an access database.

    The first section of code searches by phone number. The second searches by last name and provides a list of records to choose from.

    The phone number search works perfectly but I can't get the last name search to work. As it sits now, when I type in a last name, a blank datagrid appears. Then when I do the phone number search it works. I am new to VB and programming and I can use all the advise I can get. Here is my code:

    I just want to be able to do a search by last name

    If Option1.Value = True Then 'Search customer table by phone number
    varSearchPhone = Text1.Text
    strSearch = "SELECT tblcustomermain.[cstmrhmphn], tblcustomermain.cstmrlstnme, tblcustomermain.cstmrfrsnme, tblcustomermain.CstmrrNbr FROM tblcustomermain WHERE [cstmrhmphn] = " & varSearchPhone
    Form2.Adodc1.RecordSource = strSearch
    Form2.Adodc1.CommandType = adCmdText
    Form2.Adodc1.Refresh
    ElseIf Option2.Value = True Then 'Search customer table by last name
    strSearchName = Text1.Text
    strSearch = "SELECT tblcustomermain.[CstmrLstNme], tblcustomermain.CstmrFrsNme, tblcustomermain.CstmrHmPhn, tblcustomermain.CstmrrNbr From tblcustomermain Where [CstmrLstNme] = '[strSearchName]'"
    Form2.Adodc1.RecordSource = strSearch
    Form2.Adodc1.CommandType = adCmdText
    Form2.Adodc1.Refresh
    End If

  2. #2
    Join Date
    Dec 2004
    Location
    Laguna, Philippines
    Posts
    147
    Quote Originally Posted by hammb
    I have a form set up to search an access database.

    The first section of code searches by phone number. The second searches by last name and provides a list of records to choose from.

    The phone number search works perfectly but I can't get the last name search to work. As it sits now, when I type in a last name, a blank datagrid appears. Then when I do the phone number search it works. I am new to VB and programming and I can use all the advise I can get. Here is my code:

    I just want to be able to do a search by last name

    If Option1.Value = True Then 'Search customer table by phone number
    varSearchPhone = Text1.Text
    strSearch = "SELECT tblcustomermain.[cstmrhmphn], tblcustomermain.cstmrlstnme, tblcustomermain.cstmrfrsnme, tblcustomermain.CstmrrNbr FROM tblcustomermain WHERE [cstmrhmphn] = " & varSearchPhone
    Form2.Adodc1.RecordSource = strSearch
    Form2.Adodc1.CommandType = adCmdText
    Form2.Adodc1.Refresh
    ElseIf Option2.Value = True Then 'Search customer table by last name
    strSearchName = Text1.Text
    strSearch = "SELECT tblcustomermain.[CstmrLstNme], tblcustomermain.CstmrFrsNme, tblcustomermain.CstmrHmPhn, tblcustomermain.CstmrrNbr From tblcustomermain Where [CstmrLstNme] = '[strSearchName]'"
    Form2.Adodc1.RecordSource = strSearch
    Form2.Adodc1.CommandType = adCmdText
    Form2.Adodc1.Refresh
    End If
    instead of...
    strSearch = "SELECT tblcustomermain.[CstmrLstNme], tblcustomermain.CstmrFrsNme, tblcustomermain.CstmrHmPhn, tblcustomermain.CstmrrNbr From tblcustomermain Where [CstmrLstNme] = '[strSearchName]'"

    try this...
    strSearch = "SELECT tblcustomermain.[CstmrLstNme], tblcustomermain.CstmrFrsNme, tblcustomermain.CstmrHmPhn, tblcustomermain.CstmrrNbr From tblcustomermain Where [CstmrLstNme] = '" & strSearchName & "'"
    Give me a place to code and i shall move the earth!

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    It works! Thanks..Genious

Posting Permissions

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