Urgent! - Need help in activating combo box

[Guest]

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.

 

[Larry Linson]

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.

The devil's in the details, and your post is short on specifics and details,
though it gives a very good a description of what you hope to accomplish.
Also, can you describe what you mean by "pull the record" and "show the rest
of the information?"

What I'd expect to do in such a case is to take the information entered (or
selected*) by the user, and in VBA code, construct an SQL statement (or the
WHERE clause), which I would use to open a Form or a Report... the SQL
statement could be set as the RecordSource of the Form or Report, or the
WHERE clause could be used as the Filter or WhereCondition argument of a
DoCmd.OpenForm or DoCmd.OpenReport statement in the VBA code behind the
Command Button.

* "selected" -- because you haven't said anything about adding
new accounts, it is likely that you can use a Query for the
Row Source of a Combo Box control that will include the
valid account numbers, so that the user can start typing an
account number and the dropdown list will scroll to the next
nearest match; saves users mistyping an account number and
wasting time on fruitless searches.

Larry Linson
Microsoft Access MVP

 

[Guest]

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