continuous form not displaying all selected data in the detail section (1 Viewer)

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
hi,
access 2016
Ive a continuous form with 5 combo box, a search btn and a clear btn in the header. Detail form contain the text box for data display and footer blank.

After select of empl_name and click on search btn.
its displaying only 25 records instead of 32 records.

But when the query is run is get the 32 records for that empl_name.
Property of sheet attached.

Any idea??
 

Attachments

  • Property_sheet.docx
    33.7 KB · Views: 48

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
Why would you expect the query without filter criteria to display the same records as form with filter criteria?
 

psyc0tic1

Access Moron
Local time
Today, 17:24
Joined
Jul 10, 2017
Messages
360
What do the other 4 comboboxes do?
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
The combo contains a list of drop down values.
Select combo value and click search btn

Is there any limit of display in continuous form?
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
It seems that in the main table if one of the field used in the combo is null then the record is not being displayed in the form.

but if i put any data in the fields that used as combo.. then get record is displayed.

search cmd attached.

How can do it to display the record even if some fields are null via vba code as i will have multiple criteria for this search form??
 

Attachments

  • details.docx
    45.2 KB · Views: 43
Last edited:

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
Make sure there are no Nulls in data. In criteria expression, provide a value if field Is Null. If field is text, an empty string will serve

Nz(fieldname,"") LIKE ...
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
the Nz(fieldname,"") LIKE ... is ok.. :)
But im adding multiple combo box 6more and its getting limited character. Any of those 6 fields can be null


[Emp_name] Like "*" & [Forms]![FrmTestSearch]![Combo224] & "*" And (IsNull([comp_name]) OR Nz([comp_name]) Like "*" & [Forms]![FrmTestSearch]![Combo303])


How to do that via vba code pls? will then add it to the onclick event proc?
 

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
Possible VBA:

strCriteria = "[Emp_name] Like '*" & [Forms]![FrmTestSearch]![Combo224] & "*' And Nz([comp_name],'') Like '*" & [Forms]![FrmTestSearch]![Combo303]) & "'"
 
Last edited:

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
writing it like that but getting error..
"compile error: Expected:line number or label or statement or end of statement"

what i miss pls..


Dim strSearchCriteria As Variant
strSearchCriteria = Null
strSearchCriteria = "NZ([emp_name],"") Like " * " & [Forms]![FrmTestSearch]![Combo224] &"*'"
And NZ([comp_name],"") Like "*" & [Forms]![FrmTestSearch]![Combo303] & "*"
And NZ([Status],"") Like "*" & [Forms]![FrmTestSearch]![Combo311] & "*"
And NZ([type],"") Like "*" & [Forms]![FrmTestSearch]![Combo317] & "*"
And NZ([sign],"") Like "*" & [Forms]![FrmTestSearch]![Combo319]) & "'"

END​
 

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
Code:
Dim strSearchCriteria As String
strSearchCriteria = "NZ([emp_name],'') Like '*" & [Forms]![FrmTestSearch]![Combo224] & "*'" & _
" And NZ([comp_name],'') Like '*" & [Forms]![FrmTestSearch]![Combo303] & "*'" & _
" And NZ([Status],'') Like '*" & [Forms]![FrmTestSearch]![Combo311] & "*'" & _
" And NZ([type],'') Like '*" & [Forms]![FrmTestSearch]![Combo317] & "*'" & _
" And NZ([sign],'') Like '*" & [Forms]![FrmTestSearch]![Combo319]) & "*'"
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
no its not working..
can u pls chk attached file.
 

Attachments

  • test.accdb
    896 KB · Views: 44

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
The criteria in the Filter property is not using Nz(). The VBA code builds the filter criteria then does nothing with it.

In the Filter property:
Nz([Emp_name],"") Like "*" & Forms!FrmTestSearch![Combo224] & "*" And Nz([comp_name],"") Like "*" & Forms!FrmTestSearch![Combo303] & "*" And Nz([Status],"") Like "*" & Forms!FrmTestSearch![Combo311] & "*" And Nz([Type],"") Like "*" & Forms!FrmTestSearch![Combo317]

In VBA:
Private Sub Command365_Click()
Me.Requery
End Sub

Set the FilterOnLoad property to Yes.
 
Last edited:

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
can u pls amend the testdb..
it still not wrking
 

June7

AWF VIP
Local time
Today, 14:24
Joined
Mar 9, 2014
Messages
5,423
Very simple edits to database. If you follow the instructions for the 3 changes, should work.

Copy/paste.

Exactly what do you mean by 'still not working' because works for me.
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
The filter property and filter on loade to be added in search btn or form property?
Vba code added on click event proc for search btn
As whn I click on search it not doing anything...
 

akika

Registered User.
Local time
Today, 15:24
Joined
Aug 7, 2018
Messages
102
ppfff.. yes its working.. :)
Thanks a lots June7
 

Users who are viewing this thread

Top Bottom