1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access. Find the record number of a record (For forms and queries)

Discussion in 'Business Applications' started by firestormer, Apr 21, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    I want to use the GoToRecord command to go to a specific record in a form. The problem is this uses an offset or record number. How can i find the record number of a specific record?

    What i plan to do is use some code similar to this:

    docmd.GoToRecord acDataForm, "FrmComputer", acGoTo, expression

    where the expression is the record number of the record 'Computer01' for example.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    firestormer, this is quite difficult to achieve because you have to find 'Compter01' first.
    So the best way to learn how to do this is to create a Combo Box with the Wizard, but when it asks what you want to do click on the "Find a record on my form based on the value I selected in my Combo Box", this is the last of the 3 options.
    If you use the field containing "Computer01" in the combo and then view the VBA code it shows how to find the requiered record and it is actually beter than using the Docmd version.
     
  3. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Unfortunatly i dont think this will work as the criteria field and VB will be on a different form.

    I have 2 forms FrmFaultReport(Primary Key Fault Report ID) and FrmComputer (Primary Key Computer ID). The underlying tables of these forms are linked with a relationship using the field Computer ID. What i want is a command button in FrmFaultReport that when clicked opens FrmComputer and goes to the appropriote reocord.

    Now this can be done using the Command Button wizrard but this method opens the form and applys a filter which i dont want.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    firestormer, I see your problem, you want the Form to open with all records, but go to the one record from the FrmFaultReport?
    What you need is a record set based on the FrmComputer Table or Query, then use the Combo Box type "find" and then Goto the matching record in the Form using the Recordset Bookmark. The simplest way to do this is to have the code FrmComputer Form's "On Current" event Procedure and have a hidden field on the form that indicates that the form has been opened from the FrmFaultReport form. In this way the VB code only runs when you have come from the FrmFaultReport form.
    If you can post the database I can set it up for you.
     
  5. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    I think i see what your getting at.

    Have the command button that opens FrmComputer set the value of a hidden text box to the right record, and then the form goes to that record. Ill give it ago a get back to you.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    firestormer, the hidden field only holds a key to the fact that you have been directed to the form from the Fault report form.
    It will still need VB code to find the correct record.
     
  7. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    OK what ive done is this:

    Created a macro which minimizes FrmFaultReport, Opens FrmComputer and Sets the value of the unbound text box to the Computer ID in FrmFaultReport. The macro is run from a command button on FrmFaultReport. The macro works fine.

    I then added the follwoing VB to the AfterUpdate event of the new unbound text box in FrmComputer:

    Code:
     Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[Computer ID] = '" & Me![Text72] & "'"
        Me.Bookmark = rs.Bookmark
    Now the code works fine. BUT running the code isn't.

    I cant seem to get the code to run after the macro sets the value of the unbound text box (Text72). Ive also tried the OnChange event and that didnt work.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    firestormer the code needs to go in the Form's "On Current" event Procedure, the reason being is that Access doesn't recognise Macro/VBA "updating" as an "Update Event".
     
  9. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    But if i put in the on current wont that prevent the user from changing the reocrd as the when the try they'll get sent back to the record it opened on?
     
  10. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Can i get the macro to run the code?
    How do i turn the code above into a function?
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    firestormer, the code in the On Current Event Procedure needs an If statement that says if the text box Text72 has a value then run the
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Computer ID] = '" & Me![Text72] & "'"
    Me.Bookmark = rs.Bookmark


    The only time it has a value is when you run the Macro, if you move off the record it should loose it's value.
     
  12. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    But as its unbound it wont lose its value, anyway ive found a work around, its bit messy but it works. Ive added an unbound check box and a macro to the end of the statement to set the tick box to true. If made the statement an IFstatement so that it only runs if the check box is false.
    I moved the whole lot to the OnTimer event and set the time interval to 100.

    This way the whole lot runs 100ms after the form opens and wont run again as the IF statement stops it after the first time.

    Thanks for the help.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/564860

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice