Urgent! - Need help in activating combo box

M

marie

Hi there,

I am a new Access 2000 user, so bear with me.
I am in the process of creating a form that would prompt a user to input
data to search for a record. In basic terms, the intent is to search by
account number and the expected result would include the following:
1- secondary acct numbers in which the primary acct number(s) is(are) tied
to and visa versa;
2 - the credit limit(s) for each of these secondary accts.

I have one table that shows all of the above required data - primary a/c,
secondary a/c's, credit limits per secondary a/c's. This is a table linked to
an oracle_db.

I've created 2 combo boxes in the form to allow the user to search by
primary a/c and/or secondary a/c. Keep in mind that not all SECONDARY a/c's
have a primary a/c tied to it.

The problem that I have now is that, once I've selected an account (either
the primary or secondary) from the drop down list, the button that I created
to pull the record does not:
1 - Pull the record
2 - Show the rest of the information that I need.

How can I go about doing this? I have a feeling that I need to change/update
something in the properties, but I am not sure why. I am not comfortable with
SQL either.

Thanks for your help.
 
K

Klatuu

Selecting an item from a Combo box does nothing more than make that item the
current value of the combo box. It does not affect the current record. What
you are trying to do is very common and once you understand the technique,
you will use it a lot.

First, the Combo boxes should be unbound. Using a bound combo for searching
causes problems. I woun't go into that now, but give you an overview of how
to do it.

Obviously, you already have a rowsource for the combo that will return your
primary account numbers, so we don't need to cover that. There are two other
things we need to cover. First, as I stated previously, the combo should be
unbound. You will want to use a text box for the bound control of the
primary account. This text box should be Visible = No and Tab Stop = No.
To show the secondary accounts, you will want a row source for the second
combo that is filtered on the value in the combo for the primary account so
that when you select a primary account, only secondary accounts for that
primary will be in the second combo's list. You accomplish this by
requerying second combo in the first combo's After Update event:

Me.SecondAccountCombo.Requery

Then to make the selected record the current record, you need something like
the following code in the second combo's After Update event. It will locate
the record and make it the current record:

Dim rst as Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[Account] = '" & Me.PrimaryAccountCombo & "' And
[SubAccount] = '" & Me.SecondaryAccountCombo & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
 
Top