Using combo boxes to select name OR account number

[James Armstrong]

Hello,

This seems like it should be easy, but it has me ripping my hair out.

Basically, I'm constructing a small trade blotter for one of my
traders. It has two tables - one called tblAccounts (fields = AcctNum
(primary key), Shortname (a client code) and longname (full client
name)

The second table is called tblTrades and contains the following
fields: Date, Quantity, Price, Type (buy or sell), acctnum

There is a one to many relationship between tblAccounts.AcctNum and
tblTrades.Acctnum

Now, in my data entry form, I have 2 combo boxes - one can be used to
select the account number. The other can be used to select the
shortname. When one selects an account #, it works great - shortname
and longname are filled in automatically from the tblAccounts data.
However, when I try and choose a shortname instead of an
accountnumber, it does not pull the associated data from the table. In
fact, in a test DB I'm working with, selecting the shortname actually
updated the tblAccounts data, so I would up with incorrect data!

Boil the question down to this: How can you have 2 combo boxes live
together happily on a form - the goal is to be able to use EITHER
combo box to select and autofill the rest of the data. So, if I select
a shortname out of combo box 2, I want it to update the first combo
box with the account # of that shortname and vice versa. (account
numbers and shortnames are both unique, so there will be no dupes in
the tblAccounts data.)

Please help!!!

Thanks,

Jim

 

[Cheval]

The problem is that you have set the control source of the
combo box(s) to fields names, whereas they should be
unbound.

To go to the ShortName record or the AccNo record, put the
following code into the AfterUpdate event

Private Sub cboShortName_AfterUpdate()

Me.ShortName.Setfocus
DoCmd.FindRecord Me.cboShortName

End Sub

Private Sub cboAccNo_AfterUpdate()

Me.AccNum.Setfocus
DoCmd.FindRecord Me.cboAccNo

End Sub