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?
Recordsets, whether MAIN or SUB, are not updateable for several reasons, but having a parent/child relation isn't usually the cause. This list was copied from an old Allen Browne page that is called out by my anti-virus for having suspicious URLs. I have sanitized the list.
If you cannot edit the data in a query, this list may help you identify why it is not updatable:
It has a GROUP BY clause. A Totals query is always read-only.
It has a TRANSFORM clause. A Crosstab query is always read-only.
It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.
It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.
It involves a UNION. Union queries are always read-only.
It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.
It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.
The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.
The query is based on another query that is read-only (stacked query.)
Your permissions are read-only (Access security.)
The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)
The query calls a VBA function, but the database is not in a trusted location so the code cannot run.
The fields that the query outputs are Calcluated fields (Access 2010.)
