Fields update

hi all,

i have a form where there are fields like name, id (staff_table), Accountno (staff_Account_Table), branch code (Branch_Table) etc. they are from 3 different tables.

the form will have the branch code empty where the user will key in the branch code. i want the branch code and branch name to be reflected /updated into my account table once the user entered the branch code which means that the branch name will be updated automatically.

how can i do that???

pls advice
nono

Use an unbounded form

Use VB Code

When you click Update

Look to see if you branch already exists in the table then add or edit the record as needed.

I would suggest you use a combobox to have the user select an existing branch from the table and allow them to enter a branch that doesn't exist.


S-

hi, the is the whole scenario:

in my branchtable, i have :
- branchCode (pk)
- branchName

in my staff_table, i have : (these are the fields that will be on my main form)
- staff id
- name
- contact no

in my staff_account_table , i have : (these are the fields in my subform)
- accountno
- account type
- branchcode (link to branchtable - fk)
- salary
- staffid (link to staff_table- fk)

i have another form that displays all the information on this staff when they select from the combo box where there is no branch code included. the form include fields of :
- staffid
- name
- contactno
- branchName (from branchtable)
- accountno
- account type
- salary

how can i display the all this information in my form. how can i link all these 3 tables to display the info??

this is what i have done: under the record source of my form, i place this codings

SELECT staff_Table.staffid, staff_Table.name, staff_Table.[contact no], staff_account_Table.[account no], staff_account_Table.[account type] , staff_account_Table.[salary] FROM staff_Table INNER JOIN staff_Account_Table ON staff_Table.staffid = staff_Account_Table.staffid WHERE (((staff_Table.staffid)=Forms!frmSearch!cboSelectG roup));

how can i display the branch name???

pls advice
nono

I have included you DB back. Look at "quryMaster" in design view

You will have to add the "Where" portion

S-

hi,

the method u taught me, i have tried but cannot work.
i have attached a db here for your reference. pls guide me along.

how can i display all the fields in the form?

pls advice
nono

You should rethink about how you editing your customer profile. You data structure is set up to have more then one account per customer and you form would only show one account per customer.
You really should set it up in a form - sub form enviroment (If you like bound forms) or another way altogether (mine preference, no bound forms).
Think about it.

S-

hi,

i know what u mean and that is the reason why i have place the name, nric and the account no in the combo box for user to choose from. from here they can differentiate the name under which accountno and perform the search.

this is however the user requirement and hope that u can give me some guidelines.

pls advice
nono

hi all,

the problem is solved by using DLookup.
Your help is greatly appreciated.

Cheers!
nono