Count number of existing records while feeding data (1 Viewer)

[htadis]

Hi,

Table name :Imports
Field1 : ContainerNumber
Field2 : Bkg_number
Field3 : Size
Field4 : Weight

I have created a form which is having the above table as "control source". When data entering, if select a booking number from a Combo box, should checking how many records already having with same booking number and reflect the number of existing records in the form. If null records, it should show as "ZERO" records.

Would appreciate much, someone can assist on this.

brgds

 

[plog]

Put a text box on your form and for its Control Source you use a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php) that looks into your table and counts the relevant records.

 

[Minty]

On your form create an unbound text box. Call it txtBkCount.

Set the control source

 = DCount("Bkg_number", "Imports", "[Bkg_number] = " & txtBkg_number)

I have assumed that you Bkg_number is a number not a text field, and that its control on your form is call txtBkg_number

You may need to do a refresh on the unbound control in the on current property of the form depending on how things are set up.

edit: Bit slow...

 

[htadis]

Many thanks for the swift reply Plog. But, it returns total number of data in the record set. How can i get only the selected data. Can i put Combobox value as criteria. if yes, could u kindly show me how to put that.

 

[plog]

Minty did the heavy lifting for you. In his criteria he used txtBkg_number as the field on your form that holds that records Bkg_number.

 

[htadis]

Many thanks Minty. my Bkg_number is a text field. (sorry about not mentioned in the first place). have put as u mentioned above, but still getting an error msg. "You omited an operand or operator......."

 

[Minty]

That's because it is a text field. You need to enclose text criteria in single quote like this;

= DCount("Bkg_number", "Imports", "[Bkg_number] = [COLOR="Red"]'[/COLOR]" & txtBkg_number & "[COLOR="red"]'[/COLOR]")

 

[htadis]

Plog & Minty,

I don't know how to thank you two. After struggling days, at last done with your great support.

Many thanks again.



brgds