Results 1 to 6 of 6

Thread: [RESOLVED] MS Access Form ComboBox Data

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Resolved [RESOLVED] MS Access Form ComboBox Data

    I have a request from a client and I am not very familiar with the Access data model, so I need some help. This Access database was created by a previous employee and I have been helping the client with some reporting and query changes but now they want me to change how a form works and the answer is not obvious to me. I believe the client is using the latest version of Office.

    I've attached an image showing the form as it is filled out and has a couple of records in it.

    Name:  FormComboBoxProgramming.jpg
Views: 237
Size:  20.0 KB

    The form Data Record Source is a table (TBLOfficeBankedHrs) in Access with Dynaset Recordset.

    The Employee ComboBox has a row source that is a SELECT statement from a query (the query is linked to an external database through an ODBC connection but the SELECT just pulls the Employee Number and Name from the external Master Employee table, through a query for display in the ComboBox).

    The way the form currently works is that the user will open the form, select a date (same date for each row), click in the Employee field to select the currently displayed employee or to move to the correct one, then enter the BankedHrs and notes. Moving to the next record will save that record in the underlying table. This all works but is not ideal for the user/owner of the company.

    What he would like is that for each row, the ComboBox will only show employee's that don't currently have a record for the specified date.

    In the attached image the user selects employee 00254 for the first record. Then on the second record, he would like 00254 not to show up.

    If the employee first displayed does not have any banked hours, he will move to select the next employee in the list that is applicable.

    When he gets to the third row, 00254 and 00227 should not show up in the list for row three but as you can see the display of the impending row 4 already has a previously selected employee showing (just happens to be that I selected that employee second in the image though the employee sorts first each time in the ComboBox).

    I would like to just set up the ComboBox to refresh the list each time a new row starts and only show employees that don't have a record for the date indicated on the first row. I tried adding a Refresh command in code but it refreshed the whole form so the previous records did not show. The user would still like to see the original records so he knows he has recorded the hours properly for validation purposes.

    Any ideas how to change this form or is there other information you may need?

    One approach was to link the Master Employee to the TBLOfficeBankedHrs table to pull only those active employees without a record for that date but I don't know how to pass the date from the form to the query before it populates the ComboBox when displaying the form. Is this the right approach or should I try something else.

    TIA rasinc.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: MS Access Form ComboBox Data

    from what you say, seems like the combobox should be linked to a separate query recordset to the rest of the form, not that i use access, only access databases
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: MS Access Form ComboBox Data

    Thanks westconn1.

    The combobox is set up with a query written into the Row Source field. I assigned the query directly and changed the query to prompt for a date the user could enter manually but it is still not refreshing the combobox data.

    The owner basically just doesn't want to cycle through all the employees on each record if they have already been entered in a previous one. I see the logic for speed of data entry but I just can't figure out how to get Access to do it.

    I know you said you don't use the interface of Access but if you or anyone else has any other ideas, I would appreciate it.

  4. #4
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Re: MS Access Form ComboBox Data

    Hello,@rasinac

    Follow this step,To MS Access Form ComboBox Data

    Step 1:-Right-click the form in the Navigation Pane, and then click Design View.

    Note: This procedure assumes that the form is bound to a table or query. Some of the steps will not apply if the form is unbound.To
    determine if the form is bound to a table or query, press F4 to display the property sheet. On the Data tab of the property sheet, the
    Record Source property box displays the table or query that the form is bound to.

    Step 2:-On the Design tab, in the Controls group, ensure that Use Control Wizards Button image is selected.

    Step 3:-Click either the List Box Button image tool or the Combo Box Button image tool.

    Step 4:-On the form, click where you want to place the list box or combo box.

    Depending on your choice, the List Box Wizard or the Combo Box Wizard starts.

    Step 5:When the wizard asks how you want to get the values for the control, do one of the following:

    If you want to display the current data from a record source, click I want the list box/combo box to look up the values in a table or query.

    If you want to display a fixed list of values that will seldom change, click I will type in the values that I want.

    If you want the control to perform a find operation, rather than serve as a data entry tool, click Find a record on my form based on the
    value I selected in my list box/combo box. This creates an unbound control with an embedded macro that performs a find operation based
    on the value the user enters.

    Step 6:-Follow the instructions for specifying how the values will appear.

    Step 7:-If you chose one of the first two options on the first page of the wizard, the wizard asks what you want Access to do when you select a value. Do one of the following:

    To create an unbound control, click Remember the value for later use. This means that Access will hold the selected value until the user
    changes it or closes the form, but it will not write the value to a table.

    To create a bound control, click Store that value in this field, and then select the field you want to bind the control to.

    Step 8:-Click Next and type a label for the control. This label will be displayed next to the control then click finish.

    I hope this information will be useful.
    Thank you.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: MS Access Form ComboBox Data

    Prahlad

    Thank you for your instructions. It helped me learn more about the user interface of Access and now I can more easily setup a Combobox with queries. I don't think the Listbox is a good option here because there are too many employees to display.

    I have also found it does not allow for refreshing of the Combobox list between records without refreshing the form. The client wants the list on the second record to remove the employee selected on the first record. The third record should remove the first two employees, etc.

    One requirement is to be able to keep all the rows on screen during data entry so the user can review and validate the data entry before completion. So I started testing the concepts of putting all employees on the form at one time. I programmed a couple of buttons and added a temp table to allow the user to review before adding the data to the final table. The form now shows all data in the temp table that has been loaded and allows the user to enter data for a particular day and for only the employees applicable.

    Thank you and westconn1 for your replies. I am going to mark this thread resolved even though I cannot find a way to update the current design, the new design will accomplish the task and give the user a smoother experience.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] MS Access Form ComboBox Data

    it would be possible to use a manually (by code) filled combobox then names can be removed by code

    initially have a query to get the names, then fill the combobox using additem rather than recordsource, then can remove names from combo as records are added

    i do not use bound controls and always work like this anyway
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width