Passing data to subform help

[jeffrey159]

Hi, i need help passing data inside a form where there's a subform
1 Listbox in mainform
1 button in mainform to trigger the code to pass the data
1 listbox in subform

After pressing the button, it must pass the selected value that i click in the listbox to the subform's listbox. is there a code for this?

does openargs work in this? because the subform is already inside the mainform which doesn't require to be opened.

 

[Pat Hartman]

Subforms have multiple rows. How would you know what row your wanted to update?

 

[jeffrey159]

Subforms have multiple rows. How would you know what row your wanted to update?

it doesn't need to be updated, i just want to pass it to the subform that's all

in the subform there's only one listbox

i just want to pass the listbox value that i selected and when i click on a button, it will pass the selected item into the subform's listbox

 

[RainLover]

It is unclear what you are attempting to do.

Do you have any records in the sub form.

Can you explain in simple English what your objectives are. E.g. what are you going to use this list box for.

Where do you get the data to populate each List Box.

A pic of your Relationships may also help.

 

[Pat Hartman]

Normally, if the subform needs something from the mainform, the subform references the control or field in the mainform rather than the other way around.

 

[jeffrey159]

It is unclear what you are attempting to do.

Do you have any records in the sub form.

Can you explain in simple English what your objectives are. E.g. what are you going to use this list box for.

Where do you get the data to populate each List Box.

A pic of your Relationships may also help.

sorry because it's kind of confusing for me too, i don't know exactly what it wants.

Option 1: from mainform's listbox(Unbound, queried from different tables with no relations) and when i select a few rows from the listbox and click a on a button, it will pass this item selected rows to the subform's listbox(should i call it append or insert into the tables?)

i tried this code taken from microsoft but it just pass only the first column data on a textbox, which is not what i want:banghead:

If Me!NamesList.ItemsSelected.Count <> 0 Then
For Each oItem In Me!NamesList.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!mySelections.Value = sTemp
End Sub

and my teacher say use the WHERE condition to pass the value? is it right?

 

[Cronk]

I think people are struggling to help you do your school project because you are not saying what you want, not what "it" wants.

Maybe what you want, is to select a record in the list box (or is it a combo box?) and use that to filter the records in the list box in the sub form.

Is that what you are looking for?

 

[jeffrey159]

I think people are struggling to help you do your school project because you are not saying what you want, not what "it" wants.

Maybe what you want, is to select a record in the list box (or is it a combo box?) and use that to filter the records in the list box in the sub form.

Is that what you are looking for?

ok, sorry let me rephrase.
it's a listbox, and no i don't not want to filter the records in the list box in the subform, what i want is to select a record in the list box which i've queried from and pass the record that i select to the sub-form's listbox. that's all..

 

[RainLover]

I still cannot understand what is going on.

I asked some simple questions and they were not fully answered. So I can't help sorry.

As another thought a Pic of your Form SubForm may help.

 

[jeffrey159]

I still cannot understand what is going on.

I asked some simple questions and they were not fully answered. So I can't help sorry.

As another thought a Pic of your Form SubForm may help.

i've already simplified what i said..:banghead:
which part do you not understand?
what i want is to pass the records i select to the subform's listbox.

i just want the code to pass the records.... but i can't seem to find it from google.

Everything in the form is UNBOUND and is QUERIED from different tables with NO relationships.

 

[RainLover]

Have you tried an Append query.

 

[RainLover]

If the others who have read this are like me then the biggest problem is why do you want to do this.

I can see no practical reason.

 

[Pat Hartman]

Now that I see the picture, I think I understand what you are trying to do. You are trying to select a person from the list of all people and assign him to a drug test. The subform should not be showing a listbox. The subform should be showing individual records so each time you select a person from the mainform listbox, a new row gets added to the subform.

The following code is from an application that implements a drawing log. The form shows two listboxes "all drawings for job" and "drawings selected for this transmittal". Your situation is a little different in that you will have only a single listbox so that is the only one you need to requery. If you want records to "disappear" from it as you add them to the subform, the selection criteria for the listbox needs to exclude "assigned" people.

Private Sub cmdAddSelected_Click()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim strSQLupdate As String

   On Error GoTo cmdAddSelected_Click_Error

    If CheckAuthorization(Left(Me.Job, 2)) = True Then
    Else
        Exit Sub
    End If
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    'Add drawings to a transmittal
    Dim i As Variant

    If IsDate(Me.txtSentDT) Then
        MsgBox "Transmittal is complete when sent date is populated.  No more items may be added.", vbOKOnly
        Exit Sub
    End If
    Set db = CurrentDb()
    Set td = db.TableDefs!tblTransmittalDetail
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)

    For Each i In Me.lstDrawings.ItemsSelected
        rs.AddNew
            rs!RevID = Me.lstDrawings.Column(0, i)
            'update resubmitind on drawing rec if necessary
            If Me.lstDrawings.Column(6, i) = "Yes" Then
                strSQLupdate = "UPDATE tblDrawings INNER JOIN tblRevisions ON tblDrawings.DrawingID = tblRevisions.DrawingID "
                strSQLupdate = strSQLupdate & " SET tblDrawings.ResubmitInd = False "
                strSQLupdate = strSQLupdate & " WHERE tblRevisions.RevID = " & rs!RevID & ";"
                DoCmd.RunSQL strSQLupdate
            End If
            rs!TransmittalID = Me.TransmittalID
            rs!ReturnDT = Null
            rs!UpdateDT = Now()
            rs!UpdateBy = Environ("UserName")
        rs.Update
    Next i
    
    Me.lstDrawings.Requery
    Me.lstTransmittal.Requery
    Me.txtCountAvailable.Requery
    Me.txtCountSelected.Requery

cmdAddSelected_Click_Exit:
   Exit Sub

cmdAddSelected_Click_Error:
    Select Case Err.Number
        Case 3021
            Resume cmdAddSelected_Click_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddSelected_Click of VBA Document Form_frmCreateTransmittal"
    End Select
    
End Sub