• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

MS Access VB script to open a form on a specified line

I have a search form with a list table in it called "Search Form". What I want is when i select a line in this table, after various filtering has been done,  and click on the button Open it will open a second form "Table List" that has another List Box in it and goes to the specified record but still displays all of the other records in the table.
Any help is much apreciated.
0
AGOIT
Asked:
AGOIT
  • 12
  • 11
1 Solution
 
rockiroadsCommented:
Do you mean go to the specified record in the Listbox?
0
 
AGOITAuthor Commented:
Yes I would like it to go to the specific record in the second listbox but also still display all the other information
0
 
shrikantssCommented:
When a List Box control has the focus, it will automatically scroll to the first item that begins with the letter you type. It will not, however, automatically select items based on more than the first letter. For example, if you type ap, you may want the control to select the first item that begins with ap. This tip explains how you can implement this behaviour using the API function SendMessage. Its declaration, which must be included in the program, is as follows:

Public Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
ByVal lParam As String) As Long

You'll also need the following constant, which tells the List Box control to select the first item that begins with the specified prefix:

Public Const LB_SELECTSTRING = &H18C

In addition to the List Box, this technique requires a Text Box control. The user enters text in the Text Box, and the List Box automatically selects the first matching item. The message is sent in the Text Box's Change event procedure:

Private Sub Text1_Change()

If Text1.Text <> "" Then
SendMessage List1.hwnd, LB_SELECTSTRING, -1, Text1.Text
End If

End Sub

Letting the user select List Box items based on more than just the first letter can be particularly useful when the list contains many items.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rockiroadsCommented:
ok, you could try this. Assuming you had some primary key field (eg a field called ID of type autonumber). We need this primary key to identify unique fields and it exists in the listbox as the bounded column
eg

In your first form, we call the second form like this

DoCmd.OpenForm "mysecondform", , , , , , "22"

22 is just an example but this represents passing in arguments to the 2nd form. In your case you would pass in the contents of the ID field

eg., say you wanted to call form frmDetail and id field was called ID
DoCmd.OpenForm "frmDetail", , , , , , Me.ID

Now that you call this second form passing in the unique id (primary key), you need to handle it in the second form

Using the load event, we check for arguments then validate it by trying to find it in the listbox. If we do then we set the value to it
eg listbox is called lstRecs

Private Sub form_load()
    MsgBox "TEST: You Passed in " & Nz(OpenArgs, "")
    If IsNull(OpenArgs) = False Then
        If Me.lstRecs.Recordset.NoMatch = True Then
            MsgBox "Cannot find record with key " & OpenArgs
        Else
            Me.lstRecs.Value = OpenArgs
        End If
    End If
End Sub

0
 
rockiroadsCommented:
hangon, missed the most crucial line,

Private Sub form_load()
    MsgBox "TEST: You Passed in " & Nz(OpenArgs, "")
    If IsNull(OpenArgs) = False Then
        Me.lstRecs.Recordset.FindFirst "ID = " & OpenArgs  'assumes ID is field in table that listbox is based upon
        If Me.lstRecs.Recordset.NoMatch = True Then
            MsgBox "Cannot find record with key " & OpenArgs
        Else
            Me.lstRecs.Value = OpenArgs
        End If
    End If
End Sub
0
 
AGOITAuthor Commented:
I do have a primary Key called ID. what I want is that when the user selects it item in List12 (First List box) and they click on the button "Open Form" it will automatically open the second form "Table List" which has the second list box "List13" on the line of the selected item.
Im not sure where I should put your coding in Rockiroads.
0
 
rockiroadsCommented:
I am assuming here that you have ID selected in the rowsource for your listbox and that is the bounded column

when u open your second form (as shown earlier - this gets put inside the click event of your command button), change the code in to use Me.List12.Value (assuming ID is the bounded column, which it should be anyway).

In the called form, form_load, change lstRecs to List13
0
 
AGOITAuthor Commented:
I have attached what I have at the mo. The information displayed in list12 (Row Source) comes from a query but I have put the bound column as being number 1, the ID field. The information on list 13 comes from a Table (Row Source). Both have Column 1 bound.
I know this prob isnt making much sense
Private Sub Open_Table_List_Form_Click()
On Error GoTo Err_Open_Table_List_Form_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Table List"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Open_Table_List_Form_Click:
    Exit Sub
 
Err_Open_Table_List_Form_Click:
    MsgBox Err.Description
    Resume Exit_Open_Table_List_Form_Click
    
End Sub

Open in new window

0
 
rockiroadsCommented:
Thats fine but you havent passed in the id from List12

DoCmd.OpenForm stDocName, , , stLinkCriteria,,,Me.List12.Value
0
 
