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.

 

[vbajock]

Get those control calls out of your string or the SQL parser will take them as literal values:

"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 & "'"

 

[08211987]

Thanks for the quick response! I used your code and got an runtime 2465 "MS can't find the field 'subform_Update_PI_Contacts' referred in your expression, so I removed your single quotes (') but got the same error. The form does exist and is the correct name. I'm terrible with syntax....

Do I even need to have the sql if the subform's recordsource is the table I am trying to update? If not can you give me some idea of what this routine would look like?

Thanks again.

 

[vbajock]

You need the single quotes to process your SQL correctly.

Try this:

Dim ContactName as string

Contactname= Forms![PUT WHAT EVER YOUR MAIN FORM NAME IS HERE]![PUT WHAT EVER YOUR SUB FORM NAME IS HERE].FORM![PUT WHATEVER THE NAME OF YOUR CONTROL THAT HOLDS THE DATA HERE]
msgbox ContactName

What do you get?

 

[08211987]

I coded:
ContactName = Forms![frm_Update_PI_Contacts]![subform_Update_PI_Contacts].Form![PI_Contact_Name]
and it came back that it could not find 'subform_Update_PI_Contacts' in my expression. Sounds like it doesn't recognize the form? It is called that - here I've pasted the name: subform_Update_PI_Contacts.

This might be a silly question but could it be because I don't have anything in the Link Master Field or Child Fields in properties? I thought I could control that through code...

Thanks!

 

[vbajock]

Open the main form. Click on the subform, then choose Properties, and look at the name property, it is probably different from what the subform is actually named.

Parent/Child link is irrelevant.

 

[08211987]

I don't see a Name property in the Property Sheet, so the name that I saved it as and I see listed in the Access Objects is not the name property? Where do I find the Name property?
Thanks!

 

[08211987]

Wait I'm sorry, let me try something.....

 

[08211987]

Ok I guess I am still confused, after I moved the subform I could see in the Property Sheet a Name and a Source Object property. The name is set to the name of the parent form and the source object is set to the name I am referencing in the code and is the name that I see in the list of Access Objects. Which do I reference in the VBA - "vbajock", a name rightly used I might say! Thanks.

 

[08211987]

Can anyone tell me if the name of the subform that is listed the Access Objects on left of the Access Window is the name that is referenced in the VBA code or is that name hidden somewhere else? I'm having difficulty finding the Name property in the property sheet.
Thanks!

 

[MajP]

You have a subform control and in that is a source object.

So in this case if I was referencing a control within the subform from the parent form:

Me.sbfOrderDetails.Form.SomeControlName

the name of the form inside the subform control is
[Order Subform for Order Details] ' which is a horrible name

Notice that the outside subform is selected not the form inside. If the form is selected within the subform control you will get the form's properties.

 

[08211987]

Excellent - thanks so much for the clarity! Now I'm going to try to see how I can select a combox value on the main form and use it to filter the values to display in the subform. Should be fun!

 

[MajP]

Now I'm going to try to see how I can select a combox value on the main form and use it to filter the values to display in the subform

You can do that by using the Link Master Fields, Link Child Fields properties as shown in the image

Link Master Fields: [YourComboboxNameGoesHere]
Link Child Fields: [NameOfTheFieldToLink]

By doing this you do not have to write and code.

 

[08211987]

Thanks for the fast response! I have 3 combo fields in the main form combo_MDV_DES_TXT, combo_MSD_DES_TXT, combo_PI_Contact_Name that go after different lists of values and the subform has all three fields. I wanted to provide a different way of finding information. On the AfterUpdate event I tried the following 2 lines and they both come back with 2465 - application-defined or object-defined error so I'm sure that is not the correct syntax to modify those properties?

'Me!subform_New_Vendor_Tracker.Form.LinkMasterFields = Me.combo_MDV_DES_TXT
Forms!form_New_Vendor_Tracker!subform_New_Vendor_Tracker.Form.LinkMasterFields = Me.combo_MDV_DES_TXT

Then I'm not sure but I was thinking I might need to requery subform?
Me!subform_New_Vendor_Tracker.Form.Requery

 

[MajP]

It is a name of a control, it is not a value inside a control. Also the property belongs to the subform control and not the form inside the subform control
Me.subform_New_Vendor_Tracker.LinkMasterFields = "combo_MDV_DES_TXT

 

[08211987]

I'm sorry I don't follow. I have a main form with a combobox with a list of division names. Once a division is selected from the list I want the subform to populate with all records that contain that division. If I have the following values what would I code in the AfterUpdate event of the combobox (if that is the event I should code it in?

Main form: form_New_Vendor_Tracker
Main form combobox: combo_MDV_DES_TXT
Sub Form: subform_New_Vendor_Tracker
Sub Form Division field: Division_Name

Appreciate your help!

 

[MajP]

Once a division is selected from the list I want the subform to populate with all records that contain that division. If I have the following values what would I code in the AfterUpdate

Nothing. As I stated there is no code required. Did you read my post of 9 Apr 13:35? You just put it in the properties of the subform control. The subform control takes care of the filtering.


Link Master Fields: combo_MDV_DES_TXT
Link Child Fields: Division_Name

 

[08211987]

Yes I read it but since I have 3 combos on the main form and they all populate the same subform, the user can choose which combo to select so I'm thinking I will need to change the values of the Link Master Fields and Link Child Fields values depending on which combobox they select.

Link Master Fields: combo_MDV_DES_TXT
Link Master Fields: combo_MSD_DES_TXT
Link Master Fields: combo_PI_Contact_Name

Link Child Fields: Division_Name
Link Child Fields: SDV_Name
Link Child Fields: PI_Contact_Name

Make sense? Thanks.

 

[dhookom]

What started as something entirely different changed to:
"select a combox value on the main form and use it to filter the values to display in the subform"
Then changed to:
"I have 3 combo fields in the main form"
Then was modified to:
"the user can choose which combo to select"

If you are finished changing the specifications, I would use a little code to modify the Record Source property subform. This would run in the after update of each of the three combo box controls. The specific answer would depend on if you want the combo box controls to filter independently or combined.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Here is my example that works.
2 combos: CmboCustomer and cmboRegions
Subform control name is subfrmOrders with fields customerID and shipRegion

Private Sub cmboCustomer_AfterUpdate()
  With Me.subFrmOrders
    .LinkMasterFields = "cmboCustomer"
    .LinkChildFields = "CustomerID"
  End With
  Me.cmboRegion = Null
End Sub
Private Sub cmboRegion_AfterUpdate()
   With Me.subFrmOrders
    .LinkMasterFields = "cmboRegion"
    .LinkChildFields = "ShipRegion"
  End With
  Me.cmboCustomer = Null
End Sub