Beginner - basic question

[kevinnice]

Access 2007


In a form,In VBA code I want to set Text30 to a field from a table
tablename: agents
key: agentID
name: agent_name

In essence : Text30.value= agent_name where agentID=2

Can someone please show me the syntax for this?

Thank you

[jzwp11]

Can you provide a little more information on what tables are involved in your database and on what table the form is based. I am guessing that you do not need any VBA code, but I cannot say for sure without further info.

[kevinnice]

Yes, I simplified the question hoping for a simple answer!
Here it is: I have a DB with 9 tables.
On this particular form I use data from the table BOOKINGS.
I want to show the total bookings by agent, of which there are 7.
My form has 7 text boxes for the agent names and 7 more text boxes for the amounts.
I can calculate the amounts using code - that's working.
But I now have 7 agents codes from the bookings table and I want to display their names from the agents table.
So text30 will display agent_name 1
text31 will display agent_name 2
and so on.
The two tables are linked by agentID

I am learning how to code VBA, so I 'd prefer an answer in that form. But any answer will be much appreciated.

[jzwp11]

Actually it sounds like a query will give you the data more efficiently & more simply than using VBA. You would just base the form on the query.

I have no clue what your table structure looks like, but something along these lines will give you the agent name and the sum of their bookings.
SELECT agentID, agentname, sum(bookingamount)
FROM tblAgent INNER JOIN tblBooking ON tblAgent.agentID=tblBooking.agentID
GROUP BY agentID

You could also use a combo box on your form tied to the agentID in order to display the agent name or you could use a Dlookup() function to get the name. You could also use the DSum() function to do the summing for you.

With respect to VBA, I'm not sure from what event the code should be triggered.