 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Macros / June 2006How to "POST" accounting data into underlying tables
I have a form to record customer payment receipts into a Receipts table indicating customer, amount, date etc... Within that form is a subform based on a query that selects all open invoices to that customer from the Invoices table. I want to be able to allocate the receipt among those invoices that are being paid (in full or in part) into a Receipts Allocation table. Receipts table is joined to Receipts Allocation table by ReceiptsID. Receipts Allocation table is then joined to the Invoices table by InvoicesID. So the Receipts Allocation table sits in the middle of the other two and is joined to both by their respective Primary keys. So within the Form for entering Receipts I've got a subform in datasheet view that shows the open invoices to the customer making the payment. Ideally, beside each of these invoice records I'd like to have fields for Previous Payments and Outstanding Balance (derived from a query on the Receipts Allocation table) and Amount Being Allocated and a Checkbox to indicate whether that payment closes the invoice or not (both fields which will ultimately be fed into the Receipts Allocation Table). However, the first time a receipt is received for a given invoice, there is no previous record of the invoice in the Receipts Allocation table. So when I run the query for previous payments it returns a null set. And similarly the only way I can indicate the Amount Being Allocated into the Receipts Allocation table is if I manually type in the associated InvoiceID first which is repetitive and prone to error. It seems to me that in other systems, a temporary holding table is used to input the receipt allocation info which is then "posted" into the underlying real table. I'm not sure if this intermediary step is necesary but in any event I'd still need to somehow "post" the Primary key (InvoiceID) from the subform of open invoices into a new record in the Receipts Allocation table so I can then fill in the other fields of that record. [This "posting" issue will occur several times in my accounting database.] And the tough part is I know nothing about coding. I'm trying to accomplish this using macros. Any assistance or suggestions would be greatly appreciated ! FJ, I would recommend the following changes to your model: 1. Remove the Amount field from the Receipts table. This is confusing and redundant. The amounts are being allocated against the invoices, and are to be stored in the Receipts Allocation table. The total amount for the receipt is always easily derivable when required. 2. Forget the idea of a InvoiceClosed checkbox. This is unnecessarily complicated. This information is easilt derivable when required, based on the difference between the payments received and the invoice amount. 3. Forget the idea of a temporary holding table. This is unnecessarily complicated. 4. It doesn't work to have the subform show the invoices - you can't really enter the required data with that structure. Remove this subform. On your Receipts form, you need a subform based on the Receipts Allocation table. All you need on this form is two controls, being for the Invoice and the Amount. The ReceiptsID is automatically taken care of via the Link Master Fields and Link Child Fields settings for the subform. There will also be the I recommend a continuous view form. The Invoice field can be represented as a Combobox, which will list all the outstanding invoices for that customer, and show the outstanding amount in one of the combobox columns. This is fairly easy to work out. When you select an invoice in the combobox, the outstanding amount can be written (using a SetValue macro) as the default amount to the Amount field, but you can edit it of course if necessary. The total amount can be shown in a control in the footer or header of the subform, for you to cross-check against the total payment received.  SignatureSteve Schapel, Microsoft Access MVP > I have a form to record customer payment receipts into a Receipts table > indicating customer, amount, date etc... Within that form is a [quoted text clipped - 37 lines] > > Any assistance or suggestions would be greatly appreciated ! Steve, Thanks for the suggestion. I like the simplicity. Just a couple of issues: 1) I need the checkbox to indicate a closed invoice because they are frequently closed prior to being received in full. 2) The main problem I can't surmount is that when I make the query to feed into the InvoiceID combobox I can get the selection to work perfectly IF there have been previous payments (ie. if the InvoiceID already exists in the Receipt Allocation table. However, if its a first time payment on that invoice then when the query attempts to Sum any previous payments to the open invoices from the Receipt Allocation table, it comes up completely blank because the InvoiceID number doesn't exist within the Receipt Allocation table. So rather than getting a selection of open invoices with a $0 for prior payments, the whole query comes up Null. I suspect there's some simple way around this but I'm stumped. I'd really appreciate it if you could solve that one for me! > FJ, > [quoted text clipped - 70 lines] > > > > Any assistance or suggestions would be greatly appreciated ! FJ, > 1) I need the checkbox to indicate a closed invoice because they are > frequently closed prior to being received in full. Ok. You know your business better than me. In my experience, this would normally involve the outstanding aomout being "written off", in which case a write-off transaction needs to be entered in order to balance the books :-). > 2) The main problem I can't surmount is that when I make the query to feed > into the InvoiceID combobox I can get the selection to work perfectly IF [quoted text clipped - 5 lines] > table. So rather than getting a selection of open invoices with a $0 for > prior payments, the whole query comes up Null. You will need a Left Join in the query that you use as the combobox's Row Source. And you will probably need an Nz() function to handle the invoices with no previous transactions. I imagine something like this, as the SQL view of such a query... SELECT [InvoiceID], [Amount]-Nz([SumOfAmountReceived],0) AS Outstanding FROM Invoices LEFT JOIN PaymentsSummary ON Invoices.InvoiceID = PaymentsSummary.InvoiceID WHERE CustomerID = [Forms]![Receipts]![CustomerID] AND [Amount]-Nz([SumOfAmountReceived],0)>0 PaymentsSummary in this example is a totals query that gives the SumOfAmountReceived for each InvoiceID. ... something like that, anyway :-). SignatureSteve Schapel, Microsoft Access MVP Steve, I tried as you suggested (I think, see query below) but it still comes up blank. SELECT Invoices.CUSTOMERID, [Receipt Allocations].INVOICEID, [Receipt Allocations].ReceiptAllocation, [Invoices]![InvoiceAmount]-Nz([Invoices Payments Received To Date]![SumOfAmount Paid],0) AS Balance FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt Allocations].INVOICEID WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID])); The [Invoice payments Received To Date] is a query that sums past receipts per INVOICEID. Can you see a flaw in this ?? Seems to me that the query is still trying to find the INVOICEID field in the Receipts Allocation table. Although we've converted the Null to 0 within the formula for calculating the Balance outstanding, the query doesn't get that far. It shuts down as soon as it tries to match the INVOICEID within the Invoice table to an INVOICEID within the Receipts Allocation table, despite the Left Join. Thanks again ! > FJ, > [quoted text clipped - 29 lines] > SumOfAmountReceived for each InvoiceID. > .... something like that, anyway :-). Steve, I've stripped the query down to the fundamental problem elements for analysis: SELECT Invoices.CUSTOMERID, [Invoices]![InvoiceAmount]-Nz([Invoices Payments Received To Date]![SumOfAmount Paid],0) AS Balance FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt Allocations].INVOICEID WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID])); The above returns the same results whether there is an inner join or a left join. The query [Invoices Payments Received To Date] works fine on its own. It simply returns all invoices with any past payments. However, as soon as the search is narrowed to invoices from a customer without past payments it returns a null set again. > Steve, I tried as you suggested (I think, see query below) but it still comes > up blank. [quoted text clipped - 53 lines] > > SumOfAmountReceived for each InvoiceID. > > .... something like that, anyway :-). FJ, I can't see why you have incuded the Receipt Allocations table in the query. Just remove it. SignatureSteve Schapel, Microsoft Access MVP > Steve, > I've stripped the query down to the fundamental problem elements for analysis: [quoted text clipped - 14 lines] > search is narrowed to invoices from a customer without past payments it > returns a null set again. YES YES ! Fantastic..... Thank you for steering me in the right direction. Regarding the combobox, I can see both the invoice number and balance data when making my choice of invoice to pay. But after I chose one of them, it only dispalys the invoice number, not the balance data. Is there anyway I can see both ? Also, what event macro can I use to refresh the subform query? After I enter an amount to apply to an invoice, the balance in the combobox doesn't change unless I close the entire form and re-open it. (If I do a "Requery" macro using the OnCurrent event, my entire Access database crashes everytime it runs!!). Thanks a lot. > FJ, > [quoted text clipped - 19 lines] > > search is narrowed to invoices from a customer without past payments it > > returns a null set again. FJ, One option would be to put a SetValue macro on the After Update event of your invoice number combobox. Set it up like this in your macro... Item: [Amount] Expression: [Invoice Number].[Column](1) This assumes that Invoice Number is the name of the combobox, and Amount is the name of the payment amount. Adjust as applicable. In other words, this has the effect of setting the default amount for payment to the invoice's outstanding balance. If it's not, you can simply edit the amount. If you don't like this idea, then you can put an unbound textbox on the form, with its Control Source property set to =[Invoice Number].[Column](1) ... so this will show the outstanding amount, for you to base your decision about how much to enter into the payment amount. In such a case, I would make the background colour of this textbox different, or in some other way make it distinctive, to help make it clear that it is not actual data. I don't know what's up with your Requery causing problems. Try putting the name of your combobox (enclosed in []s) in the Control Name argument of the Requery macro, and put the macro instead on the Enter event of the combobox itself. SignatureSteve Schapel, Microsoft Access MVP > YES YES ! Fantastic..... > [quoted text clipped - 10 lines] > using the OnCurrent event, my entire Access database crashes everytime it > runs!!). Steve, Thanks. I used your suggestion to alter the column which the combox displays upon selection and it worked well. However, is there anyway I can get more than one of the columns to display once I've made the selection? Also, in a completely different form, I have a subform in datasheet view in which charges to customers are listed. I click a checkbox to indicate those charges to be added to an invoice. How can I create a total at the bottom of either the subform or form, to indicate the totals of only those charges which have been checked? Thanks once again. > FJ, > [quoted text clipped - 35 lines] > > using the OnCurrent event, my entire Access database crashes everytime it > > runs!!). FJ, > Thanks. I used your suggestion to alter the column which the combox displays > upon selection and it worked well. However, is there anyway I can get more > than one of the columns to display once I've made the selection? There are a number of approaches to this type of scenario. The combobox can only display the data from one column, but the corresponding data from other columns can be shown in separate controls (textboxes probably). There is an explanation in this article... http://accesstips.datamanagementsolutions.biz/lookup.htm > Also, in a completely different form, I have a subform in datasheet view in > which charges to customers are listed. I click a checkbox to indicate those > charges to be added to an invoice. How can I create a total at the bottom of > either the subform or form, to indicate the totals of only those charges > which have been checked? I would recommend a continuous view form rather than a datasheet. In the footer, put an unbound textbox, with its Control Source set to the equivalent of... =Sum(IIf([YourCheckbox],[YourCharge],0)) alternatively... =-Sum([YourCharge]*[YourCheckbox]) SignatureSteve Schapel, Microsoft Access MVP Great. I'll give that a whirl. In the meantime I've discovered another problem! In one of my forms I have a control which is the product of two other controls (done within a macro). If the numbers are say $500,000.00 * .80 the answer keeps coming up to 400,000.006 I've taken all numbers out to 12 decimal places and neither the 500000 nor the .8 have any distant decimals. The 500,000 field format is "currency" and the .8 field is "double" with 2 decimal places. I can't think of why this isn't calculating properly. Do you have any suggestions? Thanks. > FJ, > [quoted text clipped - 20 lines] > alternatively... > =-Sum([YourCharge]*[YourCheckbox]) FJ, I suggest changing the field with the 0.8 to Currency data type as well (unless you will ever need this to be more than 4 decimal places). This will be more accurate. A floating point number format such as Double will often produce these types of rounding problems. SignatureSteve Schapel, Microsoft Access MVP > If the numbers are say $500,000.00 * .80 the answer keeps coming up to > 400,000.006 [quoted text clipped - 5 lines] > I can't think of why this isn't calculating properly. Do you have any > suggestions? Seems to be working ok now. I switched to "Fixed" format rahter than currency because the actual umber was a % and the currency might be confusing. Thanks. > FJ, > [quoted text clipped - 12 lines] > > I can't think of why this isn't calculating properly. Do you have any > > suggestions? FJ, There is a confusion, caused by the use of the word "currency" in two different contexts. In the design of a field, then one of the things you have to consider is: Data Type If you designate a Data Type of Number, then (among other things) you have these properties to consider: Field Size Format If you designate a Data Type of Currency, then (among other things) you have this property to consider: Format Whether you select Currency or Number Data Type, amongst the Format options is Currency, as well as Fixed and the others. So, you can have a Number Data Type field which is formatted as Currency, and you can have a Currency Data Type field which is not formatted as Currency. Two different meanings of the word currency. The key point to remember here is that the Format property is relatively trivial, in that it only affects the way the data is displayed, whereas the Data Type setting of the field is more significant, as it affects the way the data is stored. So, generally speaking, if you are dealing with numerical values that will not require more than 4 decimal places, and for which you want to do precise calculations, the Currency Data Type is the obvious choice. Then you can format it to display however you want. Using a Fixed format for a Number Data Type field simply disguises the inaccuracy. Well Number fields with Filed Size set to Integer or Long Integer are accurate. SignatureSteve Schapel, Microsoft Access MVP > Seems to be working ok now. I switched to "Fixed" format rahter than > currency because the actual umber was a % and the currency might be confusing. Oh, I see. I didn't make that distinction. I'll switch the Data Type to Currency then. That should make life easier since none of my calculations require more than 3 decimals. Thanks. I just posted the following question to the "Query" area but since you are obviously a true "MVP" so I'll ask you too if you don't mind. I have a Receipts Allocation table in which the payment received for invoices are recorded by INVOICEID number. When the final payment for an invoice is received (which may or may not cover the full amount of the invoice) a checkbox is ticked "Yes" in the same table. I'd like a query where I can calculate my credit exposure to a given customer. This can be a simple formula of Total Open Invoices - Receipts to date on same. However, I can't simply add up all invoices and subtract all receipts (for reasons related to the economics of the business). I need to be able to eliminate those invoices and all associated receipts where any one of the receipts for that invoice has been checked as "yes". In other words I need to get rid of any "closed" invoices first. I can't figure out how to set-up this query and formula. Any assistance would be greatly appreciated. Thanks. > FJ, > [quoted text clipped - 36 lines] > > Seems to be working ok now. I switched to "Fixed" format rahter than > > currency because the actual umber was a % and the currency might be confusing. FJ, I have replied in the .queries newsgroup. SignatureSteve Schapel, Microsoft Access MVP > I have a Receipts Allocation table in which the payment received for invoices > are recorded by INVOICEID number. When the final payment for an invoice is [quoted text clipped - 9 lines] > need to get rid of any > "closed" invoices first. Steve, I've got a form with a few "Null" fields and a checkbox. Within that form is an invisible subform containing the results of a query. Each result is formatted as "Currency" both within the query formulas and on the form itself. The Null fields in the master form are also formated as Currency both within the form and in the underlying table (both Data Type and Format). I've got a macro which is supposed to copy the data in the subform into the appropriate fields in the master form if the checkbox is clicked. However, when the macro runs I keep getting the error message: THE VALUE YOU ENTERED ISN'T VALID FOR THIS FIELD. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits. I can't figure out what is causing this. Do you by chance have any suggestions as to possible problems? Thanks again. > FJ, > [quoted text clipped - 13 lines] > > need to get rid of any > > "closed" invoices first. Dear FJquestioner: If you can post your macro on this forum, I would be able to troubleshoot what the problem is. Without coding examples of what you've written, I wouldn't be able to guess what it might be. Joe P. -----Original Message----- From: FJquestioner [mailto:FJquestioner@discussions.microsoft.com] Posted At: Tuesday, June 06, 2006 2:38 PM Posted To: microsoft.public.access.macros Conversation: How to "POST" accounting data into underlying tables Subject: Re: How to "POST" accounting data into underlying tables Steve, I've got a form with a few "Null" fields and a checkbox. Within that form is an invisible subform containing the results of a query. Each result is formatted as "Currency" both within the query formulas and on the form itself. The Null fields in the master form are also formated as Currency both within the form and in the underlying table (both Data Type and Format). I've got a macro which is supposed to copy the data in the subform into the appropriate fields in the master form if the checkbox is clicked. However, when the macro runs I keep getting the error message: THE VALUE YOU ENTERED ISN'T VALID FOR THIS FIELD. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits. I can't figure out what is causing this. Do you by chance have any suggestions as to possible problems? Thanks again. "Steve Schapel" wrote: > FJ, > [quoted text clipped - 6 lines] > > > > I'd like a query where I can calculate my credit exposure to a given > > customer. This can be a simple formula of Total Open Invoices - Receipts to > > date on same. However, I can't simply add up all invoices and subtract all [quoted text clipped - 3 lines] > > need to get rid of any > > "closed" invoices first. Joseph, The following is a query which generates the expression value for NetRebate which is in my subform. It appears the subform perfectly. However, when I try to use the Set Value macro to copy the NetRebate amount into the main form I get the error message. Incidentally, the formatting I was talking about was Format(xxxx,"Currency"). However, that was not the problem. The error message persists even when that formattting is removed. SELECT Invoices.RebatePaidApplied, Invoices.Closed, Invoices.INVOICEID, IIf(Invoices!ClosedHow<3,[Invoices Payments Received To Date]![SumOfAmount Paid]-(Invoices!AdvanceAmount+[Disc Fee on Payment Received]!DiscFee),Nz([Invoices Payments Received To Date]![SumOfAmount Paid],0)-(Invoices!AdvanceAmount+[Disc Fee on Recourse or Chargeback]!DiscFee)) AS NetRebate FROM ((Invoices LEFT JOIN [Invoices Payments Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) LEFT JOIN [Disc Fee on Payment Received] ON Invoices.INVOICEID = [Disc Fee on Payment Received].INVOICEID) LEFT JOIN [Disc Fee on Recourse or Chargeback] ON Invoices.INVOICEID = [Disc Fee on Recourse or Chargeback].INVOICEID WHERE (((Invoices.RebatePaidApplied)=No) AND ((Invoices.Closed)=Yes)); The Set Value macro I'm using is: Item: [Net Rebate] Expression: IIf([Rebated]=-1,[Forms]![Rebates Payable and Discount Fees]![Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated].[Form]![Rebate and Fees upon Payment].[Form]![NetRebate],Null) After this I run the Refresh Command. Does this shed any light?? Thanks for your help. > Dear FJquestioner: > [quoted text clipped - 69 lines] > > > need to get rid of any > > > "closed" invoices first FJ, The bit that makes me the most suspicious is "formatted as 'Currency' within the query formulas". What do you mean by this? SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 18 lines] > I can't figure out what is causing this. Do you by chance have any > suggestions as to possible problems? Steve, The following is a query which generates the expression value for NetRebate which is in my subform. It appears the subform perfectly. However, when I try to use the Set Value macro to copy the NetRebate amount into the main form I get the error message. Incidentally, the formatting I was talking about was Format(xxxx,"Currency"). However, that was not the problem. The error message persists even when that formattting is removed. SELECT Invoices.RebatePaidApplied, Invoices.Closed, Invoices.INVOICEID, IIf(Invoices!ClosedHow<3,[Invoices Payments Received To Date]![SumOfAmount Paid]-(Invoices!AdvanceAmount+[Disc Fee on Payment Received]!DiscFee),Nz([Invoices Payments Received To Date]![SumOfAmount Paid],0)-(Invoices!AdvanceAmount+[Disc Fee on Recourse or Chargeback]!DiscFee)) AS NetRebate FROM ((Invoices LEFT JOIN [Invoices Payments Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) LEFT JOIN [Disc Fee on Payment Received] ON Invoices.INVOICEID = [Disc Fee on Payment Received].INVOICEID) LEFT JOIN [Disc Fee on Recourse or Chargeback] ON Invoices.INVOICEID = [Disc Fee on Recourse or Chargeback].INVOICEID WHERE (((Invoices.RebatePaidApplied)=No) AND ((Invoices.Closed)=Yes)); Does this shed any light?? > FJ, > [quoted text clipped - 23 lines] > > I can't figure out what is causing this. Do you by chance have any > > suggestions as to possible problems? FJ, Ok, so am I right in my understanding that this query is the Record Source of the [Rebate and Fees upon Payment] subform, which is a subform on the [Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated] subform, which is a subform on the [Rebates Payable and Discount Fees] form? And the macro runs on an event on the [Rebates Payable and Discount Fees] form? And on the [Rebates Payable and Discount Fees] form, there is a control named NetRebate. Ok, can you also let us know what is the Record Source of the [Rebates Payable and Discount Fees] form? If a table, is NetRebate a field in this table? If a query, what is the SQL of the query? Clarification of the above may help. However, you are also referring to the current record on the [Rebate and Fees upon Payment] subform, and then I think you said this subform was hidden? So it can't have a current record? So it can't work? ... Just surmising here. And beside all this, well, to be honest, if you end up wanting to set the value of one control to the value of another control, it is almost always an indication of a database design problem. But that's starting to get complicated. The good news is that when you finally get through problems like this, you find out that there is a very easy way to do what you originally thought was difficult. SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 47 lines] >>> I can't figure out what is causing this. Do you by chance have any >>> suggestions as to possible problems? Steve, I understand your comment on the design issue. I am in the factoring business which means I purchase the accounts receivable of other companies at a discount from face value and the debtor pays me down the road (hopefully!). If I am buying a receivable of say $100, I'll pay $85 as an up front advance. Then later when I receive the $100 from the debtor I will rebate the company I bought the invoice from, the remaining $15, less a discount fee (which works like an interest rate). So this database is to keep track of all the invoices purchased as well as the advances made and the rebates owed and the discount fees I've earned. In the section I'm working on now, I have queries which calculate the rebate owed and the discount fee earned for each invoice. I have a form [Rebates Payable and Discount Fees] in which I select a client and a currency. Then the subform [Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated] displays all closed (ie paid) invoices (from the Invoices table) which match the client and currency criteria. My objective is to select from this list, those invoices on which I currently want to book a rebate payable and book my discount fee. I indicate this by clicking a checkbox within that Subform (which is the "FeeAndRebateAdded" field in the underlying Invoices table). Clicking this checkbox triggers the macro which then takes the rebate and discount fee calculations from the queries and places them into the [RebatePayale] and [DiscFee] fields of the subform and hence into the underlying Invoices table. The reason I do this (and please correct me if I'm wrong) is that this data (rebtes, discount fees and other data) is going to be exported into the QuickBooks accounting program and needs to remain static. In other words the rebate and discount fee calculations, once made must remain the same even if I later change the advance rate of interest charges for a given client (which would alter the results of the query). The only resaon I have an invisible subform [Rebate and Fees upon Payment] within the subform [Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated] is that it contains the rebate and discount fee query calculations and I don't know how to get a marco to run on an object that isn't part of the currently open form. (Of course I could get the macro to Open the Query independently but I don't know how to do this without the query physically showing up on the screen). I have other forms and queries in the program that do similar functions and I've never had a problem with the macro. This error message about an invalid field value has me stumped. Thanks. > FJ, > [quoted text clipped - 72 lines] > >>> I can't figure out what is causing this. Do you by chance have any > >>> suggestions as to possible problems? FJ, > ... > ... I don't know how to get a marco to run on an object that isn't part of > the currently open form. (Of course I could get the macro to Open the Query > independently but I don't know how to do this without the query physically > showing up on the screen). This is really a job for an Update Query. An Update Query is an "action query" (as against a query that returns a data set), and as such nothing will physically show on the screen. The Update Query would be based on the same sort of data as the query that you have your subform based on. You can use an OpenQuery action in your macro to run the update. > ... This error message about an invalid > field value has me stumped. I have never tried to run a SetValue macro with an IIf() function in the Expression argument, so I'm not 100% sure it's possible. However, I think you would need a = in the front of the Expression argument in the SetValue macro. SignatureSteve Schapel, Microsoft Access MVP The other thing that SQL Statements don't support is IIF that is not part of the available KEYWORD language set, this is an exclusive MS Access Input condition that is used in VBA or VB for Conditional Input, as far as I know, it is not usable in an SQL Statement and in turn will not process anything in that statement. If there are Queries, and you have these statements in them, then I would suggest what STEVE is saying and use an Update Query, this way and you keep creating entries of items and you work out there Charges and refunds, those can be kept in various tables that when SUM'ed at the time that you need to cut a check for that particular company, you have that current charges and interest payments based on how long it took to get the money back and how long the float was. It just sounds like you're trying to manipulate data at the table level with a form, and unless you are going to code that whole process line by line, it would be a great deal faster is you use an update query(s). -----Original Message----- From: Steve Schapel [mailto:schapel@mvps.org.ns] Posted At: Thursday, June 08, 2006 12:41 PM Posted To: microsoft.public.access.macros Conversation: How to "POST" accounting data into underlying tables Subject: Re: How to "POST" accounting data into underlying tables FJ, FJquestioner wrote: > ... > ... I don't know how to get a marco to run on an object that isn't part of > the currently open form. (Of course I could get the macro to Open the Query > independently but I don't know how to do this without the query physically > showing up on the screen). This is really a job for an Update Query. An Update Query is an "action query" (as against a query that returns a data set), and as such nothing will physically show on the screen. The Update Query would be based on the same sort of data as the query that you have your subform based on. You can use an OpenQuery action in your macro to run the update. > ... This error message about an invalid > field value has me stumped. I have never tried to run a SetValue macro with an IIf() function in the Expression argument, so I'm not 100% sure it's possible. However, I think you would need a = in the front of the Expression argument in the SetValue macro. SignatureSteve Schapel, Microsoft Access MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.