Query to use form's value for criteria question.. 1

[MeSoCrazy]

Hello all,

I have a query that needs to use a value in a form as it's criteria. I know this is simple, and I have that part working.

What I really need the query to do is look to see the value from the form, if it is not open or null use a value from a second form.

I tried this code in my expression builder as a test.

IIf(IsNull([Forms]![frmEnterIssue]![TicketNbr]),66,[Forms]![frmEnterIssue]![TicketNbr])

However, when the frmEnterIssue form is not open, it ask for user input to give the value, thus not seeing it as NULL and using the 66 value in the criteria statement.

Is there another way? I only used the 66 in the statement as a test. My final statement the 66 would be replaced by the second forms field to get the value.

Thanks. Troy

 

[dhookom]

It's difficult to understand your environment and why a value could come from one form or another. You could open both forms and keep one hidden. I would probably use code to write the SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[MeSoCrazy]

Duane,

Yes, I was thinking I was going to have to do the SQL in VBA code. That is my backup plan at the moment.

Since the query was already written as a MS Access query object and a report was already built upon the query as its source, I was hoping to save some time.

The reason for multiple forms is that the application has a Issue Search form that allows the user to print the top ten issue results from their search criteria, and we also have an individual edit create issue form that allows the user to print the individual issue.

both forms call sthe same query which essentially goes out and gets all of the data and the report filters based on the the STWhere we hand it from the print button. I didn't design this method, but obviously as the amount of data grew so did the amount of time for the query to run. So I am trying to make this run more efficiently by passing the ID values I want the query to use instead of going and acquiring all data. Unfortunately, I have two different form boxes that need to be checked to determine this value to use in the query criteria.

Thanks, Troy

 

[MeSoCrazy]

Duane,

I wasn't clear at the bottom of my last post. I have two different form textboxes to acquire the criteria from, but each of those boxes are on different forms. I was hoping for a way to determine from the query object if the form was open.. use that forms value, else it would be the second forms value that would be open.

Thanks, Troy

 

[dhookom]

You could use a function to return the criteria value. The function could determine which form was open and then return the appropriate value. I would think this would be somewhat inefficient.

I don't typically include any dynamic filter/criteria in a report's record source. I build a where condition with code and use this in the DoCmd.OpenReport ...



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You can build a function like this to check to see if form one is open and has a value, if not check if form two is open and has a value and then return the correct value to a query.

public function getTktNum() as variant
  dim frm as access.form
  'check to see if first form is open
  if currentProject.allforms("frmOne").isloaded then
     set frm = forms("frmOne")
     'check to see if control value is not null
     if not isNull(frm.someControl)
       getTktNum = forms("frms1").someControl
     else
       'what do you want to do if the 1st form is open but
       'no value
     endif
  elseif currentProject.allforms("frmTwo").isLoaded then
      set frm = forms("frmTwo")
     'check to see if control value is not null
      if not isNull(frm.someControl2)
       getTktNum = frm.someControl2
      else
       'Some code if form 1 closed, form 2 open, but value is null
      end if
  else
    ' some code if both forms are not open
  end if
end function

then in a query

select fld, fld2,... from someTable where someTicketField = getTktNumber()

As Duane points out this is probably not a very clean design, but the above should work.

 

[MeSoCrazy]

MajP,

Yes I agree that the design isn't what I would like, but I am stuck with trying to fix something without being able to do it correctly.

I have taken you Function and incorporated it into my app. This works fine if in the query I use your sample code..

select fld, fld2,... from someTable where someTicketField = getTktNumber()

But because the search screen will send 10 id numbers separated by a comma, I need to use an IN statement like:

select fld, fld2,... from someTable where someTicketField IN (getTktNumber())

When I do this it fails and brings back zero records. I have created an extra column in my data that is

Temp: getTktNumber()

When I run the query by pasting my 10 ID's into the IN of my Where it runs correctly and the TEMP field actually has values that I would expect coming from the getTktNumber Function. It works when using an equal statement in the Where criteria, but fails when trying to us it inside the IN of the criteria.

Any ideas?

 

[MajP]

Not to say I would design this way, but to answer your question. Assume you had a textbox on a form with values seperated by commas. (Not sure what you mean that the form sends the 10 values seperated by a comma). I could build a function like

Public Function inList(varVal As Variant) As Boolean
  Dim frm As Access.Form
  Dim strList As String
  Dim aList() As String
  Dim listItm As Variant
  Set frm = Forms("frmList")
  strList = Nz(frm.txtList.Value, "")
  aList = Split(strList, ",")
  For Each listItm In aList
    If Not IsNull(varVal) Then
      If CStr(varVal) = listItm Then
        inList = True
        Exit Function
      End If
    End If
  Next listItm
End Function

Then in a query use it like.

SELECT
Employees.LastName,
Employees.FirstName
FROM
Employees
where
inlist([FirstName]);


The whole thing is a little sketchy. Any chance you want to redesign? You can use the same concept on a multiselect listbox.

 

[MeSoCrazy]

MajP,

Thanks, I appreciate your input and will give this a try soon. I agree with the sketchy. Unfortunately, I work for a business group in a large corporate structure, and I have been told to make a quick a fix as possible to the current design.

Frankly I have two swollen ankles from getting knocked off of my soapbox explaining to my manager why the design is flawed, why we could do some not so difficult re-design to make it much better.

I have a big red dot on my forehead, and it matches the big red dot on my cubicle wall!

 

[MajP]

Good luck. I hear you, it just seems the band-aid is becoming overly complicated. Maybe once you get the band-aid done you can propose a better fix.