Updating main form from a linked form

  • Thread starter Thread starter Bobk
  • Start date Start date
B

Bobk

I have a form used in receiving material against a purchase order. The form
is the puchase order header and contains a field "postatus". This field is a
flag indicating whether the purchase order is closed or open. The form has a
button which brings up a linked form showing a summary of all the purchase
order line items and the amounts received and indicates whether the purchase
order is complete or not. On the linked form there is a button for setting
the postatus field to closed. My problem is when the button is clicked I get
the message "You can't assign a value to this object". What is happening?
The code for opening the linked form is:

Private Sub Command147_Click()
On Error GoTo Err_Command147_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPOrecstatus"

stLinkCriteria = "[ponumber]=" & Me![ponumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command147_Click:
Exit Sub

Err_Command147_Click:
MsgBox " SELECT A PURCHASE ORDER "
Resume Exit_Command147_Click

End Sub



The code I am using to set the postatus field to closed is:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Me.[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub


What am I doing wrong?
 
"Me" is shorthand to refer to the name of the *current* form. If my form is
named "frm_PO_Order" and there is a control on it named "postatus", I can
refer to the control using

Me.[postatus]

or

Forms![frm_PO_Order].[postatus]

(the brackets [] are not required unless there are spaces in the names)


Try this code for the button that updates the PO status:

'-----------------------------
Private Sub Command40_Click()
' updates the PO status on the main form
On Error GoTo Err_Command40_Click

'*** change "Your_MainForm_Name" to the name of your main form
Forms![Your_MainForm_Name].[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
'-----------------------------


My 2 cents: You should take the time to rename your controls. Which is
easier to understand what the function is:

Private Sub Command40_Click()

or

Private Sub Set_PO_Status_Click()


Or at least add a comment to tell what the code does. :)


HTH
 
Thanks for the help. It works fine. The main form updates instantly. The
linked form doesn't display the closed status though. I think I need to use a
requery to accomplish this?

Also, thanks for the advice regarding protocol in naming controls. Three
months from now trying to remember what was done with this code I will be
thanking you again.

Steve Sanford said:
"Me" is shorthand to refer to the name of the *current* form. If my form is
named "frm_PO_Order" and there is a control on it named "postatus", I can
refer to the control using

Me.[postatus]

or

Forms![frm_PO_Order].[postatus]

(the brackets [] are not required unless there are spaces in the names)


Try this code for the button that updates the PO status:

'-----------------------------
Private Sub Command40_Click()
' updates the PO status on the main form
On Error GoTo Err_Command40_Click

'*** change "Your_MainForm_Name" to the name of your main form
Forms![Your_MainForm_Name].[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
'-----------------------------


My 2 cents: You should take the time to rename your controls. Which is
easier to understand what the function is:

Private Sub Command40_Click()

or

Private Sub Set_PO_Status_Click()


Or at least add a comment to tell what the code does. :)


HTH
----
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
I have a form used in receiving material against a purchase order. The form
is the puchase order header and contains a field "postatus". This field is a
flag indicating whether the purchase order is closed or open. The form has a
button which brings up a linked form showing a summary of all the purchase
order line items and the amounts received and indicates whether the purchase
order is complete or not. On the linked form there is a button for setting
the postatus field to closed. My problem is when the button is clicked I get
the message "You can't assign a value to this object". What is happening?
The code for opening the linked form is:

Private Sub Command147_Click()
On Error GoTo Err_Command147_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPOrecstatus"

stLinkCriteria = "[ponumber]=" & Me![ponumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command147_Click:
Exit Sub

Err_Command147_Click:
MsgBox " SELECT A PURCHASE ORDER "
Resume Exit_Command147_Click

End Sub



The code I am using to set the postatus field to closed is:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Me.[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub


What am I doing wrong?
Click to expand...
Click to expand...
 
You can try:

Me.requery


It might move you to the first record. Also try:

Me.Refresh


See HELP for the differences.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
Thanks for the help. It works fine. The main form updates instantly. The
linked form doesn't display the closed status though. I think I need to use a
requery to accomplish this?

Also, thanks for the advice regarding protocol in naming controls. Three
months from now trying to remember what was done with this code I will be
thanking you again.

Steve Sanford said:
"Me" is shorthand to refer to the name of the *current* form. If my form is
named "frm_PO_Order" and there is a control on it named "postatus", I can
refer to the control using

Me.[postatus]

or

Forms![frm_PO_Order].[postatus]

(the brackets [] are not required unless there are spaces in the names)


Try this code for the button that updates the PO status:

'-----------------------------
Private Sub Command40_Click()
' updates the PO status on the main form
On Error GoTo Err_Command40_Click

'*** change "Your_MainForm_Name" to the name of your main form
Forms![Your_MainForm_Name].[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
'-----------------------------


My 2 cents: You should take the time to rename your controls. Which is
easier to understand what the function is:

Private Sub Command40_Click()

or

Private Sub Set_PO_Status_Click()


Or at least add a comment to tell what the code does. :)


HTH
----
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
I have a form used in receiving material against a purchase order. The form
is the puchase order header and contains a field "postatus". This field is a
flag indicating whether the purchase order is closed or open. The form has a
button which brings up a linked form showing a summary of all the purchase
order line items and the amounts received and indicates whether the purchase
order is complete or not. On the linked form there is a button for setting
the postatus field to closed. My problem is when the button is clicked I get
the message "You can't assign a value to this object". What is happening?
The code for opening the linked form is:

Private Sub Command147_Click()
On Error GoTo Err_Command147_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPOrecstatus"

stLinkCriteria = "[ponumber]=" & Me![ponumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command147_Click:
Exit Sub

Err_Command147_Click:
MsgBox " SELECT A PURCHASE ORDER "
Resume Exit_Command147_Click

End Sub



The code I am using to set the postatus field to closed is:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Me.[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub


What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Just a final note - I solved the linked form update problem by using your
suggestion to use Me.Refresh. I inserted this code on the main form in the On
Activate event. I discovered by watching the status field in the poheader
table, that even though the status field was updated on the main form the
data was not being written to the table. Now, when the close PO button is
clicked on the linked form the status field is updated on the main form, the
linked form is closed, the main form is activated and the table is updated.

Thanks for the help. It is much appreciated.


Steve Sanford said:
You can try:

Me.requery


It might move you to the first record. Also try:

Me.Refresh


See HELP for the differences.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
Thanks for the help. It works fine. The main form updates instantly. The
linked form doesn't display the closed status though. I think I need to use a
requery to accomplish this?

Also, thanks for the advice regarding protocol in naming controls. Three
months from now trying to remember what was done with this code I will be
thanking you again.

Steve Sanford said:
"Me" is shorthand to refer to the name of the *current* form. If my form is
named "frm_PO_Order" and there is a control on it named "postatus", I can
refer to the control using

Me.[postatus]

or

Forms![frm_PO_Order].[postatus]

(the brackets [] are not required unless there are spaces in the names)


Try this code for the button that updates the PO status:

'-----------------------------
Private Sub Command40_Click()
' updates the PO status on the main form
On Error GoTo Err_Command40_Click

'*** change "Your_MainForm_Name" to the name of your main form
Forms![Your_MainForm_Name].[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
'-----------------------------


My 2 cents: You should take the time to rename your controls. Which is
easier to understand what the function is:

Private Sub Command40_Click()

or

Private Sub Set_PO_Status_Click()


Or at least add a comment to tell what the code does. :)


HTH
----
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have a form used in receiving material against a purchase order. The form
is the puchase order header and contains a field "postatus". This field is a
flag indicating whether the purchase order is closed or open. The form has a
button which brings up a linked form showing a summary of all the purchase
order line items and the amounts received and indicates whether the purchase
order is complete or not. On the linked form there is a button for setting
the postatus field to closed. My problem is when the button is clicked I get
the message "You can't assign a value to this object". What is happening?
The code for opening the linked form is:

Private Sub Command147_Click()
On Error GoTo Err_Command147_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPOrecstatus"

stLinkCriteria = "[ponumber]=" & Me![ponumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command147_Click:
Exit Sub

Err_Command147_Click:
MsgBox " SELECT A PURCHASE ORDER "
Resume Exit_Command147_Click

End Sub



The code I am using to set the postatus field to closed is:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Me.[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub


What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
You are very welcome. Glad you got it working... :)
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
Just a final note - I solved the linked form update problem by using your
suggestion to use Me.Refresh. I inserted this code on the main form in the On
Activate event. I discovered by watching the status field in the poheader
table, that even though the status field was updated on the main form the
data was not being written to the table. Now, when the close PO button is
clicked on the linked form the status field is updated on the main form, the
linked form is closed, the main form is activated and the table is updated.

Thanks for the help. It is much appreciated.


Steve Sanford said:
You can try:

Me.requery


It might move you to the first record. Also try:

Me.Refresh


See HELP for the differences.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Bobk said:
Thanks for the help. It works fine. The main form updates instantly. The
linked form doesn't display the closed status though. I think I need to use a
requery to accomplish this?

Also, thanks for the advice regarding protocol in naming controls. Three
months from now trying to remember what was done with this code I will be
thanking you again.

:

"Me" is shorthand to refer to the name of the *current* form. If my form is
named "frm_PO_Order" and there is a control on it named "postatus", I can
refer to the control using

Me.[postatus]

or

Forms![frm_PO_Order].[postatus]

(the brackets [] are not required unless there are spaces in the names)


Try this code for the button that updates the PO status:

'-----------------------------
Private Sub Command40_Click()
' updates the PO status on the main form
On Error GoTo Err_Command40_Click

'*** change "Your_MainForm_Name" to the name of your main form
Forms![Your_MainForm_Name].[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
'-----------------------------


My 2 cents: You should take the time to rename your controls. Which is
easier to understand what the function is:

Private Sub Command40_Click()

or

Private Sub Set_PO_Status_Click()


Or at least add a comment to tell what the code does. :)


HTH
----
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have a form used in receiving material against a purchase order. The form
is the puchase order header and contains a field "postatus". This field is a
flag indicating whether the purchase order is closed or open. The form has a
button which brings up a linked form showing a summary of all the purchase
order line items and the amounts received and indicates whether the purchase
order is complete or not. On the linked form there is a button for setting
the postatus field to closed. My problem is when the button is clicked I get
the message "You can't assign a value to this object". What is happening?
The code for opening the linked form is:

Private Sub Command147_Click()
On Error GoTo Err_Command147_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPOrecstatus"

stLinkCriteria = "[ponumber]=" & Me![ponumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command147_Click:
Exit Sub

Err_Command147_Click:
MsgBox " SELECT A PURCHASE ORDER "
Resume Exit_Command147_Click

End Sub



The code I am using to set the postatus field to closed is:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Me.[postatus] = "CLOSED"

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub


What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top