Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    9

    Unanswered: Access 2000, Where Clause or FindRecord

    Hi,

    I have a DB with 8 forms and a switchboard for tracking project information. I have a "Jump To" Combo Box on each form with the purpose of opening another form, and going to the "Project_Number" (field) on the opened form that matches the same filed on the current form (ie, Project_Number 4138 is selected on my "Funding" Form, I want to open the "Design" Form and go to Project_Number 4138.) Here is what I have tried:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = Combo38
    stLinkCriteria = Me![Project Number]
    DoCmd.OpenForm stDocName
    DoCmd.FindRecord stLinkCriteria

    This opens the form, and does not go to the correct record.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = Combo38
    stLinkCriteria = "[Project Number] = " & Me![Project Number]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    This opens the form, goes to the correct record, but filters out all other records, which I do not want either.

    I tried various options with setfocus and gotorecord, none of which worked. Any help will be greatly appreciated.

    TB

  2. #2
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    The code you have filters data, it doesn't "find" it. I haven't done what you're talking about (I usually want just the one record to show up). I think you'll have to do a FindRecord.

  3. #3
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    9
    I tried the FindRecord, as posted in the above code. Didn't work. Any ideas what I how I can make FindRecord work for my application?

    Thanks in Advance

    TB

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    stDocName = Combo38
    stLinkCriteria = "[Project Number] = " & Me![Project Number]
    ' If Project Number is a text field then use:
    ' stLinkCriteria = "[Project Number] = '" & Me![Project Number] & "'"
    DoCmd.OpenForm stDocName
    DoCmd.FindRecord stLinkCriteria



  5. #5
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    9
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = Combo38
    stLinkCriteria = "[Project Number] = " & Me![Project Number]
    DoCmd.OpenForm stDocName
    DoCmd.FindRecord stLinkCriteria

    Opened the form, but did not go to thhe correct record. Wouldn't the above code make stLinkCriteria resemble a Where Clause?

    Thanks in Advance
    TB

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Indeed....sorry about that.

    Here are a couple of ways you could attack this....

    Code:
    stDocName = Combo38
    stLinkCriteria = Me![Project Number]
    DoCmd.OpenForm stDocName
    DoCmd.FindRecord stLinkCriteria, acEntire, , acSearchAll, , acAll
    and probably more accurately with:

    Code:
    Dim R_ID As Long
    Dim stDocName As String
    Dim stLinkCriteria As String
     
    stDocName = Combo38
    stLinkCriteria = "[Project Number] = " & Me![Project Number]
    R_ID = DLookup("[myTableRecordID]", stDocName, stLinkCriteria)
    DoCmd.OpenForm stDocName
    DoCmd.GoToRecord , , acGoTo, R_ID

  7. #7
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    9
    Unfortunately, the R_ID returns an Long Value that is the project number in this case, not the record ID. For example, my project numbers are 4100 to 4400, so R_ID returns (for example) 4104. Now, the acGoTo then looks for the 4104th record. THis will work, if there is a way to return the record number from the table.

    Thanks in Advance,

    TB

  8. #8
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    9

    Talking

    Eureka!!!

    Here is the code that works...

    Private Sub Combo38_Change()
    On Error GoTo Err_Combo38_Click

    Dim stDocName As String
    Dim AbsPos As Integer

    stDocName = Combo38 'Retrieve the Form to Open
    AbsPos = Me.Recordset.AbsolutePosition + 1 'Get the position of the current record
    'Not sure why I have to add 1 here, but it was retrieving the record prior, so I adjusted.

    DoCmd.OpenForm stDocName 'Open the form
    DoCmd.GoToRecord acDataForm, stDocName, acGoTo, AbsPos 'Go to the record open on the previous form...

    Exit_Combo38_Click:
    Exit Sub

    Err_Combo38_Click:
    MsgBox Err.Description
    Resume Exit_Combo38_Click
    End Sub

    Any idea why I have to add 1 to the recordset.absoluteposition?

    By the way, thanks for all your help.

    Todd Buckles

Posting Permissions

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