Results 1 to 3 of 3

Thread: MS Access Form ComboBox Data

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007

    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: 50
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
    Join Date
    Dec 2004

    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

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007

    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.

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