Dupilcate Count in Form (1 Viewer)

[mab9]

Hi,

I currently have form that based on a branch number (in the header), the detail pulls in a series of pog's. I'm looking for a way to use dcount to check for any duplicates. I've tried using:

=DCount("POG_ID","tbl_POG","POG_ID = '" & Forms!qry_load_branch!pog_id & "'")

..but each line only then dcounts up to one. It seems like its only comparing the value in a record against the record itself instead of the whole form. Any ideas?

 

[ajetrumpet]

you don't have the field surrounded in brackets...I wonder if that makes a difference??

= DCount ("[POG_ID]", "tbl_POG", "POG_ID = Forms!qry_load_branch!pog_id")

..but each line only then dcounts up to one. It seems like its only comparing the value in a record against the record itself instead of the whole form.

What do you mean??

 

[mab9]

..but each line only then dcounts up to one. It seems like its only comparing the value in a record against the record itself instead of the whole form.

Let me try again. The form is opened based on a branch selection & the selected branch number is shown in the form header. In the detail of the form, combo boxes are used to select pog_id's. I'm trying to use dcount to count the number of time each selected id is chosen. I've attached a screenshot of what's happening. If the same id is selected on three different records, the dcount is returning 1 on each line. I want it to return 3 which would serve as a flag that the id has been duped.

 

[ajetrumpet]

1) IF...

** Your combos are all BOUND (have a controlsource of) to the field POG_ID in the table OR
** Your combos have a Rowsource that pulls records from the field POG_ID, and are NOT cascaded

THEN...the POG_ID in your screenshot is not present in duplicate records.

2) ELSE IF...

** The POG_ID combos are cascaded (filtered) based on the "Location" value in the text boxes

THEN...the POG_ID value is present in duplicate records, and your DCount function is not returning a correct value.

Be sure that everything is working correctly before you assume that the function is the culprit. "DCount" is built-in to the Access program, so chances are, it is working just fine.

END IF

 

[mab9]

IF...

** Your combos are all BOUND (have a controlsource of) to the field POG_ID in the table OR
** Your combos have a Rowsource that pulls records from the field POG_ID, and are NOT cascaded

THEN...the POG_ID in your screenshot is not present in duplicate records.

The pog_id combo is bound to the pog_id in the table. On the same note, the list of pog_id that show up in the combo is restricted to certain id's based on the location. In the table, the PK is a combination of branch + location. It seems like with the same id selected 3 times, it shouldn't return 1?

 

[ajetrumpet]

What is the Rowsource of each box??

Recordsource of the Form??

** Try using [] around the field name in the DCount function instead of just " "

 

[mab9]

The recordsource of the form is a query that pulls up the available locations & selected pog_id's for a particular branch.

The rowsource of the pog_id combobox is a query that pulls up the available pog_id's that can be selected. Its based off a query because due to a few different types of locations, only certain pog_id's are available at each one. The combobox's controlsource puts the selected id back into the main table under the field pog_id.

I tried adding the [] into the dcount, and it doesn't do anything different.

 

[ajetrumpet]

The two statements in RED do not compliment each other....

The recordsource of the form is a query that pulls up the available locations & selected pog_id's for a particular branch. The rowsource of the pog_id combobox is a query that pulls up the available pog_id's that can be selected. Its based off a query because due to a few different types of locations, only certain pog_id's are available at each one. The combobox's controlsource puts the selected id back into the main table under the field pog_id.

A controlsource doesn't do this. That is not the purpose of the property. I'm pretty sure this is where the problem is lies, but you might have to fix a couple of issues in order to resolve your problem. Controlsources bind Form controls to the Form Recordset's FIELDS. It sounds like you are trying to bind the POG_ID combo box control to a field that is not part of the query from which the Form was created.

What are the fields in the Form's underlying query (recordset)?? What source table does that query pull from?? What does the controlsource of the box say??

If you are trying to set a controlsource for the POG_ID combo box that is NOT a field in the "Form's Source Recordset, you are bound to get errors....

 

[mab9]

Turns out I was pointing to the wrong table in the dcount formula. Got it mixed up bouncing from various versions of the file. Nevermind!