Searching for several parameters with one dropdown selection

vba , access 2007 , MS.Chart.2008 Oxford, United Kingdom
  • 8 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 :)

  • 8 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.

“We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.” - Donald Knuth