Queries for 2-field single table db

[TomH]

Hi all.

I've been away from Access for a long time now and am struggling with a simple concept. I have a simple table, with two simple accounting fields: Account and Number. The Number field will have unique values and I can use that as a key field. The Account field holds the names of accounts and are sometimes duplicated.

I want to have a simple form that will allow a user to type an account number into a control and have the corresponding account name show... AND I'd like them to be able to begin typing in an alphabetic account name, with autocomplete on, and have the possible accounts numbers reported back. I assume it will require two sets of controls, on the same form.

I appreciate any help, as I am now back to work after a long illness... and am discovering just how far one's skills deteriorate over time. THANKS!

Tom

 

[CJ_London]

Sounds like you need two controls

1) a combobox called cboAccountNo for the account name with the following properties:

ControlSource:AccountNo (this is field from the recordsoure from your form)
Rowsource: "SELECT AccountNo, AccountName From tblAccounts ORDER BY AccountName"
Rowsourcetype: table/query
BoundColumn:1
LimittoList:Yes
ColumnCount:2
ColumnWidths:0;3

2) an unbound textbox with its control source set to
=cboAccountNo

 

[TomH]

Hi CJ.

Thanks for the help. That seems to work great except it forces me to choose from the drop down or it thinks I'm adding a new record and stops me.

I've attached my simple database. Thanks again.

Tom

 

[CJ_London]

I think we need to clarify what it is you are actually trying to do. The code meets this requirement

allow a user to type an account number into a control and have the corresponding account name show

Which you might use in a journal form for example

Do you mean you want the user to be able to select an account which they can then view and edit?

If so then:
1. remove the control source from the combo box so it becomes unbound 2. Add a control source to the text box (looks like it should be numbers
3. in the combo afterupdate event put the following code

me.filter="numbers=" & combo2
me.filteron=true

Be aware that Number and Name are reserved words I apprecite this is a test but suggest you avoid them and their derivatives (e.g. Names) going forward. Use names which mean something such as AccountNo or AccountName