Help with VBA Code for retrieving data from a table in an MS Access Database
Hi,
I'm new around here. I'm a student in the programming field. I have a question for those of you with experience with VBA, VB, or SQL coding languages. Basicly what I'm looking to do with my database is have it fill in the remaining related fields in a form when the Primary key field is selected in the form. basicly I'd like to be able to load an employee form and pull up the EmployeeID using a look-up wizard and once the EmployeeID is selected have the form auto fill in the text boxes that contain the employee first name, last name, address, city, state, zip code, phone number, wages, wage type, etc. One other thing I would like to do is be able to pull up a Primary key field as a Foreign key in a form and have the form auto fill in the data from the table corresponding to the foreign key. (ex: Checkout table has PartNumber as a foreign key from Inventory Table, in the Checkout form I'd like to be able to pull up the part number via look-up wizard, then retrieve data like part name, part type, retail price, etc from the Inventory table rather than having to use a billion look-up wizards). I've had a little experience with VB in the past but not enough to know how to do this. Any help would be greatly appreciated. Thank you.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 30, 2005 12:59 AM
UPDATED:
March 30, 2005 9:29 AM
Answer Wiki:
u can create a query to ur employee table with the where clause mentioning ur selected employee id, and then binding the fields with the recorset.
also if u have the navigation buttons on ur form, u can use the find and move method of the recordset that binds to the navigation buttons, specifing ur selected employee id to the recordset.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi, If the form you mention is an MS Access form all you need to do is to set the Form’s “Record Source” property to the name of the table with the data of interest or better yet set the “Record Source” property to the name of a query based on that table. This makes the data available to the form and the controls on the form.
Now you can each appropriate control a bound control by setting its “Control Source” property to one of the fields in the table/query.
I don’t use the wizards but you will want to use an unbound control in your record selection. Typically this is a combo box with one or more columns that displays the list of available records. This lets the user select a record and then you can apply a filter to the form limiting the record source to this record. There certainly are many other ways to implement the record selection and maybe the wizard does this for you.
If the form you mentioned is a VB form you will probably want to use ADO.