{Access} automatic state via form

Question

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.

Jean_Jacques · Answer

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.

Jean_Jacques · Answer

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.

gege_p · Answer

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.

gege_p · Answer

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.

zenon · Answer

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!

gege_p · Answer

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.

gege_p · Answer

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.

zenon · Answer

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.

gege_p · Answer

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.

zenon · Answer

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.

gege_p · Answer

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.

zenon · Answer

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

gege_p · Answer

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.

gege_p · Answer

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.

zenon · Answer

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.

gege_p · Answer

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.

gege_p · Answer

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.

gege_p · Answer

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.

gege_p · Answer

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.

Jean_Jacques · Answer

gege_p,   So far, I have not dealt with this parameter. Thank you for the feedback.   See you! -- Science only finds what has existed since forever. REEVES Hubert.

gege_p · Answer

Hello,

This function filtered (even blocked) the records from my tables that I wanted to display in a subform.

I am using Access 2007, this might be a new feature.

Another thing, my Access file allows me to create delivery notes and order confirmations.

By default, a product has X% or Y% discount for customers. In my Products table, all values are recorded, and when creating a delivery note via the form, these discounts are automatically displayed, which prevents errors.

But sometimes there are promotional offers where the X discount changes to Z% under certain conditions.

My question is: Is it possible to modify the value X to Z in the form without changing my default value X in the Products table?

Thank you very much for your responses.

castours · Answer

Hello To make it simpler, upload your database to the site with Cjoint.com. Everyone from the forum will be able to see your database and may offer you various solutions.

{Access} automatic state via form - Page 2

Impossible to log into my alice emails

Ccm asks me for one euro when i use duckduckgo

High cpu usage when opening folders

Where are the sfr emails (red by sfr)..?

Urgent - laptop pc cannot boot/start up

bios update boot loop

Search for software similar to google desktop

How to recover a missing microsoft equation?

Blocked internet link

Ram upgrade