{Access} automatic state via form
gege_p
-
castours -
castours -
Hello,
I just discovered Access, particularly the 2007 version, and it's not easy; any help would be welcome. In my job, I need to create delivery notes, order confirmations, etc. using Access.
So, several tables (with product table, client, etc.)
I want in my delivery note form to only enter a client ID and a product ID (along with quantity, date, etc.) that will populate the delivery note table, and for my delivery note report to be filled with the additional information present in the product and client tables (i.e. product description, client address, client phone number, etc.) automatically.
Because currently, I have to manually fill in this information for it to be displayed in my report, and I don't see the point of using Access.
Thanks to those who will help me.
I just discovered Access, particularly the 2007 version, and it's not easy; any help would be welcome. In my job, I need to create delivery notes, order confirmations, etc. using Access.
So, several tables (with product table, client, etc.)
I want in my delivery note form to only enter a client ID and a product ID (along with quantity, date, etc.) that will populate the delivery note table, and for my delivery note report to be filled with the additional information present in the product and client tables (i.e. product description, client address, client phone number, etc.) automatically.
Because currently, I have to manually fill in this information for it to be displayed in my report, and I don't see the point of using Access.
Thanks to those who will help me.
Configuration: Windows XP Firefox 3.0.11
22 answers
- 1
- 2
Next
Hello,
Finally I'm unblocked, it was really simple but you had to know it.
In properties, data, Filter by master-replica: NO
There, if it can help someone else...
Next step is stock management, another mountain to climb.
See you soon and thank you for your help.
Finally I'm unblocked, it was really simple but you had to know it.
In properties, data, Filter by master-replica: NO
There, if it can help someone else...
Next step is stock management, another mountain to climb.
See you soon and thank you for your help.
Hello gege_p,
Microsoft offers a tutorial for ACCESS available for download. It is a commercial management database; it contains the features you are looking for. It is freely accessible. It is a good reference base.
http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en
Best regards
--
Science only finds what has existed since forever.
REEVES Hubert.
Microsoft offers a tutorial for ACCESS available for download. It is a commercial management database; it contains the features you are looking for. It is freely accessible. It is a good reference base.
http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en
Best regards
--
Science only finds what has existed since forever.
REEVES Hubert.
zenon,
Generally speaking, an earlier version of ACCESS usually opens via a more modern version.
Upon opening, a sort of upgrade occurs. This is the principle of backward compatibility.
Sometimes the conversion is not 100%, however, I haven't encountered anything blocking yet.
This does not preclude taking precautions and having backups... But I am confident.
Have a good day
--
Science only discovers what has existed since forever.
REEVES Hubert.
Generally speaking, an earlier version of ACCESS usually opens via a more modern version.
Upon opening, a sort of upgrade occurs. This is the principle of backward compatibility.
Sometimes the conversion is not 100%, however, I haven't encountered anything blocking yet.
This does not preclude taking precautions and having backups... But I am confident.
Have a good day
--
Science only discovers what has existed since forever.
REEVES Hubert.
Hi,
Thank you for the information, yes he is using Access 2007, I will check out this tutorial and keep you updated.
But feel free to reach out if you have any answers.
Have a good evening.
Thank you for the information, yes he is using Access 2007, I will check out this tutorial and keep you updated.
But feel free to reach out if you have any answers.
Have a good evening.
I just looked at the management database you mentioned, Jean_Jacques. Indeed, it's the best, but it's hard to implement at my level. However, I borrowed some concepts, but they don't really address my problem.
Does anyone have a solution to my problem???
Thanks in advance.
Does anyone have a solution to my problem???
Thanks in advance.
Good evening,
Unfortunately, I think you'll have to "get your hands dirty."
I don't believe you'll find someone to design an entire application... Moreover, if you want to master your database, the only way is to learn how to build it.
That said, for more "focused" questions, no problem...
It's true that the sample database proposed by Jean-Pierre seems very complex at first glance. You might want to start by using the structure of the tables and the relationships as inspiration: in the commercial field, it's always a bit of the same principle.
If you have difficulties with forms or reports, come back with more specific questions.
Good luck!
Unfortunately, I think you'll have to "get your hands dirty."
I don't believe you'll find someone to design an entire application... Moreover, if you want to master your database, the only way is to learn how to build it.
That said, for more "focused" questions, no problem...
It's true that the sample database proposed by Jean-Pierre seems very complex at first glance. You might want to start by using the structure of the tables and the relationships as inspiration: in the commercial field, it's always a bit of the same principle.
If you have difficulties with forms or reports, come back with more specific questions.
Good luck!
Good evening Zenon,
I don't want someone to create my comic for me; my comic is already in place, I just lack the knowledge to do some programming in Access.
Let me try to clarify my problem:
Currently, my file can generate "delivery note" reports, but it is not practical and is prone to errors during the entry of the delivery notes.
Indeed, when I enter a delivery note (via the delivery note form), I have to specify line by line (using a dropdown list) the information I want to display in the report.
My wish is to enter only a client code (which will be the client ID) that is currently present in my client table, and for the information of this client (address, phone, fax,...) to be re-entered in the delivery note table to edit the delivery note report.
Do you have a solution to this problem? Or another method to display client information in the report?
Thank you very much.
I don't want someone to create my comic for me; my comic is already in place, I just lack the knowledge to do some programming in Access.
Let me try to clarify my problem:
Currently, my file can generate "delivery note" reports, but it is not practical and is prone to errors during the entry of the delivery notes.
Indeed, when I enter a delivery note (via the delivery note form), I have to specify line by line (using a dropdown list) the information I want to display in the report.
My wish is to enter only a client code (which will be the client ID) that is currently present in my client table, and for the information of this client (address, phone, fax,...) to be re-entered in the delivery note table to edit the delivery note report.
Do you have a solution to this problem? Or another method to display client information in the report?
Thank you very much.
Hi,
Here are my relationships:
- Clients table:
client code
company
last name
first name
address
...
- Delivery order table:
Delivery order number
client code
order date
delivery date
total amount excluding VAT
...
- Delivery order lines table:
line number
delivery order number
Reference
Description
quantity
unit price
line amount
- Products table:
Reference
description
unit price
They are all linked by a field (client code, delivery order number, reference).
Thank you for your help.
Here are my relationships:
- Clients table:
client code
company
last name
first name
address
...
- Delivery order table:
Delivery order number
client code
order date
delivery date
total amount excluding VAT
...
- Delivery order lines table:
line number
delivery order number
Reference
Description
quantity
unit price
line amount
- Products table:
Reference
description
unit price
They are all linked by a field (client code, delivery order number, reference).
Thank you for your help.
Good evening,
I wonder if you have an extra table "to spare" (for once)
I'm not sure you need a delivery note line table.
The date and the client number probably allow you to extract what you need from the delivery note table.
If we look at creating a "delivery note" report, if you have:
T_Clients (ClientNumber, Address...)
T_Products (ProductNumber, ProductDescription, UnitPrice,....)
T_Sales (SaleNumber, ProductNumber, ClientNumber, Quantity, OrderDate,...)
We can base the report on a query like this:
SELECT T_Clients.*, T_Products.*, T_Sales.*, T_Clients.ClientNumber, T_Sales.SaleDate
FROM T_Products INNER JOIN (T_Clients INNER JOIN T_Sales ON T_Clients.ClientNumber = T_Sales.ClientNumber) ON T_Products.ProductNumber = T_Sales.ProductNumber
WHERE (((T_Clients.ClientNumber)=ActiveForm) AND ((T_Sales.SaleDate)=Date()));
Then calculations can be done directly in the report.
I wonder if you have an extra table "to spare" (for once)
I'm not sure you need a delivery note line table.
The date and the client number probably allow you to extract what you need from the delivery note table.
If we look at creating a "delivery note" report, if you have:
T_Clients (ClientNumber, Address...)
T_Products (ProductNumber, ProductDescription, UnitPrice,....)
T_Sales (SaleNumber, ProductNumber, ClientNumber, Quantity, OrderDate,...)
We can base the report on a query like this:
SELECT T_Clients.*, T_Products.*, T_Sales.*, T_Clients.ClientNumber, T_Sales.SaleDate
FROM T_Products INNER JOIN (T_Clients INNER JOIN T_Sales ON T_Clients.ClientNumber = T_Sales.ClientNumber) ON T_Products.ProductNumber = T_Sales.ProductNumber
WHERE (((T_Clients.ClientNumber)=ActiveForm) AND ((T_Sales.SaleDate)=Date()));
Then calculations can be done directly in the report.
Good evening,
I just adapted your formula to my situation.
A dialog box opens when displaying the query telling me that I have a "syntax error (missing operator) in the expression 'Reseller Clients.Client Code'".
I entered the formula while carefully respecting the names of my tables and ...
Thank you in advance for your help.
I just adapted your formula to my situation.
A dialog box opens when displaying the query telling me that I have a "syntax error (missing operator) in the expression 'Reseller Clients.Client Code'".
I entered the formula while carefully respecting the names of my tables and ...
Thank you in advance for your help.
It's hard to imagine out of context...
Maybe you should try using [Reseller Clients].[Client Code]
Access doesn't automatically recognize fields if there are spaces.
Maybe you should try using [Reseller Clients].[Client Code]
Access doesn't automatically recognize fields if there are spaces.
Hello,
The formula is correct now; it was indeed the [] that were needed.
But now when I open the request, it asks me:
- a value for the parameter Clients resellers.Client code
and
- a value for the active form parameter
What should I put for the request to work?
Thank you for the help.
The formula is correct now; it was indeed the [] that were needed.
But now when I open the request, it asks me:
- a value for the parameter Clients resellers.Client code
and
- a value for the active form parameter
What should I put for the request to work?
Thank you for the help.
It's also hard to imagine.
I was thinking that you would open the report from a form.
In this form, we would have found the client number (to which the invoice is issued) and the references of the products sold.
Then you would need to replace "active form" with... the name of the active form from which you open the report. It also works if currently you only want to execute the query.
I can't guess the name you've given to this form. If you are in testing phase, you can manually enter a client number to check.
In principle, to reference the active form, its name is enough, or then Me!FormName (the real one) otherwise the full reference: Forms!FormName!ClientNumber
I was thinking that you would open the report from a form.
In this form, we would have found the client number (to which the invoice is issued) and the references of the products sold.
Then you would need to replace "active form" with... the name of the active form from which you open the report. It also works if currently you only want to execute the query.
I can't guess the name you've given to this form. If you are in testing phase, you can manually enter a client number to check.
In principle, to reference the active form, its name is enough, or then Me!FormName (the real one) otherwise the full reference: Forms!FormName!ClientNumber
It's working fine. I just need to adapt it to my situation.
Thank you for your help, I might need you later.
Have a good evening.
Thank you for your help, I might need you later.
Have a good evening.
As I told you, I need help. I'm sorry.
The program you told me about is great, but it doesn't completely meet my expectations.
So I will rephrase my request:
When I create a delivery note, I fill in my delivery note table with a delivery note number, 2 customer codes (one for the delivery address, another for the billing address), delivery date, order date.
Then a subform "Delivery Note Lines" which details the note: reference, quantity, discount.
I would like to create a report that can display the corresponding addresses of the customers (delivery and billing), using the selected customer codes, as well as the designation and price of the selected products using the product references. This information will complement my delivery note, in addition to what I have filled in the Delivery Note and Delivery Note Lines tables. Moreover, the price will also be useful to perform calculations (net amount, total net amount,...) via the report.
I hope my explanation is clear enough.
Thank you in advance for your help.
The program you told me about is great, but it doesn't completely meet my expectations.
So I will rephrase my request:
When I create a delivery note, I fill in my delivery note table with a delivery note number, 2 customer codes (one for the delivery address, another for the billing address), delivery date, order date.
Then a subform "Delivery Note Lines" which details the note: reference, quantity, discount.
I would like to create a report that can display the corresponding addresses of the customers (delivery and billing), using the selected customer codes, as well as the designation and price of the selected products using the product references. This information will complement my delivery note, in addition to what I have filled in the Delivery Note and Delivery Note Lines tables. Moreover, the price will also be useful to perform calculations (net amount, total net amount,...) via the report.
I hope my explanation is clear enough.
Thank you in advance for your help.
To create a report based on multiple tables, your tables need to be linked.
At first glance, I understand that there is a relationship between your order table and your client table.
However, I don't see how you link the products and the order, but maybe you're filling them in manually.
To create a report with customer address information, you have two options: base the report on a query that includes the different tables or create a subreport (which I think is better)
Subreports work the same way as subforms. If you include the client number in the report, you can display the corresponding subreport with the parent and child field information.
At first glance, I understand that there is a relationship between your order table and your client table.
However, I don't see how you link the products and the order, but maybe you're filling them in manually.
To create a report with customer address information, you have two options: base the report on a query that includes the different tables or create a subreport (which I think is better)
Subreports work the same way as subforms. If you include the client number in the report, you can display the corresponding subreport with the parent and child field information.
Good evening,
I know how to create a subform, but on the other hand, I just tried to create a subreport and I can't find the parent and child fields.
Is there a specific condition to create subreports?
(namely displaying "the parent and child fields")
Thank you in advance.
I know how to create a subform, but on the other hand, I just tried to create a subreport and I can't find the parent and child fields.
Is there a specific condition to create subreports?
(namely displaying "the parent and child fields")
Thank you in advance.
Hello, after some thought, I will try to print my delivery notes via the form; it will certainly be simpler. But here's the thing:
I just created a form with a subform to create delivery notes. Both work perfectly separately and even together.
The problem arises when I link the two forms with parent and child fields (to only display the details of the delivery notes by delivery note: reference, description, price, etc.).
Indeed, before they were linked, I could fill in the subform by entering the desired reference via a dropdown list, which automatically filled in the description, price, etc.
Once linked, I can no longer fill in my reference field; it tells me: "The field cannot be modified, it is linked to the unknown field [Lignes BL].reference" (which is where the value of the dropdown list is stored).
Do you have a solution?
Thank you in advance for your help.
I just created a form with a subform to create delivery notes. Both work perfectly separately and even together.
The problem arises when I link the two forms with parent and child fields (to only display the details of the delivery notes by delivery note: reference, description, price, etc.).
Indeed, before they were linked, I could fill in the subform by entering the desired reference via a dropdown list, which automatically filled in the description, price, etc.
Once linked, I can no longer fill in my reference field; it tells me: "The field cannot be modified, it is linked to the unknown field [Lignes BL].reference" (which is where the value of the dropdown list is stored).
Do you have a solution?
Thank you in advance for your help.
gege_p,
We need to start off on the right foot.
Here is a file to download that comes from the default examples provided with ACCESS 2000.
For your information, the path under ACCESS 2000 is: File/New Database/Database Tab
Name: Order Processing (A whole program if I dare the pun).
http://www.cijoint.fr/cjlink.php?file=cj200907/cijrdGJXuL.zip
Northwind may have been too ambitious for a start.
Let’s get to work
Best regards
--
Science only finds what has existed since forever.
REEVES Hubert.
We need to start off on the right foot.
Here is a file to download that comes from the default examples provided with ACCESS 2000.
For your information, the path under ACCESS 2000 is: File/New Database/Database Tab
Name: Order Processing (A whole program if I dare the pun).
http://www.cijoint.fr/cjlink.php?file=cj200907/cijrdGJXuL.zip
Northwind may have been too ambitious for a start.
Let’s get to work
Best regards
--
Science only finds what has existed since forever.
REEVES Hubert.
Jean_Jacques
Thank you for your response, I downloaded the file which will be very useful for finalizing my comic book.
But here it is, my database is well advanced and I don't plan to redo everything (unless I have no choice) I just need the solution to the specific problem that is currently blocking me from completing my project.
I'm sure the solution is quite simple.
Thank you in advance for your valuable help.
Thank you for your response, I downloaded the file which will be very useful for finalizing my comic book.
But here it is, my database is well advanced and I don't plan to redo everything (unless I have no choice) I just need the solution to the specific problem that is currently blocking me from completing my project.
I'm sure the solution is quite simple.
Thank you in advance for your valuable help.
Hello gege_p,
I'm sorry, I only have the 2000 version of ACCESS. So, I don't even have the option to look at your work.
The easiest thing to do is to "pump" the solutions found in 'Northwind' or 'Order Processing' to adapt them to your context.
Best regards,
--
Science only discovers what has existed since forever.
REEVES Hubert.
I'm sorry, I only have the 2000 version of ACCESS. So, I don't even have the option to look at your work.
The easiest thing to do is to "pump" the solutions found in 'Northwind' or 'Order Processing' to adapt them to your context.
Best regards,
--
Science only discovers what has existed since forever.
REEVES Hubert.
- 1
- 2
Next