How to get automatic data in Acccess

Question

Hi. I need some help with Microsoft Access.

I'm working with a database which needs some work areas, each one with her own boss. I have two boards, one with the work areas and responsibles, and the other one with the data that I have to introduce. The data of last one board have to be introduced by a form; in that form, you can introduce your work area and automatically the name of the boss will appear. The fact is that, when I have to put more than one work area automatically appear just the name of the responsible of the first one, not of all areas I have choose before.

Have anybody know how can I solve it?

Thank you so much!

Answer 1

Hi,

To automatically display the name of the boss for each selected work area in your form, you can use a combination of query criteria and a lookup function.

Here's how you can do it-

1. Make sure your tables are properly linked: Make sure you have a relationship established between the "work areas and responsibles" table and the table storing the data you need to introduce. This relationship should be based on the work area field.
2. Create a query to retrieve the boss's name: Build a query that includes the "work areas and responsibles" table and joins it with the table containing the data you need to introduce. Include the boss's name field from the "work areas and responsibles" table in the query results.
3. Modify the form's record source: Open the form in design view and go to the form's properties. Set the record source of the form to the query you created in the previous step. This will ensure that the form retrieves the required data.
4. Add a combo box or list box control: In the form's design view, add a combo box or list box control where you want to select the work areas. Configure the control's row source to retrieve the list of available work areas from the "work areas and responsibles" table.
5. Set the control's properties: In the control's properties, set the control source to the field where you want to store the selected work areas in the data table.
6. Create an event procedure: Add an event procedure to the combo box or list box control's AfterUpdate event. In the event procedure, use the DLookup function to retrieve the boss's name based on the selected work area(s) and assign it to a text box control on the form.

Private Sub cboWorkAreas_AfterUpdate()
    Me.txtBossName.Value = DLookup("BossName", "YourQueryName", "WorkAreaID IN (" & Me.cboWorkAreas.Value & ")")
End Sub

Replace "cboWorkAreas" with the name of your combo box or list box control, "txtBossName" with the name of the text box control where you want to display the boss's name, "YourQueryName" with the name of the query you created, and "WorkAreaID" with the appropriate field name for the work area identifier.

I hope this works out well!

Best Regards.