what do you mean by control?? the table??
and i know that i should choose either one
see the attachment
what do you mean by control?? the table??
and i know that i should choose either one
i deleted it right now and working on the VBA way
i'll reply later with a screen shot
Thak you
Private Sub YourControlName_AfterUpdate()
Dim strSql As String
strSql = "UPDATE Sales INNER JOIN Revenues ON Sales.Customer=Revenues.Customer AND Sales.[Date]=Revenues.[Invioce Date] SET Sales.[Paid On] = Revenues.[Payment Date] WHERE Sales.[Paid On] Is Null;"
CurrentDb.Execute strSql, dbFailOnError
End Sub
Textboxes, labels etc are controls on your form.
Back in post #24 you referred to an after update event. Which control was that for? That's where the event code is required.
So use that control name in the code below.
Note that I've corrected your sql though I think your InvoiceDate field is spelt wrongly.
Code:Private Sub YourControlName_AfterUpdate() Dim strSql As String strSql = "UPDATE Sales INNER JOIN Revenues ON (Sales.[Customer]=Revenues.[Customer]) AND (Sales.[Date]=Revenues.[Invioce Date]) SET Sales.[Paid On] = Revenues.[Payment Date] WHERE Sales.[Paid On] Is Null;" CurrentDb.Execute strSql, dbFailOnError End Sub
About to log off as its late here. Hope you get the idea now.
Congratulations! I knew you'll get it to work. Cheers!THANK YOU VERY MUCH
it worked great
now how to add a refresh code to this VBA, so it refresh the form after each form update
Some competent people just helped you to make a big mistake. When you build your application on a foundation of quicksand, how can it hold up over time? You are new to Access. The experts told you how to solve the problem. You said "no thanks, I'd rather do it wrong" and they helped you. You'll be back with other issues regarding how to change this data now that it is stored in multiple places and someone will help you again.is there any chance to use VBA over my current database??
as i have entered too much data to repeat it
Hi Pat. Thanks for the assist. Very well said. However, I would prefer to think of it as not helping the OP "make" a big mistake; but rather, helping the OP "continue" (it's already done) making mistakes. As you may have noted, I tried to offer a proper solution in Post #4; but in the end, it's the OP's database, and we can't force them to do something they don't want to do, right? I think all we can do, sometimes, is just warn them. Just my 2 cents...Some competent people just helped you to make a big mistake. When you build your application on a foundation of quicksand, how can it hold up over time? You are new to Access. The experts told you how to solve the problem. You said "no thanks, I'd rather do it wrong" and they helped you. You'll be back with other issues regarding how to change this data now that it is stored in multiple places and someone will help you again.
The amount of data in a table has nothing whatsoever to do with fixing the table structure since the fix would never involve retyping anything. It would be done with a couple of queries. Or, in this case, simply deleting the columns with the duplicated data and replacing the RecordSources of forms/reports with a query that joins two tables. What is relevant is how many queries, forms, reports, and modules you have to change. Even then, it is normally better to take the hit and fix the underlying problem rather than putting on a band aid and closing your eyes to it.
I saw that you tried to actually solve his problem rather than put a bandaid on it. You know the old saying - you can bring a horse to water but you can't make him drink. That's what we have here. I just didn't want anyone who came across this later to think that this problem actually got solved.
Try
SELECT [Sales Query].Customer
FROM [Sales Query]
WHERE ((([Sales Query].[Paid On]) Is Null)) AND Customer = Forms!FormName.ComboName
ORDER BY [Sales Query].Customer;