Access Form Helps

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form (tblClientTransactions) which has a list of records for the
times that we spent on clients. The form has client ID, client name,
employee #, hours, etc. On the form, I want the users be able to change
client ID and to populate the client name automatically, vice versa. I have
the client ID set up as a combo box.

I tried the main form and sub-form by linking Client table and
tblClientTransactions. It comes up only the transactions associate with that
client ID (or client name). I want it to be able to list all the
transactions on the form and make changes.

Thanks.
 
Sorry, it just doesn't work that way. Subforms are for showing child records
to the parent record in the main form.
As far as changing clients, any time you change a vaule in the form, you are
changing the data in the field bound to the control you changed the value in
for the current record. You have to do some action that will update the
current record, then have some method for selecting the record you want to
edit.
 
you don't need to store the client's name in tblClientTransactions - it's
already stored in your Clients table. storing the client's name in multiple
tables violates database normalization rules, and creates real problems in
maintaining accurate data. what if the client's name changes (companies
change their names, persons get married or divorced, people entering the
data make typos). if you store the client's name in more than one location,
you have to track down every instance of that name and change it if you want
your data to be accurate and consistent.

you're using a relational database, so use it relationally. store the
client's name once, in the Client table. you can base a data entry form on
tblClientTransactions, with two combo boxes that are both bound to the
ClientID field in that table (remember, *remove* the client name field from
tblClientTransactions). in one combo box, set the RowSource as
SELECT ClientID FROM tblClients;
in the other combobox, set the RowSource as
SELECT ClientID, ClientName FROM tblClients;
set the ColumnCount to 2. set the ColumnWidths to 0"; 1.5"
in both comboboxes, the BoundColumn is 1 (the default setting).

so one combobox will show the ClientID, and the other combobox will show the
ClientName. since both comboboxes are bound to the same field, when you
change data in one it will automatically show the change in the other.

note: in the SELECT statement above, substitute the correct table and field
names, of course.

hth
 
Back
Top