SubForm Problems (Access 2000 format)

[truthlover]

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 frmEditConstructionLog 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 fkSurveyCrewLogID (*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 (subfrmAddCrewTeamMember) 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 subfrmAddCrewTeamMember 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:

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

Second Method:

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

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!

[ChipR]

I'll give you another option here. This is an example straight from my application with errorhandling removed for brevity. It uses the OpenArgs argument of the OpenForm.

--View Projects-- form
Private Sub EditProjectButton_Click()
If ProjectNumberCombo.ListIndex < 0 Then
ProjectNumberCombo = ProjectNumberCombo.Column(0, _
CurrentPage.Caption - 1)
End If
DoCmd.OpenForm "Edit Project", , , , , , ProjectNumberCombo
End Sub

--Edit Project-- form
Private Sub Form_Open(Cancel As Integer)
Dim strProjectNumber As String
If IsNull(Me.OpenArgs) Then
MsgBox "No project specified."
DoCmd.Close acForm, "Edit Project"
Exit Sub
Else
strProjectNumber = Me.OpenArgs
End If

'only 1 record should be available to the form
Me.Filter = "ARDECProjectNumber = """ & strProjectNumber & """"
Me.FilterOn = True

'initialize fields
...

Also, you can string together OpenArgs like
DoCmd.OpenForm "Edit Project", , , , , , string1 & " , " & string2 & "," string3

then in Form_Open

dim strArgs() as String
strArgs = Split(Me.OpenArgs, ",")
Box1 = strArgs(0)
Box2 = strArgs(1)
Box3 = strArgs(2)

Hope that helps.

[truthlover]

Hi Chip

Sounds like that could work. Only problem is I'm really not good with VBA and though it seems simple enough, I have no idea where to put the code.

Can you help?

Thanks!

[ChipR]

What exactly do you have and want to have in the new form you pop up?

[truthlover]

@ChipR
I'm putting together a response. Hope to have it up soon. Thanks!

[truthlover]

@ChipR
The best way for me to explain it is to show you the application. (I only included the necessary forms, tables and queries). This will also allow you to see all the corresponding code.

I tried to upload the file, but it said it exceeded the size allowed by the size of the file, not even one MB. (??) So I uploaded it to my site. You can access it here.

Anyway, the file will open to the main form (for purpose of this post). The "Additional Crew" button is the first method I referred to. That one's self explanatory. I want the pop up to filter and display only the corresponding SurveyCrewLogID, and that each time the user advances, that SurveyCrewLogID is automatically generated.

The "Add Crew Member" button is the second, and preferred method. It's not as obvious what the problem is at first, until you click the button for several different SurveyCrewLogID's. You'll see when you do, that the SurveyCrewLogID and Crew Member follows you from one record to the next. (That's what happens when I have the form open in Edit mode).

If, however, I have the form open in Add mode, it works properly, but the user can't use the button to see who's already been entered.

What I need to happen is that the subform be properly filtered to only display entries to the cooresponding SurveyCrewLogID. If there are no entries, it should show only the field with the * allowing the user to enter a name. If there are entries, it should show that name (with matching SurveyCrewLogID and the field with the * for entering an additional crew memeber.

I hope this will explain what I'm after.

Thanks so much for your time!

[ChipR]

Sorry, but I won't be able to look at this until tomorrow.

[ChipR]

Your query is joining on the tbl_QuerySurveyTeams and coming up with very few records. First, change your query and get rid of the tbl_QuerySurveyTeams because that's handled by the subform. Add the tbl_SurveyCrewLog.SurveyCrewLogID field to the query. That's the field which you'll filter the form on and use as the master field to the subform. The child field is the fkSurveyCrewLogID.

So in your frm_EditConstructionLog:cmdAddCrewMember_Click() do
DoCmd.OpenForm "frm_SurveyCrewTeams", , , "SurveyCrewLogID = " _
& SurveyCrewLogID

Then on the subfrm_SurveyCrewTeams change the Link Master Fields to SurveyCrewLogID and remove that filter on the data tab.

Should work.

[truthlover]

Chip,

I did see this, but it's a little hard for me to decipher (Sorry. I tried to warn you I might need things really spelled out).

I've got some things happening at work that I need to take care of first, but I'm hoping some time next week to tackle this again.

Thanks!

@ChipR

[truthlover]

Hi Chip

I hope you're still around...

I got the first part, but I dont understand what you mean by

Then on the subfrm_SurveyCrewTeams change the Link Master Fields to SurveyCrewLogID and remove that filter on the data tab.

Can you explain a little further?

Thanks for your help and your patience!!

[truthlover]

Never mind, I think I got it working.

Thanks!!

@truthlover

[truthlover]

I was wrong, I thought it was working, but I'm still having problems. As soon as I can, I'll post the problems I'm still having.

Thanks!

@truthlover

[truthlover]

Just an update ... the problem hasnt been resolved, but the people who are using it decided they wanted to use a different process anyway, so the form is no longer needed.

Thanks for your help.

Merry Christmas everyone!


@truthlover