Insert a record to an opening subform (1 Viewer)

Babycat

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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

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!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
Are Txt_ID, Txt_Qinfo_Code, and Txt_Qinfo_Pname unbound controls?
 

Babycat

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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

1636828227101.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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

1636829220631.png


Forms' properties

1636829489416.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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
1636832009280.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
Hi DB

Please find attached db. The code is in form FrmSub_PhieuNX
 

Attachments

  • SubFormIssue.zip
    1.4 MB · Views: 351

Babycat

Member
Local time
Today, 15:15
Joined
Mar 31, 2020
Messages
275
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
 

Users who are viewing this thread

Top Bottom