By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

SubForm Problems (Access 2000 format)

P: 107
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddCrew_Click()
  2. On Error GoTo Err_cmdAddCrew_Click
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  7.     stDocName = "subfrm_SurveyCrewTeams"
  9.     stLinkCriteria = "[fkSurveyCrewLogID]=" & Me![SurveyCrewLogID]
  10.     DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormEdit
  12.     Forms!subfrm_SurveyCrewTeams![fkSurveyCrewLogID] = Me![SurveyCrewLogID]
  14. Exit_cmdAddCrew_Click:
  15.     Exit Sub
  17. Err_cmdAddCrew_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_cmdAddCrew_Click
  21. End Sub
Second Method:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddCrewMembers_Click()
  3. DoCmd.RunCommand acCmdSaveRecord
  5. On Error GoTo Err_cmdAddCrewMembers_Click
  7.     Dim stDocName As String
  8.     Dim stLinkCriteria As String
  10.     stDocName = "frm_AddCrewTeamMembers"
  11.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  13. Exit_cmdAddCrewMembers_Click:
  14.     Exit Sub
  16. Err_cmdAddCrewMembers_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_cmdAddCrewMembers_Click
  20. 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!
Nov 24 '08 #1
Share this Question
Share on Google+
12 Replies

Expert 100+
P: 1,287
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
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.
Nov 24 '08 #2

P: 107
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?

Nov 24 '08 #3

Expert 100+
P: 1,287
What exactly do you have and want to have in the new form you pop up?
Nov 24 '08 #4

P: 107
I'm putting together a response. Hope to have it up soon. Thanks!
Nov 25 '08 #5

P: 107
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!
Nov 25 '08 #6

Expert 100+
P: 1,287
Sorry, but I won't be able to look at this until tomorrow.
Nov 25 '08 #7

Expert 100+
P: 1,287
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.
Nov 26 '08 #8

P: 107

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.


Dec 4 '08 #9

P: 107
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!!
Dec 9 '08 #10

P: 107
Never mind, I think I got it working.


Dec 9 '08 #11

P: 107
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.


Dec 11 '08 #12

P: 107
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!

Dec 18 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.