On Current event

[Lori]

I have an unbound field (Vendor_Count) on a subform. I am trying to write
code to count the number of records fitting the criteria for each record
displayed. I tried the following code as an On Current event on the subform
but it doesn't work. It is returning ALL records retrieved by the query (the
source of the subform), instead of just the count for the current record
displayed on the subform. Any ideas? Thanks!

Vendor_Count = DCount("*", "qry_Zip_Code_Vendors", "[Status_Code] = 'Active'
and [Peer_Group_Code] <> 11 ")

 

[Jezzepi]

Hello Lori,

What is the key that relates the current record to the table that you are
trying to get a count from?

Example:
Table1
tbl1RecID
tbl1OtherData

Table2
tbl2RecID
tbl2_R_tbl1RecID
tbl2OtherData

Private Sub Form_Current()
Dim lRecords as Long

lRecords = DCount("tbl2_R_tbl1RecID", "Table2", "tbl2_R_tbl1RecID = " &
Me.tbl1RecID & " AND tbl2OtherData = Something")

Me.MyTextBox = lRecords

End Sub

I hope this answers your question.

Best

J.

 

[Jezzepi]

Something else...

The on current event should be used at the parent form/record level. So
when you are on the current parent record of Table1 records, your desired
count will be related to that record. You can place your display anywhere on
either of the two forms. If you place it on the subform then you will assign
the value of your result as follows:

Me.MySubForm!Form.MyTextBox = lRecords

 

[Lori]

The parent form is based on a table called tbl_Vendor - the subform is based
on a query called qry_Zip_Code_Vendors. The key that links these two tables
is zip_code and local_agency_code. The subform lists all active vendors with
a zip code, local agency record. The code below is what I placed at the On
Current event of the subform. Somehow I need to filter within the query to
let the count apply only to the zip code record I am currently on in the
form. Are you saying the code needs to be at the OnCurrent event of the
parent form? I am getting confused on how to write the code to link the
tables and include the criteria included below.

The code I have: Vendor_Count = DCount"*", "qry_Zip_Code_Vendors",
"[Status_Code] = 'Active'

and [Peer_Group_Code] <> 11 ")

Something else...

The on current event should be used at the parent form/record level. So
when you are on the current parent record of Table1 records, your desired
count will be related to that record. You can place your display anywhere on
either of the two forms. If you place it on the subform then you will assign
the value of your result as follows:

Me.MySubForm!Form.MyTextBox = lRecords

I have an unbound field (Vendor_Count) on a subform. I am trying to write
code to count the number of records fitting the criteria for each record
displayed. I tried the following code as an On Current event on the subform
but it doesn't work. It is returning ALL records retrieved by the query (the
source of the subform), instead of just the count for the current record
displayed on the subform. Any ideas? Thanks!

Vendor_Count = DCount("*", "qry_Zip_Code_Vendors", "[Status_Code] = 'Active'
and [Peer_Group_Code] <> 11 ")

 

[Jezzepi]

Hi Lori,

The event choice for your DCount is fine. First make sure that your
criteria matches your datatype. If Status_Code is a string then your
statement is fine. If Peer_Group_Code is a number and not a string then your
statement is fine.


Dim lVenderCount as Long

If(Not IsNull(Me.zip_code)) then
lVenderCount = DCount("zip_code", "qry_Zip_Code_Vendors",
"Status_Code = 'Active' AND Peer_Group_Code <> 11" AND zip_code = " & Chr(34)
& Me.zip_code & Chr(34))
End If

Me.VenderCountTextBox = lVenderCount


Note that you should count one field item (i.e. zip_code), not "*". Next
note that you should also include the zip_code of your current record in your
criteria expression. Assumably zip_code is a string, so you will want to
include the character code function Chr(34) for quotations around the field
variable in your criteria. This should work for you. Feel free to let me
know if you need more help. In fact, let me know the status of your
situation after you try this.

Best

J.

The parent form is based on a table called tbl_Vendor - the subform is based
on a query called qry_Zip_Code_Vendors. The key that links these two tables
is zip_code and local_agency_code. The subform lists all active vendors with
a zip code, local agency record. The code below is what I placed at the On
Current event of the subform. Somehow I need to filter within the query to
let the count apply only to the zip code record I am currently on in the
form. Are you saying the code needs to be at the OnCurrent event of the
parent form? I am getting confused on how to write the code to link the
tables and include the criteria included below.

The code I have: Vendor_Count = DCount"*", "qry_Zip_Code_Vendors",
"[Status_Code] = 'Active'

and [Peer_Group_Code] <> 11 ")

Something else...

The on current event should be used at the parent form/record level. So
when you are on the current parent record of Table1 records, your desired
count will be related to that record. You can place your display anywhere on
either of the two forms. If you place it on the subform then you will assign
the value of your result as follows:

Me.MySubForm!Form.MyTextBox = lRecords

I have an unbound field (Vendor_Count) on a subform. I am trying to write
code to count the number of records fitting the criteria for each record
displayed. I tried the following code as an On Current event on the subform
but it doesn't work. It is returning ALL records retrieved by the query (the
source of the subform), instead of just the count for the current record
displayed on the subform. Any ideas? Thanks!

Vendor_Count = DCount("*", "qry_Zip_Code_Vendors", "[Status_Code] = 'Active'
and [Peer_Group_Code] <> 11 ")

 

[Jezzepi]

Lori,

Here is something else that just occurred to me. If you are trying to
populate a text box that resides within your list of records, you will see
the same value in every record. The value will change for each record that
you move to, but again will be the same down the list. You will want to be
sure to place the count value text box in the form header/footer.

The parent form is based on a table called tbl_Vendor - the subform is based
on a query called qry_Zip_Code_Vendors. The key that links these two tables
is zip_code and local_agency_code. The subform lists all active vendors with
a zip code, local agency record. The code below is what I placed at the On
Current event of the subform. Somehow I need to filter within the query to
let the count apply only to the zip code record I am currently on in the
form. Are you saying the code needs to be at the OnCurrent event of the
parent form? I am getting confused on how to write the code to link the
tables and include the criteria included below.

The code I have: Vendor_Count = DCount"*", "qry_Zip_Code_Vendors",
"[Status_Code] = 'Active'

and [Peer_Group_Code] <> 11 ")

Something else...

The on current event should be used at the parent form/record level. So
when you are on the current parent record of Table1 records, your desired
count will be related to that record. You can place your display anywhere on
either of the two forms. If you place it on the subform then you will assign
the value of your result as follows:

Me.MySubForm!Form.MyTextBox = lRecords

I have an unbound field (Vendor_Count) on a subform. I am trying to write
code to count the number of records fitting the criteria for each record
displayed. I tried the following code as an On Current event on the subform
but it doesn't work. It is returning ALL records retrieved by the query (the
source of the subform), instead of just the count for the current record
displayed on the subform. Any ideas? Thanks!

Vendor_Count = DCount("*", "qry_Zip_Code_Vendors", "[Status_Code] = 'Active'
and [Peer_Group_Code] <> 11 ")