expression has no value error when running search query

[GaP42]

I have a form used to present a list of "qualifications", filtered by qualification type. The form has a search text box in the header to find a person's qualification. Initially when opened the form displays the relevant records. When the first character is entered the list should filter, however all records disappear, despite the char entered being a value in the set. I have tested the SQL independently as a standalone query and it shows the expected result set.

The vba query used in the form acting on the search text is:

strFilteredList = "SELECT tblQual.*, tluQual.QualType, tluQual.QualAuthority, [FirstName]+ "" "" +UCase([LastName]) AS PersonName, tblPersons.FirstName, tblPersons.LastName" & _
                " FROM tblPersons RIGHT JOIN (tblQual LEFT JOIN tluQual ON tblQual.QualTypeID = tluQual.QualTypeID)" & _
                " ON tblPersons.PersonID = tblQual.PersonID" & _
                " WHERE ([tblQual].[QualTypeID] = " & intQTypeID & _
                " AND [FirstName] LIKE "" * " & Me.txtFilterPerson.Value & " * "")" & _
                " OR ([tblQual].[QualTypeID] = " & intQTypeID & _
                " AND [LastName] Like "" * " & Me.txtFilterPerson.Value & " * "")" & _
                " ORDER BY [FirstName]+"" ""+UCase([LastName]);"

The intQTypeID is a value on the form representing the qualification type : Me.QTypeID - and shows the value used in the filter on opening the form.

The query above works when the ID is run with a intQTypeID from outside the form, however when running the search it reports the expression has no value - referring to: intQTypeID = Me.QTypeID

Hope it is a simple oversight on my part. Thanks

 

[Gasman]

So use Me.QTypeID or Me.intQTypeID ?

 

[arnelgp]

whre is the code theat set the value of intQTypeID?


maybe check your String filter (don't add Space on them):

...
...
...
" AND [FirstName] LIKE '*" & Me.txtFilterPerson.Value & "*')" & _
" OR ([tblQual].[QualTypeID] = " & intQTypeID & _
" AND [LastName] Like '* " & Me.txtFilterPerson.Value & *')" & _
" ORDER BY [FirstName]+"" ""+UCase([LastName]);"

 

[GaP42]

So use Me.QTypeID or Me.intQTypeID ?

The assignment of
intQTypeID = Me.QTypeID

is made prior to the search SQL

 

[arnelgp]

can you set the value again?

intQTypeID = Nz(Me!QtypeID, 0)

strFilteredList = "SELECT tblQual.*, ...
...
...

 

[GaP42]

whre is the code theat set the value of intQTypeID?


maybe check your String filter (don't add Space on them):

...
...
...
" AND [FirstName] LIKE '*" & Me.txtFilterPerson.Value & "*')" & _
" OR ([tblQual].[QualTypeID] = " & intQTypeID & _
" AND [LastName] Like '* " & Me.txtFilterPerson.Value & *')" & _
" ORDER BY [FirstName]+"" ""+UCase([LastName]);"

Thanks ArnelGP - embarrassing but that seems to be it (ie spaces) .. I'll blame the autoformatting that ... no names .. because it was probably me.

 

[GaP42]

Further to the above, the form opens with Allow Additions: No (to avoid the problem when the search returns no records). I have an Add Record button on the form which should allow a record to be added (Allow Additions = True, and is set back to False after insert). I attempt to set the QualTypeId for the new record (as a FK value to the Qualification Type Table) however I can't take the value from the form as it is a new record - I need the value used prior to pressing the Add New Record button (the records displayed are all of the same Qualification Type on the selection made when opening the form). Hence the warning that you cannot add or change the record as a related record is required in tluQual (QualTypId = 0).

Suggestions as to how to deal with this?

 

[ebs17]

Use OpenArgs. Schematic representation:

DoCmd.OpenForm "FormName", , , "ID_f = " & Me.ID, , , Me.ID

The opened form evaluates the transfer on OpenArgs itself:

[Event of own choice]
   If Not IsNull(Me.OpenArgs) Then
      If Me.NewRecord Then Me.txtID_f = CLng(Me.OpenArgs)
   End If
[End Event]

The WhereCondition filters on existing records, OpenArgs provides the ID for new records.

 

[GaP42]

Use OpenArgs. Schematic representation:

DoCmd.OpenForm "FormName", , , "ID_f = " & Me.ID, , , Me.ID

The opened form evaluates the transfer on OpenArgs itself:

[Event of own choice]
   If Not IsNull(Me.OpenArgs) Then
      If Me.NewRecord Then Me.txtID_f = CLng(Me.OpenArgs)
   End If
[End Event]

The WhereCondition filters on existing records, OpenArgs provides the ID for new records.

Thanks Eberhard - to clarify, the form in use (frmQual) is displaying the records of interest, which allows editing but not additions. Your suggestion is to open the form again (as a new record) and pass the ID for the Qualification Type as an open arg? And by implication then refresh the form to show all records of interest. When the frmQual is opened now I have to pass the Qualification Type to use as the value to select the records of interest.

 

[arnelgp]

without seeing your form, it's hard to guess.
but if the QualTypeId is a combobox, you can use the Not In list Event to add it table tluQual.

 

[ebs17]

Your suggestion is to open the form again

No, not mandatory.
The value to be passed should be known when the form is opened for the first time and thus already be passable.

The OpenArgs property retains its content throughout the entire runtime of the form instance (open form). It can therefore be used at some later point, but it can also be ignored if it is not needed.
I explicitly wrote about an event of own choice. This could be the buttonclick for unlocking for appending, or the Form_BeforeUpdate.

 

[GaP42]

Thanks Arnelgp. The form does not display the QualTypeID, just the associated descriptor, and it is not a combo as the value is expected to be tthe same for all records in the form (as they were selected on the basis of qualtypeID. (I do not want to add values to tluQual, just use the value used to select the records).
I'll follow through with Eberhard's suggestion in the first instance
Below if the rendering of the form and the partial design view: This is all dummy data.

 

[GaP42]

No, not mandatory.
The value to be passed should be known when the form is opened for the first time and thus already be passable.

The OpenArgs property retains its content throughout the entire runtime of the form instance (open form). It can therefore be used at some later point, but it can also be ignored if it is not needed.
I explicitly wrote about an event of own choice. This could be the buttonclick for unlocking for appending, or the Form_BeforeUpdate.

Thanks Eberhard, applied the technique and working fine to add add new records to any list of qualifications of a qualification type.