I want a field to be updated on the sub form

[Guest]

I have a Main form, named as Payment Details. The Cheque no is recorded here.
I have a Sub Form, which lists out all the pending Invoices. I have a check
box and a Text Box too against each Invoice in the Sub form. I want the
cheque no to be updated in the sub form, when I tick the Check Box. ( This
means, when I tick the Check box, the cheque is paid towards these Invoices.)
Please help. Thanks

 

[Ken Snell \(MVP\)]

Am I understanding correctly that you have a checkbox in the subform, and
that you want the "cheque" number to be put into a textbox in that subform
when you click the checkbox to Yes?

If yes, you would run simple programming using the AfterUpdate event of the
checkbox control:

Private Sub CheckBoxName_AfterUpdate()
Select Me.CheckBoxName.Value
Case True
Me.TextBoxName.Value = Me.Parent.ControlName.Value
Case False
Me.TextBoxName.Value = Null
End Select
End Sub


Where:
CheckBoxName is the name of the checkbox control on the subform

TextBoxName is the name of the textbox on the subform where you want the
cheque number to be put

ControlName is the name of the control on the main form where the cheque
number is being displayed

 

[Pat Hartman\(MVP\)]

Don't you mean that you want to allocate the money from the check to the
open invoices? How do you want to do that? The usual method is oldest
invoice first. Or, are you saying that you are going to assign the check
yourself by checking the checkbox of each invoice? What happens if the
check doesn't cover the entire invoice amount?

 

[Guest]

Hi,

Thanks for your answer. But, I am not able to run the code. It shows a error
on the first line itself. My check box name is PAID and Text box name is
PAID_ID. The control Name on the main form is Cheque_no.

I wrote the following code in the after update event of the Check Box.
Private Sub PAID_AfterUpdate()
Select Me.Paid.Value -----------------------------(Error Line)
Case True
Me.Paid_id.Value = Me.Parent.cheque_no.Value
Case False
Me.Paid_id.Value = Null
End Select
End Sub

Please help.

 

[Ken Snell \(MVP\)]

Dumb me... my error in the code that I'd posted. Try this:

Private Sub PAID_AfterUpdate()
Select Casae Me.Paid.Value
Case True
Me.Paid_id.Value = Me.Parent.cheque_no.Value
Case False
Me.Paid_id.Value = Null
End Select
End Sub

Be sure that this code is going into the form's module, not in the "box"
next to the On After Update property for the checkbox.

 

[Guest]

Hi Snell,

Thanks. I got it right. could you please tell me, whether is it possible to
update this field in another table which does not exist in the sub forms.

For eg., If I want the cheque no to be updated in a different table, say
Cheque_ Numbers.

 

[Ken Snell \(MVP\)]

Sigh.... it just ain't my day... this corrects my remaining typo...

Private Sub PAID_AfterUpdate()
Select Case Me.Paid.Value
Case True
Me.Paid_id.Value = Me.Parent.cheque_no.Value
Case False
Me.Paid_id.Value = Null
End Select
End Sub

 

[Ken Snell \(MVP\)]

To do this (why do you want to redundantly the cheque number in more than
one place in your data tables? That suggests a nonnormalized data structure
and may lead to data discrepancies.... not advised) --

You typically would run an update query from the programming if you want to
update an existing record; or else you run an append query from the
programming if you want to add a new record.

What is you wish to do?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

HI Snell,

Thanks. Presently, I am updating using an append query with some criterias.
I want to do away with it, and want to append a single line, whenever I
update the Cheque_no. ( What happens in append query is that, all the datas
get appended and results in duplicate if I do not give criteria.) So, please
suggest the same in coding, where I could update a single field when I click
the check box.

Thanks.

 

[Guest]

Hi Ken,

I am trying to do basically the same thing. I want the check box to update a
rate field. This rate is actually from another subform on the main form. When
I use this code it didn't populate the rate as expected. Any ideas?

Here is my code:

Private Sub Form_frmQualifyingRateEntry_AfterUpdate()
Select Case Table.USE_Q_RATE.QUALIFYING_RATE.Value
Case True
QUALIFYING_RATE.QUALIFYING_RATE.Value =
qryQUALIFYING_RATE_CALCS3.Q_RATE.Value
Case False
QUALIFYING_RATE.QUALIFYING_RATE.Value = Null
End Select
End Sub

Thank you,

Tim

 

[Ken Snell \(MVP\)]

The generic code would be this (Caution: you should think carefully about
running this code when you click the checkbox -- that will not let you undo
the append query if you mistakenly check a box -- better if you use the
form's BeforeUpdate event perhaps, or some other event that will occur when
you're all done and sure of your selections -- perhaps even use a command
button to "save" the selections?):

Private Sub CheckboxName_AfterUpdate()
Dim dbs As DAO.Database
Dim strSQL As String
strSQL = "UPDATE TableName SET Fieldname =" & _
Me.Paid_id.Value & " WHERE AnotherField = 'some criterion'"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

My guess is that you're not using the correct reference to the subform in
that other form. You must reference a subform through the main form in which
it's located.

Forms!FormName!SubformName!ControlNameOnSubform.Value = "Your value"

where FormName is the name of the main form, SubformName is the name of the
subform control (the control that holds the subform object itself) on the
main form, and ControlNameOnSubform is the name of the control on the
subform that is to get the value.

Also, I'm not sure what qryQUALIFYING_RATE_CALCS3.Q_RATE.Value is in the
code? Are you trying to use a field named Q_RATE from a query named
qryQUALIFYING_RATE_CALCS3? If so, this will not work. Tell us more about
what this part of your code is trying to do.

 

[Guest]

Thanks. But, the Code is not executing. I have given the exact details below.
Please help.

I want the field to be updated in the Table - Stock.
Field to be updated – Received Qty in the table Stock.
Field name where value is stored in the form – Qty_received.
Check Box Name – Paid_1.

Private Sub PAID_1_AfterUpdate()
Dim dbs As DAO.Database
Dim strSQL As String
strSQL = "UPDATE Stock SET Received qty =" & _
Me.QTY_RECEIVED.Value & " WHERE qty_received <>0' "
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError--------------------- Error line.
dbs.Close
Set dbs = Nothing
End Sub

Thanks.

Maran

 

[Ken Snell \(MVP\)]

Whenever you have a space or nonletter or nonnumber or nonunderscore
character in a table, query, field, or control name, you must surround the
name with [ ] characters. So change the code step that sets the strSQL
statement to this:

strSQL = "UPDATE Stock SET [Received qty] =" & _
Me.QTY_RECEIVED.Value & " WHERE qty_received <>0' "

I don't understand the WHERE clause that you're using. Are you wanting to do
this update only if the Qty_received control's value on the form is not
zero? If yes, then the code needs to be changed even more because the query
cannot see the control on your form in this way.

Private Sub PAID_1_AfterUpdate()
Dim dbs As DAO.Database
Dim strSQL As String
If Me.QTY_RECEIVED.Value <> 0 Then
strSQL = "UPDATE Stock SET [Received qty] =" & _
Me.QTY_RECEIVED.Value & " WHERE qty_received <>0' "
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing
End If
End Sub