Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Question Unanswered: How do I open a form with the record that I want?

    Hello all,
    Please help...

    I'm what you can call a "newbee" to VBA programming and I've run in to a problem regarding opening a form with the info that I want to look at.

    The data provided to that form has an ID field called "TR No" as in tracking number.

    In the form that I'm currently standing in, I copy one cell and put that in to the "Docmd" command like this:

    Code:
    Private Sub Kommandoknapp26_Click()
    'On Error GoTo Err_Kommandoknapp26_Click
        
        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim stTableName As String
        Dim TRNo As String
        
        
        
        TR_No.Setfocus 'set the focus on "tr No"
        
        TRNo = TR_No.Text ' Copy the text to an variable
    
        stDocName = "SRS_Field_data"
        
        'Now, I want to open the form with the record that has collumn [TR No] row = TRNo
        DoCmd.OpenForm stDocName, acNormal, , TRNo, acFormReadOnly
        
        DoCmd.Beep
        
    End Sub
    I do not want to alter the information in the record, just look at it...

    Regards Johan

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Simplest way to do this would be with a combobox. On the form (based on your query/table) that displays the record info:

    1) Place a combobox on the form

    2) When the wizard comes up select:
    Find a record based on the value I selected

    3) In the next screen move TRNo over to the right pane

    4) Finish stepping thru the wizard

    Set the Form's properties:

    Allow Edits No
    Allow Deletions No
    Allow Additions No

    Set the combobox's properties:

    Auto Expand Yes

    Run the form, and when you start to enter the TRNo in the combobox it will autofill. Press enter and the appropriate record should pop up!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    You have nearly got it!

    You dont need to copy the variable that way either

    stLinkCriteria = "[TRNO]=" & Me!TrNo 'This just filters on the TRNO of the database and uses the TrNo on the form as the filter

    DoCmd.openform stDocName,,,stLinkCriteria, acFormReadOnly

    Put the rest of the code in and that should work

    For future reference you do not need to set the focus to the control to "copy" is to a variable

    You can just use

    Variable = ControlName
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  4. #4
    Join Date
    Jun 2006
    Posts
    4

    Just tried it and it didn't work

    Hello ChrisGolden,

    I've tried what you suggested but what i get is a popup box telling me to write in what TRNo I'm looking for and when I've done that it opens a blank record

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    Quote Originally Posted by ChrisGolden
    You have nearly got it!

    You dont need to copy the variable that way either

    stLinkCriteria = "[TRNO]=" & Me!TrNo 'This just filters on the TRNO of the database and uses the TrNo on the form as the filter

    DoCmd.openform stDocName,,,stLinkCriteria, acFormReadOnly

    Put the rest of the code in and that should work

    For future reference you do not need to set the focus to the control to "copy" is to a variable

    You can just use

    Variable = ControlName
    Hello ChrisGolden,

    I've tried what you suggested but what i get is a popup box telling me to write in what TRNo I'm looking for and when I've done that it opens a blank record

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I think it should read (note the underscore in Tr_No):

    stLinkCriteria = "[TRNO]=" & Me!Tr_No

    Chris

  7. #7
    Join Date
    Jun 2006
    Posts
    4

    still nothing

    No that didn't work either...

    regards Johan

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    The Me! bit should be the same as whatever the name of the control on the form is, right click the control, the text box it will be, click properties and go to the "Other" tab, in there will be the name of the control.

    So if the control is called TR_No then it should be Me!TR_No

    The [TR] should also be whatever the ID field is in the table so if you go to the table that the form is opening and whatver field you want to match the control on the form to thats what should be inside the [TR]
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

Posting Permissions

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