read values from subform and input to field on form 1

[jmbcreative]

I have information show up in a subform on my main form when I select a value from my combo box on my main form. I need information from the fields in the subform to input into a field on my main form when I select a value in another combo box. How can I do this?

I am using the following code, but for some reason it is not working.

Private Sub Combo42_Click()
If Combo42.Value = "Relocate" Then
Dim relocateMsg2 As Integer
relocateMsg2 = MsgBox("Move all Components?", vbYesNoCancel + vbQuestion, "Test Message")
If relocateMsg2 = 6 Then
Me.ADDHardware.Value = Form_HardwareListcomponentsQuerysubform.ControlID.Value
ElseIf testMsg2 = 7 Then

Else

End If


End If
End Sub

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Are you missing a Form?
Me.[Subform Control Name].Form.ControlID.Value

How to refer to a control on a subform or subreport in Access 2000

http://support.microsoft.com/?kbid=209099

 

[jmbcreative]

Thanks for the link on the windows support site. Unfortunately I am still having issues. I have updated to the below syntax, yet I am still unable to return the values.

Private Sub Combo42_Click()
If Combo42.Value = "Relocate" Then
Dim relocateMsg2 As Integer
relocateMsg2 = MsgBox("Move all Components?", vbYesNoCancel + vbQuestion, "Test Message")
If relocateMsg2 = 6 Then
Me.ADDHardware.Value = Forms![Form_ChangeLogEntry]![Form_HardwareListcomponentsQuerysubform].Form![ControlID].Value

ElseIf testMsg2 = 7 Then

Else

End If


End If
End Sub

In adition, I may need to concate the values, since there are multiple values per control on the subform.

 

[Remou]

You could try dots. The advantage is that a dot should return the name of your control.
[tt]Me.[Subform Control Name].Form![ControlID].Value[/tt]
It is important that you use the name of the subform control, not the form contained. These are usually the same, but not always.

 

[PHV]

Tip: use the expression builder (loaded forms) to discover the correct syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmbcreative]

Thanks PHV and Remou! I used both the expression builder and the article from microsoft to derive the correct syntax listed below.

Private Sub Combo42_Click()
If Combo42.Value = "Relocate" Then
Dim relocateMsg2 As Integer
relocateMsg2 = MsgBox("Move all Components?", vbYesNoCancel + vbQuestion, "Test Message")
If relocateMsg2 = 6 Then
Me.ADDHardware.Value = Form_ChangeLogEntry.HardwareList_components_Query_subform.Form![ControlID].Value

ElseIf testMsg2 = 7 Then

Else

End If


End If
End Sub

Many Thanks.

Joe

 

[jmbcreative]

So, now I have the values from the subform inputing to a field on my main form. The problem is, I have multiple values in my subform and I need all of those values to show up on my main form. For example, for each record in my main form, several records show up in my subform because they are linked as a child objects. I need all of to grab values from all of those records in the subform.

How can I do this?

 

[Remou]

I am beginning to wonder about the design of your database. Why would you need to grab values, when a query should work as well?

 

[jmbcreative]

My database is being used to maintain a hardware list. The Main form that I am discussing here is used to submit changes for review. My main Hardware list table has every piece of hardware on inventory. Some of pieces are systems (CPU's) and some of these are components (hard drive, monitors) Each system has components that coincide with it and a identified by the controlID. Each system has a controlID that is only a number (325). each component that coincides with the system has the same controlID number followed by a letter (325a, 325b).

For the purposes here, when a request to relocate the system is made I have a message box that asks the user if they want to include all of the components with the request. If yes, I need the controlID's and serial numbers of all of the components to show up in the "description" field of the main form. Due to limited knowledge, I have a hidden subform that is linked to the systems name as child objects. So, when a systems is selected the corresponding components show up on the subform. Those are the values that I need to input into the main form.

Hope that helps to understand my ultimate need here.

 

[Remou]

Yes, thank you. How about:

Dim rs As Recordset
Dim strDesc As String
Set rs = Me.Form_HardwareListcomponentsQuerysubform.Form.RecordsetClone
Do While Not rs.EOF
    strDesc = strDesc & rs![i]Field1[/i] & " " & rs![i]Field2[/i] & vbCrLf
    rs.MoveNext
Loop
Me.Description = strDesc

End Sub

 

[jmbcreative]

BADA-FREAKIN BING!!

Thanks mate! That did tha trick.