Form combo boxes and other stuff....

[RageMatrix]

Hey all,

I''ve got a form with an unbound combo box and a bunch of data-bound text
boxes which get its data from a query. What I want to do is to initially
have no values in the controls at all until something is selected from the
combo box. I've used the wizard to set this up, but the text boxes all point
to the first record while the combo box default value is initially nothing,
which doesn't match up to the records.
What would be the best/easiest way to do this?

Also, when using forms, are data-bound controls generally frowned on or not?
Should I be using unbound controls and VBA instead to do this?

One last question, how do I manipulate database records using VBA? Any
decent pointers would be helpful.

Just as an aside, can anyone point me to some good web resources for Access
which are not totally newbie tutorials. Specifically, I'm looking for good
VBA / Form design resources.

Thanks for any help!

Jon.

 

[judgehopkins]

See thread702-558490

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[RageMatrix]

I don't see how that thread helps me, although thanks for replying. I'm not having trouble displaying related records via a combo box, but I want to have no records displayed in the controls when the form opens or if nothing is selected in the combo box.

Also, when using the record navigation controls of the form, I'd like the corresponding field to automatically be displayed as the current value of the combo box. As the wizard makes this an unbound combo box, how would I do this?

 

[judgehopkins]

Sorry, maybe I don't know what it is you want to do.

Try

http://support.microsoft.com/default.aspx?scid=kb;en-us;209576

Microsoft Knowledge Base Article - 209576

ACC2000: How to Create Synchronized Combo Boxes
The information in this article applies to:
Microsoft Access 2000

This article was previously published under Q209576
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).


SUMMARY
This article shows you how to create a combo box that is filtered to list only those items that are related to an item that you selected in a previous combo box.
MORE INFORMATION
Steps to Reproduce the Behavior
In the following example, the first combo box lists the category names from the Categories table, and the second combo box lists the product names from the Products table. When you select a category name in the first combo box, the second combo box is filtered to list only the product names for that category.
Open the sample database Northwind.mdb.
Create the following query based on the Categories table, and then save the query as qryCategoriesList.

Field: CategoryID CategoryName
Table: Categories Categories
Sort: Ascending
Show: <checked> <checked>
Criteria:
Or:

Create the following query based on the Products table, and then save the query as qryProductsList.

Field: ProductID ProductName CategoryID
Table: Products Products Products
Sort: Ascending Ascending
Show: <checked> <checked> <checked>
Criteria: IIF(IsNull([Forms]![frmSelector]![cboCategorySelect]), [CategoryID],[Forms]![frmSelector]![cboCategorySelect])
Or:

NOTE: The IIF() function within the criteria tests to see if the first combo box is null. If the combo box is null, the query uses all the CategoryIDs within the Products table as criteria, then returns all Products in the second combo box. If the control is not null, the CategoryID that appears on the form becomes the criteria for the query, and then returns the related Products.
Create a new form in Design view that is not based on any table nor any query.
From the View menu click Properties.
In the Property sheet, click the Event tab, and then click in the On Current check box.
Click Build next to the check box, click Code Builder, and then click OK.
Enter the following code:Me!cboProductSelect.Requery
Add two combo boxes as follows and then save the form as frmSelector: Combo Box 1:
-------------------------------
Name: cboCategorySelect
RowSourceType: Table/Query
RowSource: qryCategoriesList
ColumnCount: 2
ColumnWidths: 0&quot;;1&quot;
BoundColumn: 1

Combo Box 2:
-------------------------------
Name: cboProductSelect
RowSourceType: Table/Query
RowSource: qryProductsList
ColumnCount: 3
ColumnWidths: 0&quot;;1&quot;;0&quot;
BoundColumn: 1

Right-click the first combo box that is named cboCategorySelect, and then click Properties.
In the Property sheet, click the Event tab, and then click in the After Update box.
Click the Build button next to the check box, click Code Builder, and then click OK.
Enter the following code:Me!cboProductSelect.Requery
Me!cboProductSelect.SetFocus

Open the form in Form view, and then select a category from the first combo box. Note that the second combo box lists only the products that are related to the specific category.NOTE: Each time that you select a different category from the first combo box, the second combo box resets and then lists the appropriate products for the category that you choose.
REFERENCES
For more information about combo boxes, click Microsoft Access Help on the Help menu, type combo boxes: what they are and how they work in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about how to requery controls on a form, click Microsoft Access Help on the Help menu, type requery action in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about how to apply this concept to Access 2000 projects, click the article number below to view the article in the Microsoft Knowledge Base:
235359 ACC2000: Implementing Query-by-Form in an Access Project

For additional information about how to apply this concept to earlier versions of Access, click the article number below to view the article in the Microsoft Knowledge Base:
98660 ACC: How to Create Synchronized Combo Boxes



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.