SubForm Problems (Access 2000 format)
I have a problem that's difficult to explain, so please be patient.
The database is in 2000 format, though I'm doing most of the designing in 2007 (saved as 2000 format, of course)
I have a continuous form frmEditConstruc tionLog with an unbound combobox that filters the results to the desired project number. This works fine.
I need to add the option of choosing multiple Crew Members, and this is where I'm stuck...
I cant use a subform because the form is in Continuous Form view (which is necessary). But all attempts to launch a form (popup) that will automatically insert the contents of the SurveyCrewLogID field into the subform fkSurveyCrewLog ID (*and* filter the subform to display only those IDs) have failed.
I have tried several methods. Only two have had any promise, but both had problems I was unable to solve...
First was a command button that would open a simple datasheet view subform (subfrmAddCrewT eamMember) with the SurveyCrewLogID field and AdditionalCrew fields. Problem was it will insert the SurveyCrewLogID into the first record from the main form, but if someone has to enter more than one person, the SurveyCrewLogID field remains empty. I need that field automatically populated and dont know how to do that.
The second method (the only other one that I had any success at) was a pop up form that used the subfrmAddCrewTe amMember as a subform. The problem I had with that is it only worked in Add mode (which didnt allow the user to see if there were any other names entered) and if someone clicked on it and got out without entering a name, it either entered a blank value or gave an error message (depending on whether I had made the field required). When I coded it to open in Edit mode, it didnt filter the subforms to the desired SurveyCrewLogID field.
I will post the code for both methods below. Hopefully, someone will be able to show me what I did wrong, or what I can do to correct the problem.
(I actually prefer the second method since it gives the user an overview of the record they're updating)
First Method:
Second Method:
If anyone is so kind as to help, please be patient. I know Access, but not VBA so you may need to explain with lots of details.
Thanks so much!
The database is in 2000 format, though I'm doing most of the designing in 2007 (saved as 2000 format, of course)
I have a continuous form frmEditConstruc tionLog with an unbound combobox that filters the results to the desired project number. This works fine.
I need to add the option of choosing multiple Crew Members, and this is where I'm stuck...
I cant use a subform because the form is in Continuous Form view (which is necessary). But all attempts to launch a form (popup) that will automatically insert the contents of the SurveyCrewLogID field into the subform fkSurveyCrewLog ID (*and* filter the subform to display only those IDs) have failed.
I have tried several methods. Only two have had any promise, but both had problems I was unable to solve...
First was a command button that would open a simple datasheet view subform (subfrmAddCrewT eamMember) with the SurveyCrewLogID field and AdditionalCrew fields. Problem was it will insert the SurveyCrewLogID into the first record from the main form, but if someone has to enter more than one person, the SurveyCrewLogID field remains empty. I need that field automatically populated and dont know how to do that.
The second method (the only other one that I had any success at) was a pop up form that used the subfrmAddCrewTe amMember as a subform. The problem I had with that is it only worked in Add mode (which didnt allow the user to see if there were any other names entered) and if someone clicked on it and got out without entering a name, it either entered a blank value or gave an error message (depending on whether I had made the field required). When I coded it to open in Edit mode, it didnt filter the subforms to the desired SurveyCrewLogID field.
I will post the code for both methods below. Hopefully, someone will be able to show me what I did wrong, or what I can do to correct the problem.
(I actually prefer the second method since it gives the user an overview of the record they're updating)
First Method:
Code:
Private Sub cmdAddCrew_Click()
On Error GoTo Err_cmdAddCrew_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "subfrm_SurveyCrewTeams"
stLinkCriteria = "[fkSurveyCrewLogID]=" & Me![SurveyCrewLogID]
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormEdit
Forms!subfrm_SurveyCrewTeams![fkSurveyCrewLogID] = Me![SurveyCrewLogID]
Exit_cmdAddCrew_Click:
Exit Sub
Err_cmdAddCrew_Click:
MsgBox Err.Description
Resume Exit_cmdAddCrew_Click
End Sub
Code:
Private Sub cmdAddCrewMembers_Click()
DoCmd.RunCommand acCmdSaveRecord
On Error GoTo Err_cmdAddCrewMembers_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_AddCrewTeamMembers"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdAddCrewMembers_Click:
Exit Sub
Err_cmdAddCrewMembers_Click:
MsgBox Err.Description
Resume Exit_cmdAddCrewMembers_Click
End Sub
Thanks so much!
Comment