Text Boxes

[uplink600]

I have a table that contains customer details such as Account Number, Account Name & Phone Number etc etc.

On a form I want to create text boxes to display/change the information. In the first text box (this will possibly be a combo), the user will enter or select the Account Number and I would like all the other boxes to autofill with the corresponding details for the account entererd.

Can you please advise the easiest way. On my spreadsheets I use the
VLookup function but not sure about Access.

Thanks

VC

 

[ecniv]

Well, depending on the amounts of data you are looking at it may be best to have a search field or two at the top, a list box of results to click on and the form data entry/update at the bottom.

The user would then search via account number or name (possibly fuzzy match/wildcard). This produces a reduced list to look at which the user then selects the correct customer. Once selected the lower half of the form is completed with data; the id is held in a variable or in a textbox (invisible) and the mode set to edit. Your choice whether to bind it or fill it via code (I prefer the latter).

Then the user can click on the save button to store changes.
A reset button can be used (set the held editting id to -1) to enter new details (id of -1).

Just some options, ideas you may want to play with.

Anadvantage of not binding is that the tables are not linked to the back end (list box may be but I'm sure you can find a way around that ) so as you develop it won't hinder you.


Vince

 

[Mile-O]

Select the details you need into a query.

i.e.

SELECT AccountNumber, Customer, Address
FROM tblAccounts
ORDER By AccountNumber;

On your form put a combobox and change the following properties to suit: ColumnCount and ColumnWidths. Set the ColumnCount to the number of fields in your query and then set the ColumnWidths to 0 or 1 depending on what fields you want to show. If I was only showing the first field in the SQL above then I'd set the ColumnWidths to 0;1;1.

Ensure that the combo's RowSource is set to the query presented by the SQL. Ensure the query is a saved definition and not an SQL statement in the RowSource.

Now, with your textboxes you can refer to the relevant column by putting an expression in the ControlSource. If I wanted my textbox to refer to the Address field then I'd set the expression to be: =[MyCombo].[Column](2) - the account number is column 0.

There's no VBA required and every time the combo selection changes the textboxes update automatically.