General Question about VLOOKUP equivalent in access (1 Viewer)

[William Demuth]

I need help with a general concept. I am more of an Excel guy, so I probably don't know the correct terms for Access

I want to be able to click on the data that someone else has entered into a form for more details.

The form is a detail form for forecasting opportunities, and one field is to select a registration name from the registration table.

I want to be able to click on this name, and have all the lines in the registration table that have the same name open in a form. (I have a form that shows ALL registrations, so I just want to filter it to match the entry)

In excel, this can be done with a vlookup. How can I do it in Access? If I can get a general direction I can research myself!

Please note, the name in the registration table is an index but NOT the primary, and several lines may have the same title.

 

[jdraw]

see if this tutorial helps
http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

 

[William Demuth]

Not exactly what I need. (But I did JUST learn what you showed me)

Here is the simple version

I want to click on the registration that was selected, and open a seperate form that shows the registration list filtered down to what was selected in the combo box.

I know I am doing a LOUSY job of explaining, but without basic terms its tough!

Thanks for trying!

 

[gemma-the-husky]

you need a query to select the rows you want

include the criteria forms!myformname!myfieldname

replacing the "names" with what you have for real.

base your popup form on this query.

should work.

 

[DevastatioN]

The query method described above will work. If you have a form already that shows the exact data you want, and all you want is to have it filtered you can use the following code:

I'm going to assume you are opening the form and want it filtered.

Dim strWhere As String  'SQL Syntax for Where Statement
    
    strWhere = ""

    If Not IsNull(txtFilterField) Then
        strWhere = "[FieldName] =" & txtFilterField
    End If

    'Close search form
    DoCmd.Close

    'Open the form to filter
    DoCmd.Open "frmForm"

    'Set the form appropriately for the seach
    Screen.ActiveForm.FilterOn = False 'Incase filter was already on
    Screen.ActiveForm.Filter = strWhere
    Screen.ActiveForm.FilterOn = True
    Screen.ActiveForm.AllowAdditions = False 'Don't allow users to enter new data if form is filtered

Note if the search field is text you need to use the following line instead

strWhere = "[FieldName] Like '*" & txtFilterField & "*'"

 

[William Demuth]

I just took a victory lap around the office!

Thanks for the approach, I can take it from here!!!!