runtime error while adding records

[tselie115]

The action was cancelled by an associated object runtimeerror "3426" when trying to execute this code:
Me.invetory_transactions.Form.Recordset.AddNew
PS: it only happend when the recordset is empty.
it doesnt happen if records are existing and we are adding to them

Private Subtype_AfterUpdate()
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from Materialtable1 where ID =" & Me.Type.Column(1))
Do Until tb.EOF = True
With tb
Form_InventoryTransactions.material = tb!Material1
Form_InventoryTransactions.Rate = tb!Rate1
Form_InventoryTransactions.Unit = tb!Unit1
Form_InventoryTransactions.materialid = tb!materialid
End With
tb.MoveNext
Me.invetory_transactions.Form.Recordset.AddNew

Loop
tb.Close
db.Close

 

[wazz]

Private Subtype_AfterUpdate()
    
    Dim db As dao.Database    'good idea to specify dao.
    dim tb As dao.Recordset
    Set db = CurrentDb
    Set tb = db.OpenRecordset(Materialtable1, dbopendynaset)    ' not required?  where ID =" & Me.Type.Column(1)

    With tb
        .addnew
        !Material1 = Form_InventoryTransactions.material    ' your field first; where data is coming from second.
        !Rate1 = Form_InventoryTransactions.Rate
        !Unit1 = Form_InventoryTransactions.Unit
        !materialid = Form_InventoryTransactions.materialid
        !Type = Me.Type.Column(1)    'add this? may need Form_InventoryTransactions.Type.Column(1) 
        .update    'required
        .Close
    End With

    set db = nothing
    set rs = nothing

hopefully this is not the opposite of what you meant!

 

[tselie115]

hehe in fact this is the oposite!
im trying to copy the full record set from the "tablematerial1" to the subform fields.

 

[tselie115]

in fact i have 3 related tables "type" (ID) one-to-many-relationship with "materialtable1"(MaterialID) one-to-many relationship with "subtypes".
these tables are used as templates.the user setup the content once and he can use them.

i have other 3 tables with same structure relationships "products", "inventory transactions" and "Submaterials".these tables are shown through 3 nested subforms.

what im trying to do is that when the user choses a combobox(rowsource is "type", the records already setup in the template tables to be autofilled in the subform(invertorytransactions) and subsubform (submaterials) and therefore update the tables "inventorytransactions" and "submaterials"

there are two approaches either filling the subform and subsubform and therefore the tables or update the tables and refresh to show it on the forms.
the vb code shwn in previous post is giving the error!
Thank you and regards!

 

[wazz]

hi. it's a bit difficult to understand this. but, if you choose to apply a recordset to a subform you can simply say

me.sfrmYourSubform.form.RecordSource = rsYourRecordset

maybe that's all you need for now? you might not need that line of code that's giving you the error.

btw, one reason it's hard to tell what's going on is because of your control and object names. try to use the standard conventions. for example,

tables - tblMaterials1.
recordsets - rsYourRecordset (unless it's another language in which case, nevermind)
subforms - sfrmYourSubform (or fsubYourSubform)

it makes the code *much* easier to understand and it's easier to follow patterns from tbl to rs to frm to sfrm (tblMaterials - rsMaterials - frmMaterials - sfrmMaterials).

also, you have this:

With tb
    Form_InventoryTransactions.material = [COLOR="Red"]tb[/COLOR]!Material1
    Form_InventoryTransactions.Rate = [COLOR="red"]tb[/COLOR]!Rate1
    Form_InventoryTransactions.Unit = [COLOR="red"]tb[/COLOR]!Unit1
    Form_InventoryTransactions.materialid = [COLOR="red"]tb[/COLOR]!materialid
End With

when you use With... you can change your code to this:

With tb
    Form_InventoryTransactions.material = !Material1    
    [COLOR="Green"]'or: me.material = !Material1; or me.sfrmYourSubform.form!material = !Material1.[/COLOR]
    Form_InventoryTransactions.Rate = !Rate1
    Form_InventoryTransactions.Unit = !Unit1
    Form_InventoryTransactions.materialid = !materialid
End With

if you apply the recordset to one of your forms or subforms as described at the beginning, you might not need this part at all. (?)