Insert a record to an opening subform

[Babycat]

Hi Everyone

I have a continuous subform (call it subform B) bound to table TBLTEMPNAME. There is a button on subform A what is to insert new record to TBLTEMPNAME therefore to subform B.
However, before running InsertSQL I normally have to remove the recordsource of subform B to NONE , if not it will introduce an error something likely exclusive open... I think it becauses the subform B is holding table TBLTEMPNAME so it is not allow to insert...

Thus after inserting, I will set recordsource again such following code

SQL = "SELECT  TBLTEMPNAME .* FROM  TBLTEMPNAME  ORDER BY TempPName ASC;"


    InsertSQL = "INSERT INTO  TBLTEMPNAME "  _
                & " (ProductID, TempCode, TempPName) VALUES " _
                & " (" & Txt_ID & ", '" & Txt_Qinfo_Code & "', '" & Txt_Qinfo_Pname & "');"
            
    Forms!MainForm!SubFrmName.Form.RecordSource = ""  'Set to NONE

        DoCmd.SetWarnings False
        DoCmd.RunSQL InsertSQL
        DoCmd.SetWarnings True

    Forms!MainForm!SubFrmName.Form.RecordSource = SQL ' Set recordsource again

The side effect is subform flickering because of it's recordsource changed.

Is there anyway to implement it better?

Thank you!

 

[theDBguy]

Are Txt_ID, Txt_Qinfo_Code, and Txt_Qinfo_Pname unbound controls?

 

[Babycat]

Are Txt_ID, Txt_Qinfo_Code, and Txt_Qinfo_Pname unbound controls?

No, they are bound controls on other subform (call it subform A).
My correction: The button is not on mainform. It is on the subform A.

 

[theDBguy]

No, they are bound controls on other subform (call it subform A).
My correction: The button is not on mainform. It is on the subform A.

So, are they bound to the table fields ProductID, TempCode, TempPName respectively? If so, are you trying to duplicate a record?

 

[Babycat]

So, are they bound to the table fields ProductID, TempCode, TempPName respectively? If so, are you trying to duplicate a record?

They are 2 different tables.

Actually subform A (continuous) bounds to TBLPRODUCT, subform B (continuous) bounds to TBLTEMPNAME.
Subform A has controls: Txt_ID, Txt_Qinfo_Code, Txt_Qinfo_Pname hold value of field ProductID, ProductCode and ProductName. Button on subform A is to select product on table TBLPRODUCT copy its values to TBLTEMPNAME (subform B)

I have code to check if that productID already existed on TBLTEMPNAME, the Insert will be ignored. Thus there is no dupplicate

Edit: Add a screenshot, Txt_ID is an invisible control

 

[theDBguy]

They are 2 different tables.

Actually subform A (continuous) bounds to TBLPRODUCT, subform B (continuous) bounds to TBLTEMPNAME.
Subform A has controls: Txt_ID, Txt_Qinfo_Code, Txt_Qinfo_Pname hold value of field ProductID, ProductCode and ProductName. Button on subform A is to select product on table TBLPRODUCT copy its values to TBLTEMPNAME (subform B)

I have code to check if that productID already existed on TBLTEMPNAME, the Insert will be ignored. Thus there is no dupplicate

Edit: Add a screenshot, Txt_ID is an invisible control

View attachment 96123

Hi. Thanks for the clarification. It sounds like you're trying to copy a record from subform A to subform B, correct? Also, you said the code is executed from subform A, right? If so, what you described earlier about getting a lock error when doing that doesn't quite make sense. Typically, a lock error will happen if you are in the process of manually editing a form and at the same time try to run some code to also update the bound table. In your case, if the button is on subform A, then any edits you may do on subform B should automatically save into the table and release the lock. There must be something else going on for it to hold on to the record lock.

 

[Babycat]

Yes, I am trying to copy a record from A to B. There must be something wrong elsewhere. This is not first time I encounter this problem
The screenshot when subform B recordsoursei s not set not NONE

Forms' properties

 

[theDBguy]

One quick test is to make sure subform B is not Dirty. In other words, make sure there are no pending updates on that form by saving the dirty record into the bound table first. Maybe you give that a shot.

 

[Babycat]

One quick test is to make sure subform B is not Dirty. In other words, make sure there are no pending updates on that form by saving the dirty record into the bound table first. Maybe you give that a shot.

Tested, it is not dirty

 

[theDBguy]

That's sort of what I was thinking earlier, since the button is not on the same subform. If you need help troubleshooting the problem, you might consider posting a demo file for testing. Not sure what else to suggest without seeing the entire setup. Sorry.

 

[Babycat]

Hi DB

Please find attached db. The code is in form FrmSub_PhieuNX

 

[Babycat]

Hi

Somehow the RecordLock properties of form B is set to All record, that mighe be the cause.
I have treid to remove it, it then works well without any error