Returning query value in VBA

[pretoria]

Hi,
I'm trying something in VBA and I'm not sure if its possible.
I'm trying to add an event before insert and set a counter field to a value result of a query (LastCounterValue) and add on 1. Is this possible? Additionally, the query must be run on the current form Cust_FK field. How would I combine this function into the code?


' Insert value result of LastCounterValue Query and add 1
Me.Counter = [Query].[LastCounterValue].[Counter] + 1

 

[pretoria]

I received notice from someone that I don't need to use a query in vba. Perhaps someone can give me some insight

I have in my COMLOG table

Cust_FK Counter
1_______ 1
1_______ 2
1_______ 3
1_______ 4
2_______ 1
2_______ 2
2_______ 3

The result I want to return for Cust_FK=1 is 5 (filter on Cust_FK, which is 1... look up max value, which is 4... and add 1)

How would I go about this in vba?

 

[pretoria]

problem solved. for those of you interested.
Me.Counter = Nz(DMax("Counter","COMLOG","Cust_FK=" & Me.Cust_FK),0) + 1