My Form has a sub form, When I select a client the sub form filters all his Invoice's. I want to add a button in my sub form to enter the word "PAID" to that record in the sub form. The main form has focus to TblAccounts and my sub form has a query to tblInvoices . The query in linked by ClientID in both tables. Is it possible to enter this text in to the query to up date the table tblInoices ?
Thanks for any help....................Bob
Clarify, please: Are you looking for something to mark "PAID" for a specific invoice? EDIT: Your answer to theDBguy came in just as I finished this. So I updated it.
Let's say that you have a sub-form that has its data in continuous form mode. (Since otherwise, there is only one record visible anyway.) So you want to pick one record (one row) on that subform. In that mode of operation, when you open the subform, the first row is the selected row. If you select a different row then THAT row becomes the current row.
Understand that in continuous mode, there is only one control for each field or other item in a given record. You could do this either of two ways. You can put a command button, call it InvPd (for Invoice Paid) as a control name either in the sub-form as a control in the row with the data from the invoice, or you can put that in the main form next to the sub-form. And you need a slot for the text "PAID" to appear. Call THAT slot PaySts (for Pay Status.)
Option 1: The [InvPd] button is in the sub-form. In this case, the sub-form's [InvPd] OnClick event will take the click event when you click that button, BUT another thing will happen. That row becomes the current record for the sub-form. FROM THE SUBFORM you can do something so simple as Me.[PaySts]= "PAID" and then force it to update by Me.Dirty = False - which writes back the sub-form record.
Option 2: The command button is on the main form. In this case, you have to first select the correct record in the sub-form before you click the button on the main form. Let's say the name of the sub-form control is InvList (for Invoice List). In this case, the main form's [InvPd] OnClick event will take the click event but now the event is in the main form and the action you want is in the sub-form. Because this is trickier, the syntax is a bit uglier. You want [InvList].[Form].[PaySts] = "PAID" followed by [InvList].Form.Dirty = False - which writes back the sub-form record.
Option 3: Instead of a command button, use a check box in the sub-form and just check it. You can use InvPd as its name here, too - but this time it is a check box. You don't have to write anything, just have the check box checked or not checked to indicate pay status. And to assure the quick update, again remember that selecting the row you want to pay makes it current, and the check box in this case is in the sub-form, so in the sub-form's events, setting the check box selects that row. Then Me.Dirty = False finishes the job. You asked about a command button, but the number of mouse actions would be the same whether you had the command button or the check box - and you wouldn't need a separate invoice status box that only was blank or contained the word "PAID" - your check box would be both the active control and the status indicator. Less work.
Just to clarify: The sub-form's event code might be this simple:
Code:
Private Sub InfPd_Click()
Me.[PaySts] = "PAID"
Me.Dirty = False
End Sub
I'm not sure why you would do this from the main form when you are displaying potentially many invoices in the subform. You could have a text box (possibly hidden) in the main form that gets the value of the current InvoiceID from the subform code. Then use the text box in an update query of the invoice record followed by a requery. Another method would be to clone the subform recordset and apply the "PAID" to the selected invoice.
My Form has a sub form, When I select a client the sub form filters all his Invoice's. I want to add a button in my sub form to enter the word "PAID" to that record in the sub form. The main form has focus to TblAccounts and my sub form has a query to tblInvoices . The query in linked by ClientID in both tables. Is it possible to enter this text in to the query to up date the table tblInoices ?
Thanks for any help....................Bob
Clarify, please: Are you looking for something to mark "PAID" for a specific invoice? EDIT: Your answer to theDBguy came in just as I finished this. So I updated it.
Let's say that you have a sub-form that has its data in continuous form mode. (Since otherwise, there is only one record visible anyway.) So you want to pick one record (one row) on that subform. In that mode of operation, when you open the subform, the first row is the selected row. If you select a different row then THAT row becomes the current row.
Understand that in continuous mode, there is only one control for each field or other item in a given record. You could do this either of two ways. You can put a command button, call it InvPd (for Invoice Paid) as a control name either in the sub-form as a control in the row with the data from the invoice, or you can put that in the main form next to the sub-form. And you need a slot for the text "PAID" to appear. Call THAT slot PaySts (for Pay Status.)
Option 1: The [InvPd] button is in the sub-form. In this case, the sub-form's [InvPd] OnClick event will take the click event when you click that button, BUT another thing will happen. That row becomes the current record for the sub-form. FROM THE SUBFORM you can do something so simple as Me.[PaySts]= "PAID" and then force it to update by Me.Dirty = False - which writes back the sub-form record.
Option 2: The command button is on the main form. In this case, you have to first select the correct record in the sub-form before you click the button on the main form. Let's say the name of the sub-form control is InvList (for Invoice List). In this case, the main form's [InvPd] OnClick event will take the click event but now the event is in the main form and the action you want is in the sub-form. Because this is trickier, the syntax is a bit uglier. You want [InvList].[Form].[PaySts] = "PAID" followed by [InvList].Form.Dirty = False - which writes back the sub-form record.
Option 3: Instead of a command button, use a check box in the sub-form and just check it. You can use InvPd as its name here, too - but this time it is a check box. You don't have to write anything, just have the check box checked or not checked to indicate pay status. And to assure the quick update, again remember that selecting the row you want to pay makes it current, and the check box in this case is in the sub-form, so in the sub-form's events, setting the check box selects that row. Then Me.Dirty = False finishes the job. You asked about a command button, but the number of mouse actions would be the same whether you had the command button or the check box - and you wouldn't need a separate invoice status box that only was blank or contained the word "PAID" - your check box would be both the active control and the status indicator. Less work.
Just to clarify: The sub-form's event code might be this simple:
Code:
Private Sub InfPd_Click()
Me.[PaySts] = "PAID"
Me.Dirty = False
End Sub
It enters payment descriptions or Invoices numbers into payment descriptions or enter only that invoice total in to Paid amount but all this info is stored in TblAccount and Im trying get Paid save into tblInvoices
It enters payment descriptions or Invoices numbers into payment descriptions or enter only that invoice total in to Paid amount but all this info is stored in TblAccount and Im trying get Paid save into tblInvoices
Why is it not updatable?
I would just use a double click event on the amount or paid control.
If you cannot update the control, then just an update query with some relevant I'd as the criteria. Then a requery .
Wouldn't you need some check on the amount paid? You could just sum for invoice and if it matches invoice amount, show paid. Control could then be unbound?
