Invoking Tables/Fields (2nd posting)

[Heron]

I need to bill people monthly within a set period and within a set budget. My table (tB) has fields (M1 etc) each representing a month’s billing. Because the query coming from it uses a lot of itineration (carrying forward of balances, each with quite involved Iif statements, I keep getting the error message “Query too complex”. So I’ve given up and have to write the query in VBA.

I need to know how to bring the values of fields in tB, do the necessary, and display the results in a form with arrays of textboxes for each month of the year. I’ve tried using “Anna’s” suggestion (thanks Anna) as below but it doesn’t work. What is the command for getting a field? And, after completing the procedure, passing the results in other tables or queries?

Option Compare Database
Private Sub Form_Load()
Dim CurrentDb.TableDefs("tb").Fields("M1").Value as integer ‘January Input
Dim CurrentDb.TableDefs("tb").Fields("M2").Value as integer ‘February Input
……Repeat until Field M12 incl.
For fc = 1 To 12
M(fc)= TextBox M(fc)
Next M(fc)
End Sub

 

[Rich]

Why can't you use a totals query, group by month?

 

[Heron]

Thanks for responding. Believe me, any query I try to do for this gives me "Query too complex". Also this section is only a small part of what I need to do. And anyway, I need to know how to invoke tables in the future. So please, HOW do do you do it? I've 5 books on VBA but NONE tell me how.

 

[Heron]

PS to my reply to Rich. I realise it may read as a little tetchy.
The form I wish to produce contains a lot of calculations with many more lines of textboxes and more VBA. If I used a query just for this section I still have do a further query or another section and then another and then another all strung together. And, as I said, that gives me "Query is too complex".
Believe me, I have tried to do this every which way the conventional way.
So I do really need to bite the bullet and do it in VBA and need to know that magic line to get a field into my module.
Thanks for your patience and I appreciate all the time you, Rich and the others, donate to us newbies.

 

[Rich]

Give some more details ,how much data, how do you carry the balance foward , table set up etc..