Loop through a Table/Query and return the one or more values

[adi2011]

Loop through a Table and return the one or more values
Hello,

In my access database I have one simple table called "Transactions" like this with example data

http://img96.imageshack.us/img96/268...1220142554.jpg

and all that data will be pulled on specific way through one simple form called "Test form". It should all work similar to this

http://img818.imageshack.us/img818/1...1220140354.jpg

This is detailed explanation..

When user clicks on combo box CUSTOMER there is After update Event that will contain loop that will search all data in Table "Transactions" and then at the end show that customer "A" has two invoices no. '1' and '3'
In case user chooses customer "B" then subform will show result invoice no. '4' and etc.

Is this possible, what code I should use at After update Event of combo box CUSTOMER?

Any help is appreciated and many thanks in advance for prompt replies!

Cheers
Adi

​

 

[gemma-the-husky]

you do not need a loop

just a query that counts transactions for the selected customer

something like this

items = nz(dcount("*","tblTransactions","Customer = " & targetcustomer),0)

count all the records, in the table, where customer is equal to somecustomervalue
the nz just handles the case when no records are returned.

 

[adi2011]

Hello Dave,

Thank you very much for your help!

I basically understand what you want me to do and it is a great suggestion but I am not sure where exactly should I place this code?
I have just re-created database based on example and screenshots I have posted. Please see attached database.
I hope you can give me some pointers because I am stuck right now!

Thank you,
Adi

 

[adi2011]

Here's update of attachment.

Any suggestion is welcome!

All the best,
Adi

 

[stopher]

All you need to do to your current database is get the form to recalc each time the combo is changed....

In the On Change event of the combo, just enter the following:

me.recalc

That's it!

In case you don't know how to set up the On Change event, the go to the properties box for the combo and find the On Change property and click on it. Click on the three dots at the end of the line. Then choose Code Builder.

hth
Chris

 

[adi2011]

Hello,

Thank you very much for suggestion!

However, I have solved situation with this code

http://www.utteraccess.com/forum/Loop-Table-Return-t2001226.html

Thank you!
Adi