see the attachment
Attachments
-
Annotation 2019-11-03 005011.jpg
auto populate table fields (1 Viewer)
- Thread starter ElPerson
- Start date
see the attachment
- Local time
- Today, 00:42
- Joined
- Oct 29, 2018
- Messages
- 21,447
Understood. I was just thinking you might be interested to know why it didn't work. That's all. Cheers!
isladogs
MVP / VIP
- Local time
- Today, 08:42
- Joined
- Jan 14, 2017
- Messages
- 18,207
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.
Also you shouldn't use Date as a field name as its an Access function name. SalesDate or similar would be ok
About to log off as its late here. Hope you get the idea now.
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 SubAlso you shouldn't use Date as a field name as its an Access function name. SalesDate or similar would be ok
About to log off as its late here. Hope you get the idea now.
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
- Local time
- Today, 00:42
- Joined
- Oct 29, 2018
- Messages
- 21,447
Congratulations! I knew you'll get it to work. Cheers!
- Local time
- Today, 03:42
- Joined
- Feb 19, 2002
- Messages
- 43,196
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.
- Local time
- Today, 00:42
- Joined
- Oct 29, 2018
- Messages
- 21,447
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...
- Local time
- Today, 03:42
- Joined
- Feb 19, 2002
- Messages
- 43,196
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.
actually it's Solved, and as you know access is a huge world of opportunities and every thing can be done in multiple ways
i'm new to access and managed to create a great database that do everything i want it to be done, with the help of the GREAT PEOPLE in this forum, so Thank you guys
- Local time
- Today, 03:42
- Joined
- Feb 19, 2002
- Messages
- 43,196
If you're happy, we're happy. But this forum is searched by many people, even years after the fact. It's not fair to them to think that this is a real solution. The band aid stopped the bleeding. But the bone never got set so your arm is going to be crooked forever. OK.
This isn't just a difference of opinions regarding a "best" technique. This a difference between wrapping your arm in a bandage or actually fixing the break. In the end, the arm will heal. In one case, you'll have limited mobility and strength. Perhaps even be plagued forever with pain. In the other, it will be as good as new.
This isn't just a difference of opinions regarding a "best" technique. This a difference between wrapping your arm in a bandage or actually fixing the break. In the end, the arm will heal. In one case, you'll have limited mobility and strength. Perhaps even be plagued forever with pain. In the other, it will be as good as new.
Hi Guys
now i am very satisfied with what you guide me to make
I noticed that every time i try to insert a revenue to my database in the form i keep entering the invoice date manually!! while its already inserted earlier in the sales table
in the revenue form i created a combo box which showing only the unpaid invoices using the following
SELECT [Sales Query].Customer
FROM [Sales Query]
WHERE ((([Sales Query].[Paid On]) Is Null))
ORDER BY [Sales Query].Customer;
i there a way to make the invoice date showing only the dates for the customer selected in the combo box?
see the attachment
now i am very satisfied with what you guide me to make
I noticed that every time i try to insert a revenue to my database in the form i keep entering the invoice date manually!! while its already inserted earlier in the sales table
in the revenue form i created a combo box which showing only the unpaid invoices using the following
SELECT [Sales Query].Customer
FROM [Sales Query]
WHERE ((([Sales Query].[Paid On]) Is Null))
ORDER BY [Sales Query].Customer;
i there a way to make the invoice date showing only the dates for the customer selected in the combo box?
see the attachment
Attachments
-
WhatsApp Image 2019-11-04 at 21.10.24.jpg
- Local time
- Today, 00:42
- Joined
- Aug 30, 2003
- Messages
- 36,131
Sorry, I think I misread the question. You want the date associated with the customer? Would there only be one? In the after update event of the customer combo you could use a DLookup to find the related date. Or if appropriate, include the date field in the combo and get it this way:
http://www.baldyweb.com/Autofill.htm
http://www.baldyweb.com/Autofill.htm
