FindFirst & FindNext

[murphyt]

I am working on a small project that has Client information stored on Access 97.
The problem is a Client may have more than one account number.
On the Data Entry Form I have a Combo Box ( Source Clent Name from dtaClient) and I want to be able to select Client and Account Number is entered into a label box.
I have written code before for One Client - One Account and the code is as follows ;

Private Sub cmboClient_Click()
Form1.dtaClient.Recordset.MoveFirst
Form1.dtaClient.Recordset.FindFirst "Client = '" & _
Trim(Form1.cmboClient.Text) & "'"
Form1.lblAccount = Form1.dtaClient.Recordset.Fields _
("Account").Value

This lists only the first Client Name and Account Number eg.
Name - Smith Account - 123
I want to be able to Click and the other accounts are listed as well eg.
Combo Box Account
Smith 123
Smith 456
Smith 789
I can't use the AddItem as Combo Source is from the Access Database Table Clients.

I tried using a cmd Button with FindNext but it does not pick up if more than two accounts and only works on first entry.

Hope someone can help.
Regards,
Trevor

[1 is a prime!]

I'm not sure what you mean exactly, but couldn't you do this:

Private Sub cmboClient_Click()
Dim FirstSearch As Boolean

FirstSearch=True
dtaClient.Refresh
Do
If FirstSearch=True Then
FirstSearch=False
dtaClient.Recordset.Findfirst etc...
Else
dtaClient.Recordset.Findnext etc...
Endif
If dtaClient.Recordset.Nomatch=True Then
Exit Do
Else
{FInd account number and add details to ComboBox}
Endif
Loop

Does this help?

Cheers,

Steve

[murphyt]

Sorry I was not clear enough.
The Combo Box is filled with the Client name from the database.
I want to be able to click on the Combo and the account number is entered into a Label Box.
The problem is if there is more than one Account Number.
eg.
Client named Smith who has say three account numbers , 123 , 456 , 789.
In the Combo it will list Smith three times , but I want to be able to click combo on each name and show different Account Number in the Label Box.
Smith 123 ( not correct account number & click second Smith and 456 shows in Label Box.)
Regards,
Trevor

[funkyd77]

It sounds to me like you want to do a join when you get the recordset - ie populate the recordset from a join sql query.

it might take a bit more effort but the basic steps could be like this:

i assume you have 2 tables of data, one for the clients and one for the accounts for each client.

like:

'table client might have
--------------------------
Client ID   | Name
--------------------------
1           | Smith
2           | Black
--------------------------

'table accounts might have
----------------------------
Client ID   | Account Number
----------------------------
1           | 123
1           | 456
1           | 789
2           | 987
2           | 725
----------------------------

Now, you want to declare a recordset object in code and populate the recordset with a join query <can't remember the exact syntax> but its something like:

"select client.name, accounts.[Account Number] FROM client, account WHERE client.[Client ID] = account.[Client ID]"

you would then have a recorset that would contain:

--------------------------
Name   | Account Number
--------------------------
Smith  | 123
Smith  | 456
Smith  | 789
Black  | 987
Black  | 725
--------------------------

then i would have a combo that is either bound to no recordset or bound to the recordset you just created.

actually - its probably better to bind the label and the combo to the above created recordset then as you change the name the new account number should come up...

hmm - i might try this theory and let you know because so far it's off the top of my head.

[JorgeLedo]

You just have to check if it is the first John Smith in the Combo if it is use the "Find First" if it isn't use the "FindNext" once for the second, twice for the third, and so on.

I would do it in a diferent way. I would use an SQL statement to make a recordset only with Jonh Snith's and the go trough the records to display the John Smtih you would like.