Results 1 to 3 of 3

Thread: Find Button

  1. #1
    Join Date
    Jan 2002
    Location
    Attleboro, MA
    Posts
    5

    Question Unanswered: Find Button

    I am working in Access97, and am trying to create a find button that usees 2 fields to select a record from a form. I need some help with the coding of the button. What I have so far is listed below. Does access97 support recordset findFirst call? I cannot seem to get the form to advance to the record. Any help would be greatly apprehiated. 'Find record is where the code stops working.

    Private Sub Command36_Click()
    Dim strSource As String
    Dim strService As String
    Dim strSource_Text As String
    Dim strSource_Title As String
    Dim strService_Text As String
    Dim strService_Title As String
    Dim strRecord As String
    Dim x As Integer
    Dim dbs As DAO.Database, rst As DAO.Recordset

    'Define Variables
    strSource_Text = "Please enter the Source_ID of the record you wish to find."
    strSource_Title = "Find Source_ID!"
    strService_Text = "Please enter the Service_Code of the record you wish to find."
    strService_Title = "Find Service_Code!"
    'Input Boxes
    strSource = InputBox(strSource_Text, strSource_Title)
    strService = InputBox(strService_Text, strService_Title)

    'Find Record
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("dbo_CUBE_PRODUCT_HIERARCHY")
    If strSource = SOURCE_ID And strService = SERVICE_CODE Then

    x = MsgBox(prompt:="Action executed", Buttons:=vbYes + vbCritical, title:="Action SetFocus")

    End If
    End Sub

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    try it with the following changes:

    dim dbs as database
    dim rst as recordset
    dim strSQL as String

    ....


    if len(strSource)>0 AND len(strService)>1 Then
    strSQL = "SELECT * FROM dbo_CUBE_PRODUCT_HIERARCHY WHERE Source_ID = '" & strSOURCE & "' AND SERVICE_CODE = '" strService "'"
    end if


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

  3. #3
    Join Date
    Jan 2002
    Location
    Attleboro, MA
    Posts
    5

    Question

    Thank you for the help. I wrote some code that does find the record. However, it is not replacing whatever record is on the form with that record. And deleteing the record it writes over. How do I get it to move to the record set by the criteria. Here is the as I have it. Any help would be greatly appreciated.

    Private Sub Command36_Click()
    Dim strSource As String
    Dim strService As String
    Dim strSource_Text As String
    Dim strSource_Title As String
    Dim strService_Text As String
    Dim strService_Title As String
    Dim rstCriteria As String
    Dim x As Integer
    Dim dbs As DAO.Database, rst As DAO.Recordset
    'Define Variables
    strSource_Text = "Please enter the Source_ID of the record you wish to find."
    strSource_Title = "Find Source_ID!"
    strService_Text = "Please enter the Service_Code of the record you wish to find."
    strService_Title = "Find Service_Code!"
    'Input Boxes
    strSource = InputBox(strSource_Text, strSource_Title)
    strService = InputBox(strService_Text, strService_Title)
    rstCriteria = "SELECT * FROM dbo_CUBE_PRODUCT_HIERARCHY WHERE SOURCE_ID = '" & strSource & "' And SERVICE_CODE = '" & strService & "'"

    'Find Record
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(rstCriteria)

    'rst.RecordCount
    MsgBox rstCriteria

    Forms("Product Hierarchy Update Form")!SOURCE_ID.Value = rst!SOURCE_ID
    Forms("Product Hierarchy Update Form")!SERVICE_CODE.Value = rst!SERVICE_CODE
    Forms("Product Hierarchy Update Form")!PRODUCT_HIERARCHY_CODE.Value = rst!PRODUCT_HIERARCHY_CODE
    Forms("Product Hierarchy Update Form")!FAMILY.Value = rst!FAMILY
    Forms("Product Hierarchy Update Form")!PHGROUP.Value = rst!PHGROUP
    Forms("Product Hierarchy Update Form")!PRODUCT.Value = rst!PRODUCT
    Forms("Product Hierarchy Update Form")!KEY_VOLUME_IND.Value = rst!KEY_VOLUME_IND
    Forms("Product Hierarchy Update Form")!INDICATOR_2.Value = rst!INDICATOR_2
    Forms("Product Hierarchy Update Form")!INDICATOR_3.Value = rst!INDICATOR_3

    End Sub

Posting Permissions

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