auto populate table fields (1 Viewer)

[ElPerson]

Hi
I have Sales Table and a revenue Table
each of them have these fields "CusName""SaleDate""PaymentDate"

I would like to make access auto populate the "PaymentDate" field in Sales Table based on the entry to the revenue table once the "CusName"&"SaleDate" are equal.

Thank you

 

[theDBguy]

Hi. Welcome to AWF! What do you mean by "once the CustName & SaleDate are equal?" When do they get changed? Are you keeping track of the date in multiple records? I could be wrong, but it sounds like you may have a bad table design if you're storing redundant information in multiple locations.

 

[ElPerson]

Hi. Welcome to AWF! What do you mean by "once the CustName & SaleDate are equal?" When do they get changed? Are you keeping track of the date in multiple records? I could be wrong, but it sounds like you may have a bad table design if you're storing redundant information in multiple locations.

If i make a Sale i enter the data using form to the sale table as follows
"CusName" "SaleDate" but the third field left empty "PaymentDate"
waiting till the payment made at that point i use form to rnter the payment data to the revenue table as follows
"CusName" "SaleDate" and "PaymentDate"
i want access to monitor these two fields "CusName" "SaleDate" once the equal it copy the "PaymentDate" from revenue table to sales table

Thank you

 

[theDBguy]

If i make a Sale i enter the data using form to the sale table as follows
"CusName" "SaleDate" but the third field left empty "PaymentDate"
waiting till the payment made at that point i use form to rnter the payment data to the revenue table as follows
"CusName" "SaleDate" and "PaymentDate"
i want access to monitor these two fields "CusName" "SaleDate" once the equal it copy the "PaymentDate" from revenue table to sales table

Thank you

Hi. Here's what I would suggest...


Use three tables as follows:


tblCustomers
CusID, PK
CusName


tblSales
SaleID, PK
CusID, FK
SaleDate


tblPayments
PmtID, PK
SaleID, FK
PmtDate
Amt


With the above structure, you can use a form/subform setup to enter sales and payments.

 

[ElPerson]

is there any chance to use VBA over my current database??

as i have entered too much data to repeat it

Thank you

 

[theDBguy]

is there any chance to use VBA over my current database??

as i have entered too much data to repeat it

Thank you

Well, you shouldn't need VBA. You should be able to use an UPDATE query.


PS. How much data are we talking about. You should also be able to easily migrate it to the new structure using queries. Once you got the correct structure and using proper forms, you'll have less problems later on.

 

[ElPerson]

Well, you shouldn't need VBA. You should be able to use an UPDATE query.

how to use it based on my tables as i told you

 

[theDBguy]

how to use it based on my tables as i told you

Something like:

UPDATE Table1
INNER JOIN Table2
ON Table1.CusName=Table2.CusName
AND Table1.SaleDate=Table2.SaleDate
SET Table1.PaymentDate=Table2.PaymentDate
WHERE Table1.PaymentDate Is Null

 

[ElPerson]

Something like:

UPDATE Table1
INNER JOIN Table2
ON Table1.CusName=Table2.CusName
AND Table1.SaleDate=Table2.SaleDate
SET Table1.PaymentDate=Table2.PaymentDate
WHERE Table1.PaymentDate Is Null

would you please guide me where to enter this code in access 2019

Thank you

 

[theDBguy]

would you please guide me where to enter this code in access 2019

Thank you

It's a query. You would enter it in the query designer on SQL View. However, you'll need to change the name of the tables. I forgot to use Sales and Revenue table, but Table1 would be the one you want to update, which I think is the Sales table.

 

[ElPerson]

It's a query. You would enter it in the query designer on SQL View. However, you'll need to change the name of the tables. I forgot to use Sales and Revenue table, but Table1 would be the one you want to update, which I think is the Sales table.

i did what you told me and got error message
syntax error missing operator in query expression ".

 

[theDBguy]

i did what you told me and got error message
syntax error missing operator in query expression ".

Can you post the SQL statement you tried to use?

 

[ElPerson]

Can you post the SQL statement you tried to use?

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
SELECT Sales.id, Sales.Customer, Sales.Discount, Sales.Quantity, [quantity]*(55.5-[discount]) AS Price, Sales.Date, Sales.[Paid On]
FROM Sales;

 

[theDBguy]

Hi. You can't combine an UPDATE query with a SELECT query. Remove the SELECT part and enclose your field names in square brackets, since you use reserved words and spaces in the names.

 

[ElPerson]

Hi. You can't combine an UPDATE query with a SELECT query. Remove the SELECT part and enclose your field names in square brackets, since you use reserved words and spaces in the names.

it worked great

Thank you

 

[theDBguy]

it worked great

Thank you

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.

 

[ElPerson]

how to turn the warning message Off that appear when i run the query??

 

[isladogs]

If you're using a query, you can turn off warning messages by going to File...Client Settings ...Confirm then un-tick Action Queries.
However this applies globally and I wouldn't recommend it as important info may be hidden.

If doing this in code, you can use

CurrentDb.Execute "INSERT INTO ….", dbFailOnError

This will allow error messages but hide the standard messages about inserting X rows etc

 

[theDBguy]

how to turn the warning message Off that appear when i run the query??

Hi. As Colin said, you can turn off this settings or use VBA to bypass it. But if you would rather use a macro, you can use the SetWarnings action to also turn it Off before running the query and then don't forget to turn it back On after running the query. Cheers!

 

[ElPerson]

Hi. As Colin said, you can turn off this settings or use VBA to bypass it. But if you would rather use a macro, you can use the SetWarnings action to also turn it Off before running the query and then don't forget to turn it back On after running the query. Cheers!

i mean is there an option to turn it off completely for this query only??