Problem with a form query

[Guest]

Hello,

I am fairly new to Access VBA programming so please bear with me.

I am working on a form which has 2 combo boxes. The first cbo will display
1 of 2 possible company names which are selected from a lookup table. Each
company has a number of departments associated with it. This reflects a
1-to-many relationship. When either company is selected, the 2nd cbo becomes
active. I need this 2nd cbo to display the results of a query which should
be executed when the company is selected. This query should basically be:

Select Department from LkTbl
where form.company = LkTbl.Company;

I have created this as a query. How do I attach this query? Should the
query be the rowsource for the Dept. cbo or something else?

TIA,
Rich

 

[Marshall Barton]

I am working on a form which has 2 combo boxes. The first cbo will display
1 of 2 possible company names which are selected from a lookup table. Each
company has a number of departments associated with it. This reflects a
1-to-many relationship. When either company is selected, the 2nd cbo becomes
active. I need this 2nd cbo to display the results of a query which should
be executed when the company is selected. This query should basically be:

Select Department from LkTbl
where form.company = LkTbl.Company;

I have created this as a query. How do I attach this query? Should the
query be the rowsource for the Dept. cbo or something else?

Assuming you have the details of the query correct (using
Forms!theform.company), then the company combo box's
AfterUpdate event procedure needs to execute
Me.deptcombo.Requery.

If your form does not filter for a single record, then you
will need the same line in the form's Current event.

 

[Guest]

Thanks for the reply Marsh but I don't understand the syntax you used
(Forms!theform.company)

Is it basically "Forms", then the ! as a seperator character,
then the name of my form, ".", then form name?

Adding to my confusion, when I execute the query directly, it prompts me for
a value then returns the results perfectly.

What is the "Current" event? I see it but don't know what this event is.

Thanks,
Rich

 

[Marshall Barton]

The standard syntax for referring to a control on a form
from outside the form is:
Forms
refers to the collection of open forms

!
indicates the following is a member of
the collection

[form name]
the name of the open form

..
indicates the following is the name of a
property (including controls) or method
of the form

[control name]
the name of the property or method

The [ ] are only needed if the name contains a
non-alphanumeric character. Many people never use names
with a space or punctuation character so they don't need to
use the [ ]

You will be prompted for any term that Access can not
resolve within the context of the query. Make a note of
the prompt, it is the term that you mistyped.

The Current event is triggered whenever a record in the
form's record source becomes the current record (via user
navigation, etc).

These are all very basic concepts and your questions about
them are a clear indication that you need to to some
homework by taking a class or reading a book on how to use
Access for something beyond data entry.