Searching for several parameters with one dropdown selection

• • Reply

  3 years ago

  by egallen

  Emma Gallen

  Oxford, United Kingdom

  Joined 3 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 :)

  Report abuse
• • Reply

  3 years ago

  by egallen

  Emma Gallen

  Oxford, United Kingdom

  Joined 3 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

  Report abuse