I am using Access 2000 and have constructed a project logging system. ProjectNumber (the key field) exists in table ProjectMain. Two tables are linked to it by ProjectNumber in a one-to-many relationship: ProjectAssignment and ProjectActivity.
The activity logging process takes place in a mainform/subform. The mainform contains a txtProjectNumber (protected) in which the ProjectNumber chosen from a previous list screen is displayed along with other background project information. The subform, which is in the continuous forms state, displays prior logged activity (protected) and allows the entry of a new piece of activity only in the last record position. The record source for the mainform is the table ProjectMain and the record source for the subform is the table ProjectActivity.
The activity logging process involves populating a field called LoggedBy that resides on the subform and in the table ProjectActivity. When a new piece of activity is entered in the last record position, I populate LoggedBy using a three-column LoggedByCombobox that itself is populated with a concatenation of AssigneeLastName and AssigneeFirstName (called AssignedName in the 1st column), followed by AssigneeLastName in the 2nd column and AssigneeFirstName in the 3rd column. The combobox also resides in the subform and is loaded only with the names of those who are assigned to the given project number. The person entering the activity is expected to choose his or her name from the combobox and the concatenated form of their name then ends up in the LoggedBy field.
I have managed to successfully load the combobox using the following SELECT statement in the combobox’s RowSource property:
The problem I encounter is, the combobox won’t load automatically. When I click the combobox upon entering the screen, a pop-up appears stating, Enter Parameter Value. The parameter value for which it is looking is ProjectNumber. When I enter the project number and hit OK, the combobox then populates just fine.
I then did some TekTips research and discovered a VBA coding method in a post that I could possibly apply to my situation to eliminate the appearance of the parameter pop-up but still load my combobox. Here is the code that I initially applied to the MainForm Load event figuring that the subform loads first, then the mainform loads. (This was based on a post indicating the order of mainform/subform events is as follows:
Subform open, subform load, subform current, mainform open, mainform load, mainform activate, mainform current.)
Since I needed txtProjectNumber from the mainform and combobox from the subform, I figured the code wouldn’t work unless both forms were loaded.
The code abended with the following error message: “Can't find the field [Forms] referred to in your expression”. I got the same error when I placed the code in the mainform’s Load or Activate events. I double checked the names of all the fields involved in the Forms! statement and found them to be accurate.
So then I tried applying the same code to the txtProjectNumber control in each of the following three events: BeforeUpdate, AfterUpdate, and Enter. The Enter event gave me the same error as above, but the Before and After Update events did not produce an error. They resulted in the screen being brought up successfully, but the combobox was empty.
When I applied the same code to the analogous subform events, I received similar results.
After this long story, does anyone have any ideas on how I can resolve this problem? I suspect the communication issues between the mainform and subform may be too much to overcome amd I may have to tell the users to simply enter their LoggedBy names manually.
Thanks, Rooski
The activity logging process takes place in a mainform/subform. The mainform contains a txtProjectNumber (protected) in which the ProjectNumber chosen from a previous list screen is displayed along with other background project information. The subform, which is in the continuous forms state, displays prior logged activity (protected) and allows the entry of a new piece of activity only in the last record position. The record source for the mainform is the table ProjectMain and the record source for the subform is the table ProjectActivity.
The activity logging process involves populating a field called LoggedBy that resides on the subform and in the table ProjectActivity. When a new piece of activity is entered in the last record position, I populate LoggedBy using a three-column LoggedByCombobox that itself is populated with a concatenation of AssigneeLastName and AssigneeFirstName (called AssignedName in the 1st column), followed by AssigneeLastName in the 2nd column and AssigneeFirstName in the 3rd column. The combobox also resides in the subform and is loaded only with the names of those who are assigned to the given project number. The person entering the activity is expected to choose his or her name from the combobox and the concatenated form of their name then ends up in the LoggedBy field.
I have managed to successfully load the combobox using the following SELECT statement in the combobox’s RowSource property:
Code:
SELECT ([AssigneeLastName] & ", " & [AssigneeFirstName]) AS AssignedName, ProjectAssignment.[AssigneeLastName], ProjectAssignment.[AssigneeFirstName]
FROM ProjectAssignment
WHERE (((ProjectAssignment.ProjectNumber)=[me]![Forms]![frmAddProjActivityMain]![txtProjectNumber]))
ORDER BY ProjectAssignment.[AssigneeLastName], ProjectAssignment.[AssigneeFirstName];I then did some TekTips research and discovered a VBA coding method in a post that I could possibly apply to my situation to eliminate the appearance of the parameter pop-up but still load my combobox. Here is the code that I initially applied to the MainForm Load event figuring that the subform loads first, then the mainform loads. (This was based on a post indicating the order of mainform/subform events is as follows:
Subform open, subform load, subform current, mainform open, mainform load, mainform activate, mainform current.)
Since I needed txtProjectNumber from the mainform and combobox from the subform, I figured the code wouldn’t work unless both forms were loaded.
Code:
Private Sub Form_Load()
Me![Forms]![frmAddProjActivitySub]![cmbxProjectActivityLoggedBy].RowSource = "SELECT [AssigneeLastName] & ', ' & [AssigneeFirstName] AS AssignedName, ProjectAssignment.[AssigneeLastName], ProjectAssignment.[AssigneeFirstName] FROM ProjectAssignment WHERE ProjectAssignment.ProjectNumber = [Me]![Forms]![frmAddProjActivityMain]![txtProjectNumber] ORDER BY [AssigneeLastName], [AssigneeFirstName];"
End SubSo then I tried applying the same code to the txtProjectNumber control in each of the following three events: BeforeUpdate, AfterUpdate, and Enter. The Enter event gave me the same error as above, but the Before and After Update events did not produce an error. They resulted in the screen being brought up successfully, but the combobox was empty.
When I applied the same code to the analogous subform events, I received similar results.
After this long story, does anyone have any ideas on how I can resolve this problem? I suspect the communication issues between the mainform and subform may be too much to overcome amd I may have to tell the users to simply enter their LoggedBy names manually.
Thanks, Rooski
