How to have two dcount criteria in access VBA (1 Viewer)

luzz · Dec 12, 2017

Hi all, how can i include two dcount criteria in my search button on my form? Currently i am able to search the PO number, buy i would like to be able to search by the MR Name as well. How can i do that?
Below is my VBA code:

Code:

Private Sub txtSearch_Click()

    Dim sql As String
    If DCount("*", "MainTable", "mxdpo like '*" & Me.txtKeywords & "*'") = 0 Or DCount("*", "MainTable", "mrname like '*" & Me.txtKeywords & "*'") = 0 Then
    MsgBox "No record found,check your MXDPO again!"
    Else
    sql = "SELECT [MainTable].[Fabrication], MainTablee.Mill, MainTable.Width, MainTable.FinishedGoods, MainTable.Colour, MainTable.LabDipCode, MainTable.GrossWeight, MainTable.NettWeight, MainTable.Lbs, MainTable.Loss, MainTable.Yds, MainTable.Remarks, MainTable.POType, MainTable.ComboName, MainTable.GroundColour, MainTable.GLGPO, MainTable.FabricDelivery, MainTable.MXDPO, MainTable.MRName, MainTable.GLA, MainTable.StyleNo,MainTable.GarmentSketch, MainTable.GSMPerSqYd, MainTable.SampleRequirement, MainTable.PrintedRemarks, MainTable.Buyer, MaximMainTable.[Solid/Printing],MainTable.Reference,MainTable.GarmentDelDate,
MainTable.GarmentSketch2,MainTable.GarmentSketch3, MainTable.GarmentSketch4, MainTable.GarmentSketch5" _
    & " FROM MainTable " _
    & " WHERE MXDPO LIKE '*" & Me.txtKeywords & "*'" _
    & " OR WHERE MRName LIKE '*" & Me.txtKeywords & "*'"
    Me.FormMxdSub.Form.RecordSource = sql
    Me.FormMxdSub.Form.Requery
    
    End If
End Sub

Minty · Dec 12, 2017

So what doesn't work?

luzz · Dec 12, 2017

Minty said:
So what doesn't work?

I think is the second dcount which is the MR Name.

When i search the MR name in the textbox of my form, it shows the error message " No record found, Please check your mxdPO again"

Minty · Dec 12, 2017

Think about your logic flow.
If you don't enter a valid mxdpo then your first check evaluates to true.
If you don't enter a valid mrname then your second check evaluates to true.

So the way you have it you would have to enter a valid mxdpo AND mrname to get past your check.

luzz · Dec 12, 2017

Minty said:
Think about your logic flow.
If you don't enter a valid mxdpo then your first check evaluates to true.
If you don't enter a valid mrname then your second check evaluates to true.

So the way you have it you would have to enter a valid mxdpo AND mrname to get past your check.

I want it to be like user can either search the PO by mxdPo or mr name

Minty · Dec 12, 2017

I understand that -

Instead of using a dcounts simply validate that they have made an entry into the text boxes of some type then adjust your SQL accordingly.
Then test the recordset - Add something like this into your code to check the recordset (Air code untested)

Code:

Dim rs As Recordset
Set rs = currentdb.openrecordset sql 
   If rs.EOF or rs.BOF Then 
        msgbox "No records"
        rs.close
        Exit Sub
   End If 
Me.FormMxdSub.Form.RecordSource = sql

luzz · Dec 12, 2017

Minty said:
I understand that -

Instead of using a dcounts simply validate that they have made an entry into the text boxes of some type then adjust your SQL accordingly.
Then test the recordset - Add something like this into your code to check the recordset (Air code untested)

Code:

Dim rs As Recordset Set rs = currentdb.openrecordset sql If rs.EOF or rs.BOF Then msgbox "No records" rs.close Exit Sub End If Me.FormMxdSub.Form.RecordSource = sql

Okay, I will try! Thank you. Does this means that i should remove my dcount statement?

Minty · Dec 12, 2017

Yes - simply check they aren't null or empty string ("").
If they are don't include them in your SQL string, there's no point.
Then check that corrected SQL for returned records.

There is an excellent multi search form example here http://allenbrowne.com/ser-62.html

How to have two dcount criteria in access VBA (1 Viewer)

luzz

Registered User.

Minty

AWF VIP

luzz

Registered User.

Minty

AWF VIP

luzz

Registered User.

Minty

AWF VIP

luzz

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread