RunTime Error 3201 Multiselect List Box populate subform

H

heidii

Hello All:

I am struggling with populating my subform from a multiselect list
box.

Here is a brief overview of my form:

1. Main form - FRM-FERTAPPS
2. Multiselect list box is on main form
3. Multiselect list box data is populated with a text box from my
main form, LOTNUMBER
4. Subform - FRM-FERTAPPSBLOCKS
5. cmd button on main form with code to populate subform with blocks
selected in multiselect list box.

I can not get the code to populate mysubform. Recieve the "Run-Time
Error '3201' You cannot add or change a record because a realted
record is required in table 'TBL-FERTAPPS'

I have the master and child links set up correctly on my forms. How
can I Make my code below insert my records and also add the id field
from my main form like it does if I were to manually add the text by
typing.

Or am I just doing this the wrong way?

Private Sub Command24_Click()
Dim MyDB As DAO.Database
Dim frm As Form, ctl As Control
Dim varItm As Variant, i As Integer
Dim rstSelectedBlock As DAO.Recordset

Set MyDB = CurrentDb()
Set rstSelectedBlock = MyDB.OpenRecordset("TQRY-FERTAPPSBLOCKS",
DB_OPEN_DYNASET)
Set frm = Forms![frm-fertapps]
Set ctl = frm!BLOCKSELECTION
For Each varItm In ctl.ItemsSelected
rstSelectedBlock.AddNew
rstSelectedBlock!BLOCKDETAILID = ctl.Column(1, varItm)
rstSelectedBlock!BLOCKNAME = ctl.Column(2, varItm)
rstSelectedBlock!VARIETY = ctl.Column(3, varItm)
rstSelectedBlock!Acres = ctl.Column(4, varItm)
rstSelectedBlock.Update
Next varItm
For i = 0 To BLOCKSELECTION.ListCount - 1 'clears the selection
BLOCKSELECTION.Selected(i) = False
Next i
Me.Requery
End Sub

I am still new to VBA,

Thanks

heidi
 
H

heidii

Sorry for double posting, but I figured it out.

Here is what I did for anyone that has the same issue:

Private Sub Command24_Click()
Dim MyDB As DAO.Database
Dim frm As Form, ctl As Control
Dim varItm As Variant, i As Integer
Dim rstSelectedBlock As DAO.Recordset

Set MyDB = CurrentDb()
Set rstSelectedBlock = MyDB.OpenRecordset("TQRY-FERTAPPSBLOCKS",
DB_OPEN_DYNASET)
Set frm = Forms![frm-fertapps]
Set ctl = frm!BLOCKSELECTION
For Each varItm In ctl.ItemsSelected
rstSelectedBlock.AddNew
rstSelectedBlock!FERTID = me.fertid 'added this line to
add the unique key from my main form.
rstSelectedBlock!BLOCKDETAILID = ctl.Column(1, varItm)
rstSelectedBlock!BLOCKNAME = ctl.Column(2, varItm)
rstSelectedBlock!VARIETY = ctl.Column(3, varItm)
rstSelectedBlock!Acres = ctl.Column(4, varItm)
rstSelectedBlock.Update
Next varItm
For i = 0 To BLOCKSELECTION.ListCount - 1 'clears the selection
BLOCKSELECTION.Selected(i) = False
Next i
Me.Requery
End Sub




Hello All:

I am struggling with populating my subform from a multiselect list
box.

Here is a brief overview of my form:

1.  Main form - FRM-FERTAPPS
2.  Multiselect list box is on main form
3.  Multiselect list box data is populated with a text box from my
main form, LOTNUMBER
4.  Subform - FRM-FERTAPPSBLOCKS
5.  cmd button on main form with code to populate subform with blocks
selected in multiselect list box.

I can not get the code to populate mysubform.  Recieve the "Run-Time
Error '3201'  You cannot add or change a record because a realted
record is required in table 'TBL-FERTAPPS'

I have the master and child links set up correctly on my forms.  How
can I Make my code below insert my records and also add the id field
from my main form like it does if I were to manually add the text by
typing.

Or am I just doing this the wrong way?

Private Sub Command24_Click()
   Dim MyDB As DAO.Database
    Dim frm As Form, ctl As Control
    Dim varItm As Variant, i As Integer
    Dim rstSelectedBlock As DAO.Recordset

    Set MyDB = CurrentDb()
    Set rstSelectedBlock = MyDB.OpenRecordset("TQRY-FERTAPPSBLOCKS",
DB_OPEN_DYNASET)
    Set frm = Forms![frm-fertapps]
    Set ctl = frm!BLOCKSELECTION
    For Each varItm In ctl.ItemsSelected
        rstSelectedBlock.AddNew
        rstSelectedBlock!BLOCKDETAILID = ctl.Column(1, varItm)
        rstSelectedBlock!BLOCKNAME = ctl.Column(2, varItm)
        rstSelectedBlock!VARIETY = ctl.Column(3, varItm)
        rstSelectedBlock!Acres = ctl.Column(4, varItm)
        rstSelectedBlock.Update
    Next varItm
    For i = 0 To BLOCKSELECTION.ListCount - 1  'clears the selection
        BLOCKSELECTION.Selected(i) = False
    Next i
    Me.Requery
End Sub

I am still new to VBA,

Thanks

heidi
 
H

heidii

Okay the code is still working fine but I have found a problem.

After I update the subform with the new data, the main form jumps to a
new record. It is inserting the new data in the subform correctly. I
can' go back to that record and see the new records added. But I do
not want it to move to a new record. I want to finish the record with
the other subforms I have.

What is causing my main form to move to the next record after I use my
code listed above.

I have tried changing data entry from no to yes, but it still does the
same thing.

need help.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top