Searching for several parameters with one dropdown selection

vba , access 2007 , MS.Chart.2008 Oxford, United Kingdom
  • 11 years ago

    I have set up a form which has a boolean logic search table. I can fill dropdowns and then add my selections to the table one by one. What I am trying to do is make one selection on the dropdown which will then add several relavant parameters from a relavant table to the search. At the moment I have the following code:

    parametersrow = lastarrayentry(parameters, 2) + 2  ' +2 is to make rows 0 and 1 not matter
    'find end of results order list then set order number bigger than any used previously
    For i = 0 To lastarrayentry(parameters, 2)
        If IsNumeric(parameters(i, 0)) Then If reorderresults(parameters(i, 0)) = "" Then reorderresults(parameters(i, 0)) = i
    Next
    parameters(parametersrow, 0) = lastarrayentry(reorderresults) + 2  ' +2 is to make rows 0 and 1 not matter
    

    tablesarray = Split(newtables, ",") For i = 0 To UBound(tablesarray) For j = 0 To CurrentDb.tabledefs(tablesarray(i)).Fields.Count - 1 fieldname = CurrentDb.tabledefs(tablesarray(i)).Fields(j).name 'for each table that begins with the word total If fieldname Like "total*" Then 'check if there is a corresponding summary table tablename = "summary" & Right(fieldname, Len(fieldname) - 6) If checktablefieldexists(tablename, , False) <> "Table Error" Then For k = 0 To CurrentDb.tabledefs(tablename).Fields.Count - 1 'if they have conditions table then add all fields from conditions table excluding id field If CurrentDb.tabledefs(tablename).Fields(k).name <> "id" Then parametersrow = parametersrow + 1 parameters(parametersrow, 0) = 2 'suggestion parameters(parametersrow, 2) = tablename parameters(parametersrow, 3) = CurrentDb.tabledefs(tablename).Fields(k).name End If Next k End If End If Next j Next i

    This allows me to add several parameters to the table with one selection but it won't allow me to search for them afterwords.

    Any suggestions would be great ...

    Thanks :)

  • 11 years ago

    Sorry the second part of the code doesn't look very clear:

    tablesarray = Split(newtables, ",") For i = 0 To UBound(tablesarray) For j = 0 To CurrentDb.tabledefs(tablesarray(i)).Fields.Count - 1 fieldname = CurrentDb.tabledefs(tablesarray(i)).Fields(j).name 'for each table that begins with the word total If fieldname Like "total*" Then 'check if there is a corresponding summary table tablename = "summary" & Right(fieldname, Len(fieldname) - 6) If checktablefieldexists(tablename, , False) <> "Table Error" Then For k = 0 To CurrentDb.tabledefs(tablename).Fields.Count - 1 'if they have conditions table then add all fields from conditions table excluding id field If CurrentDb.tabledefs(tablename).Fields(k).name <> "id" Then parametersrow = parametersrow + 1 parameters(parametersrow, 0) = 2 'suggestion parameters(parametersrow, 2) = tablename parameters(parametersrow, 3) = CurrentDb.tabledefs(tablename).Fields(k).name End If Next k End If End If Next j Next i

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” - Antoine de Saint Exupéry