Form Combobox Dependencies, and how they Populate a Table

[kristen.h24]

Hello,



I am fairly new to Acces, and I have two problems that I am not quite sure how to solve.

I have a database for logging time at work. There is a form (Work Hour Submission) that employees use to submit each time entry for the day, and once submitted, the entries are input into a table (Work Hours) with everyone's time entries for the fiscal year. (I am aware that spaces in names are not the best idea, but this was my first Access project and it is up and running at my job, so I don't feel like I can easily change them now- perhaps in the future I can work on this.)

So far, everything populates into the Work Hours table exactly as it should, except for one thing. I have two comboboxes in the form that are connected- the first is for a project's title (Funding Source) that an employee would be working on, and the second is for that project's billing code (Funding Code). We (my boss and I) did not want employees to have to worry about knowing each project's funding code, so I set up the form to auto-populate the code when the project's title is selected. It works great in the form. However, in the table I am not sure how to have the Funding Source and the Funding Code both present, because of the way I had to set up the dependent comboboxes in the form. The Row Source for both comboboxes is exactly the same- the Funding Source and the Funding Code from a separate table (Budget Codes), and the combobox for Funding Source has a 2 in Column Count but will display only Column 1, and the one for Funding Source has a 2 in Column Count but will display only Column 2. An on change sub event was created so when the Funding Source changes, the Funding Code will change to the Funding Source's secomd column.

Private Sub cboFndSrc_Change()
Me.cboFndCd.Value = Me.cboFndSrc.Column(1)
End Sub

But it seems to only work when the Control Source for both comboboxes in the form is the same field in the Work Hours table, (Funding Source) which means that I don't know how to have both fields (Funding Source and Funding Code) show up in the table, and therefore in any reports that are created from the table, at least as far as I know. That is the first problem.

The second problem is about those connected comboboxes. I originally had the project's title and funding code all show up in the same field, but I have since learned that sometimes we will need to go back in and change the funding code that a project is billed to- when funding has run out for a project but it still needs to be worked on, or when a project changes hands, etc. So I was asked to keep the funding codes separate from the project titles, but I was hoping to still connect them at least initially so the funding code that is originally supposed to go to a project will pop up and then could be easily altered if necessary. Is there a way to keep the private sub I have now (which makes the Funding Code automatically change when the Funding Source changes) but not allow the Funding Source to change if the Funding Code changes? Ideally, an employee would be able to select a Funding Source from the dropdown list in the form, then that project's original Funding Code would pop up in the next dropdown list, but the employee (or more likely I) would be able to change the Funding Code if needed without the Funding Source changing. Is this even possible? Like, a second private sub for cboFndCd on change that locks the cboFndSrc from changing, or something similar? It would be fine if I could change this in the table instead of the form, if needed, since it will most likely be me changing any funding codes when they do need to be changed.

Thanks in advance for any advice, and I apologize for the length of this post!

[June7]

What do you mean by 'project's title and funding code all show up in the same field'? How can these data be in the same field?

Code can populate the Funding Code field only if it is still Null.

If IsNull([Funding Code) Then
Me.cboFndCd.Value = Me.cboFndSrc.Column(1)
End If

Recommend AfterUpdate event instead of Change.

[kristen.h24]

I originally had a query that displayed the project title, then a few spaces, then the code that went with it, kind of like concatenate. This way on reports, etc. you could see the title and code. Now, I will need to go back and add the extra combobox to reports too, once I get it working right.

Thanks for your help! I will try this code....