Subform referencing 1

[08211987]

I know there are a lot of posts on subforms I tried doing all the suggestions but for some reason my code does not work. Appreciate any help:

I have a form with a subform datasheet. The subform recordsource is tbl_MDV_SDV_PI_CONTACT. The fields in the subform are MDV_NBR, MDV_DES_TXT, MSD_NBR, MSD_DES_TXT, PI_Contact_Name (combobox). The only field that is editable is the combobox. The dropdown’s ROWSOURCE is:
SELECT DISTINCT tbl_MDV_SDV_PI_CONTACT.PI_Contact_Name FROM tbl_MDV_SDV_PI_CONTACT GROUP BY tbl_MDV_SDV_PI_CONTACT.PI_Contact_Name
ORDER BY tbl_MDV_SDV_PI_CONTACT.PI_Contact_Name;

PI_Contact_Name is either selected from dropdown or a name is added and the name updated in the table. The other fields cannot be updated.

My events are:
Private Sub PI_Contact_Name_AfterUpdate()
PI_Contact_Name.Requery
End Sub

Private Sub PI_Contact_Name_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
intAnswer = MsgBox("Add " & NewData & " to the list of PI Contacts?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
Response = acDataErrAdded
DoCmd.RunSQL "Update tbl_MDV_SDV_PI_Contact " & _
"Set PI_Contact_Name = Me!subform_Update_PI_Contacts.Form!PI_Contact_Name " & _
"Where MDV_NBR = Me!subform_Update_PI_Contacts.Form!MDV_NBR and " & _
"MSD_NBR = Me!subform_Update_PI_Contacts.Form!MSD_NBR"
Else
Response = acDataErrDisplay ' Require the user to select
End If
End Sub

I get a dialog to enter the Me!subform_Update_PI_Contacts.Form!PI_Contact_Name and all the other Me. fields. I have tried coding the main form name also in the SQL but still gets the prompt. What should the syntax be for the subform field names in the SQL for PI_Contact_Name, MDV_NBR, MSD_NBR?

Also, if I manually enter those values I get a write conflict during the REQUERY that another user has changed the fields, to save record, copy to clipboard or drop changes so I’m thinking I need to save the updates before requery, what would that syntax be?

Thanks so much for anyone’s help.

 

[08211987]

I'm sorry, I guess I wasn't clear about that in my message 9 Apr 13 14:22.
Thanks this worked wonderfully, thanks for all who responded!

 

[MajP]

BTW, there are many different techniques you could use to do this
1. Change the link master/child (as demoed)
2. Build a filter string and set that on the subform
3. Reference the controls in the query criteria
4. Change the query in the source object
...
Also you could change the functionality.
Now it only filters on one of the three combos. But you could make it filter (select records that match any, or select records that match all) of 1 or more combos.

 

[dhookom]

I almost exclusively use changing the SQL of the Record Source property since inevitably one or more of the filters involves a range such as a date range. Ranges do not work with Master/Child properties.

Duane
Hook'D on Access
MS Access MVP

 

[08211987]

Good info from both - thanks!