Setting RowSource in VBA for combobox on New Form

[08211987]

Hi,
I have 2 forms, one is an update form and the other is an identical form copied from the 1st form used to add new records. There are 2 forms due to less fields and security. The update form works fine but the new form does not. There is a combobox (Division_Name) whose RowSource depends on the value selected in another combobox (GMM_Name). In both forms the RowSource for (Division_Name) is as displayed below but on the new form the "enter parameter" dialog displays for the GMM_Name:

SELECT tbl_T133M_GMM_DIV_SDV.DIVISION FROM tbl_T133M_GMM_DIV_SDV WHERE
(((tbl_T133M_GMM_DIV_SDV.GMM_NAME)=[Forms]![form_New_PSSA_Tracker_Request]![GMM_Name])) GROUP BY tbl_T133M_GMM_DIV_SDV.DIVISION;

I tried Me!GMM_Name and Me.GMM_Name, I also saved the form first, I also tried inline SQL to set the RowSource. Below is my latest code:

Private Sub GMM_Name_AfterUpdate()

DoCmd.SetWarnings False
DoCmd.Save acForm, "form_NEW_PSSA_Request" 'saving form in case field not saved after entered
Debug.Print "GMM_Name = "; Me.GMM_Name 'displays correct name
Me.GMM_Name.Requery
Dim strSQL As String
strSQL = "SELECT tbl_T133M_GMM_DIV_SDV.DIVISION FROM tbl_T133M_GMM_DIV_SDV WHERE (((tbl_T133M_GMM_DIV_SDV.GMM_NAME)=[Forms]![form_New_PSSA_Tracker_Request]![GMM_Name])) GROUP BY tbl_T133M_GMM_DIV_SDV.DIVISION;
"
Me.Division_Name.RowSource = strSQL
Me.Division_Name.Requery
Me.Division_Name.SetFocus 'this is not even setting focus

End Sub

Appreciate any assistance!

 

[PHV]

What about this ?

Private Sub GMM_Name_AfterUpdate()
 Me!Division_Name.RowSource = "SELECT DISTINCT [DIVISION] FROM tbl_T133M_GMM_DIV_SDV WHERE GMM_NAME=' " & Me!GMM_Name & "'"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Thanks PH, I can get that to work in a query when I type it in SQL but when I click on the Division combobox dropdown it is blank, however I am no longer getting the "enter parameter" dialog. Do I need to do anything else but Me.Division_Name.Requery, should the Rowsource be blank behind control since I am doing it in code? I tried with it blank and I also copied the code you send there too and it is still blank.
Thanks.

 

[PHV]

It should be blank when no division exists for the given gmm_name ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Yes you're right but there are divisions. When I am controlling the RowSource in code should I do something else in the properties panel for the division field for Row Source?

Since I couldn't get this to work, I decided to use the same form and started the process of using a variable to check if the record is new to control differences between the existing and new records since the dropdowns are showing in that form.

Thanks for your assistance.