VBA Access: DCOUNT to find number of records contain specific text

kjacob318

New Member
Joined
Dec 22, 2015
Messages
13
Hello,

I've been trying to debug for days with no luck. I am trying to write a conditional statement to evaluate "if my text is already in the field, then exit sub, if not, then add".

Below is my code. Everything I'd tried doesn't seem to work. I've even tried to do DLookup to see if it returned NULL.

Anything will be helpful!


Code:
    Dim db As Database
     Dim rs As DAO.Recordset
     Set db = CurrentDb()
     Set rs = db.OpenRecordset("Approvals")

Count_of_Text = DCount("[ApprovalID]", "Approvals", Me.ApprovalID)

If Count_of_Text > 0 Then 'Check to see if record already exists
    
    MsgBox "Approval already in database."
    
    Exit Sub
    
Else
     With rs 'Add record information
        .AddNew
        .Fields("ApprovalID") = Me.ApprovalID
        .Update
        .Close
     End With
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
What exactly is the problem?

Are you not getting a value returned from the DCount?

Are you getting errors?
 
Upvote 0
What exactly is the problem?

Are you not getting a value returned from the DCount?

Are you getting errors?

It seems that the DCOUNT is returning the count of total records in the dataset. What I want is to only count the records in field ApprovalID when it is equal to Me.ApprovalID . Both are text.
 
Upvote 0
Oops, bit late here.:eek:

Should have noticed the problem.

This,
Code:
Count_of_Text = DCount("[ApprovalID]", "Approvals", Me.ApprovalID)
should look something like this.
Code:
Count_of_Text = DCount("[ApprovalID]", "Approvals", "[ApprovalID]='" &  Me.ApprovalID & "'")
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top