AGOITAuthor Commented:
I put in your command after the DoCmd.OpenForm stDocName, , , stLinkCriteria and it gave me an error saying "The action or method is invalid because the form or report isn't bound to a table or query"
When I bound the second form to the Table the error was removed but in list13 it did not jump to the required line. It simply remained at the top. If I bind the form to the query it does the same.
0
 
rockiroadsCommented:
Can u post your code and also the code in form_load of the second form. Did you put that in?
0
 
AGOITAuthor Commented:
I didnt have the code in for the second form. I put it in as above but now compile error.
Below are both scripts.
Thanks for all this help. I know im prob wrecking your help
List12
 
Private Sub Open_Table_List_Form_Click()
On Error GoTo Err_Open_Table_List_Form_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Table List"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Open_Table_List_Form_Click:
    Exit Sub
 
Err_Open_Table_List_Form_Click:
    MsgBox Err.Description
    Resume Exit_Open_Table_List_Form_Click
    
End Sub
 
 
List13
 
 
Private Sub form_load()
    MsgBox "TEST: You Passed in " & Nz(OpenArgs, "")
    If IsNull(OpenArgs) = False Then
        Me.lstRecs.Recordset.FindFirst "ID = " & OpenArgs  'assumes ID is field in table that listbox is based upon
        If Me.lstRecs.Recordset.NoMatch = True Then
            MsgBox "Cannot find record with key " & OpenArgs
        Else
            Me.lstRecs.Value = OpenArgs
        End If
    End If
End Sub

Open in new window

0
 
rockiroadsCommented:
The example I gave used lstRecs, but I said you have to change it to List13 because that is what you are using
0
 
AGOITAuthor Commented:
Ok done that. This is what I have now. I am getting the message box but when the second form opens the list box is still showing all the contents of the table however it is not jumping straight to the specific record that was selected in the List12 box
First Form
 
Private Sub Open_Table_List_Form_Click()
On Error GoTo Err_Open_Table_List_Form_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Table List"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.OpenForm stDocName, , , Me.List12.Value
 
Exit_Open_Table_List_Form_Click:
    Exit Sub
 
Err_Open_Table_List_Form_Click:
    MsgBox Err.Description
    Resume Exit_Open_Table_List_Form_Click
    
End Sub
 
 
 
 
Second Form
 
Private Sub form_load()
    MsgBox "TEST: You Passed in " & Nz(OpenArgs, "")
    If IsNull(OpenArgs) = False Then
        Me.List13.Recordset.FindFirst "ID = " & OpenArgs  'assumes ID is field in table that listbox is based upon
        If Me.List13.Recordset.NoMatch = True Then
            MsgBox "Cannot find record with key " & OpenArgs
        Else
            Me.List13.Value = OpenArgs
        End If
    End If
End Sub

Open in new window

0
 
rockiroadsCommented:
So the correct value of ID was displayed right?

What is the rowsource for List13? And have you got many rows? Did you scroll down to see if it was selected or not?
0
 
AGOITAuthor Commented:
The correct ID was put into the filter field under Data in the Form properties however the list did not show it highlighted or jump to it. There are approx 79000 lines in the table and the rowsource for List13 is the table
0
 
rockiroadsCommented:
Thats a lorra items to show in a list.

In list13, is the first column the ID field? and look in properties in form design, what is the bounded column?
0
 
AGOITAuthor Commented:
Alas that is quite a large amount of information I agree. And its only going to get larger ovber time.The ID field is the first column and the bounded column is 1
0
 
rockiroadsCommented:
Can u add a diagnosic please to form 2
and let me know what msgbox comes up with what messge

Private Sub form_load()
    MsgBox "TEST: You Passed in " & Nz(OpenArgs, "")
    If IsNull(OpenArgs) = False Then
        MSGBOX "DIAG: Looking for ID"
        Me.List13.Recordset.FindFirst "ID = " & OpenArgs  'assumes ID is field in table that listbox is based upon
        If Me.List13.Recordset.NoMatch = True Then
            MsgBox "Cannot find record with key " & OpenArgs
        Else
            Me.List13.Value = OpenArgs
            MSGBOX "DIAG: Found it " & Me.List13.ListIndex
        End If
    End If
End Sub
0
 
AGOITAuthor Commented:
I attached the code above to form 2 but it just displayed the first Test message then did the same as usual. No other message was displayed
0
 
rockiroadsCommented:
So you got

TEST: You Passed in

or did u get

TEST: You Passed in <<somevalue>>

0
 
AGOITAuthor Commented:
I got Test You Passed in
0
 
rockiroadsCommented:
Back to form 1, the value is not being passed. I just looked at your code again and noticed you are calling it twice. The first call is not passing in the argument.
Can you do that, pass in the value of List12 as shown earlier
0
 
AGOITAuthor Commented:
Hey That did it. Thanks so much. I removed the extra line and discovered that I only had 3 commas after the stDocName when there should be 6. It works perfect now and jumps and highlights the required filed. You are a Legend
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now