Solved How to make a form to enter multiple entries in table with same form (2 Viewers)

hrdpgajjar

Registered User.
Local time
Today, 20:02
Joined
Sep 24, 2019
Messages
127
Hi all,
i have a table named "Dispatch details". It has following columns,

1. Dispatch Date
2. Party Name
3. Truck Number
4. Freight Charges

I have created a single entry form to enter all details. As per single case dispatch this works but what in case where i have to dispatch for more than one parties in single truck ?

Also i need to calculate freight charges for all the parties material delivered in single truck.


second question is,
what if i need to deliver several trucks with several parties on same day?


I am confused here. Please guide me.


Thanks
 
You do seem to want to make life hard for yourself.
Tables with spaces in the names, fields with spaces in the names :(

Normally this would be handled with a mainform/subform scenario, but that also relies on your data structure.
 
This is a very basic table design problem. Please take a screenshot of your table relationship design and post it here so we can see what you have.
Use Database Tools > Relationships to view your relationships.
 
Hi all,
i have a table named "Dispatch details". It has following columns,

1. Dispatch Date
2. Party Name
3. Truck Number
4. Freight Charges

I have created a single entry form to enter all details. As per single case dispatch this works but what in case where i have to dispatch for more than one parties in single truck ?

Also i need to calculate freight charges for all the parties material delivered in single truck.


second question is,
what if i need to deliver several trucks with several parties on same day?


I am confused here. Please guide me.


Thanks
Hi
Are you able to upload a copy of the database?
 
You are getting detailed advice from the others so I will provide a bit of overview advice.

You have run into a problem that is so common that nobody will be surprised. The solution to this problem is a process called "Database Normalization" and it is used to handle cases like yours where there is an action that is a response to a single event but might involve multiple people or multiple locations or some other "multiple elements" in response to the single cause.

You need to do a search to learn about normalization. Read a few articles and then you will know why you are being told about sub-tables or child tables. Just a hint on the search: If you use the SEARCH option in the upper right of the forum window (to the right of your login name), you can search for "Normalization" because this IS a database forum. However, in a general web search, you must look for "Database Normalization" because at least half-a-dozen other disciplines also use the term "normalization." Therefore, on a general web search, you must be specific enough to narrow the topic.

When you DO that search, you will get all sorts of hits. I recommend you look at the .EDU site hits first, then when you are starting to get a feel for it, go ahead to the .COM sites. There, the only reason isn't that the .COM sites are wrong. They just have something to sell you sometimes and it can be distracting at first.
 
You will need a design something like this so:
  1. Multiple Trucks
  2. Make multiple deliveries on multiple dates
  3. To multiple parties
View attachment 117202
Here is the file. From here you develop your forms, queries and reports. Add or delete fields in each table as needed.
please take a look at the attached database of mine. Now how can i design a form for multiple entries?
 

Attachments

I know this post may seem a little dismissive or rude but it's not meant to be so please don't be offended. There are ideas above which will help solve your problem in the short term, but the nub of the problem depends on having a decent data structure to start with as suggested by the The Doc Man and others.

In excellent source for the necessary learning if you can get hold of a copy of 'Access Database Design & Programming' (ISBN 978-0596002732) which despite being more than 20 years old the chepters 2 - 7 are the best simple introduction to database design!
 
please take a look at the attached database of mine. Now how can i design a form for multiple entries?
Hi

Do Farmers call you to place Orders for Items which you then Dispatch

Please explain your process in more detail.
 
We are going to need to know what kind of business operation this is and what data you want to track.
 
Please remake your tables.
1. Do NOT use ID as the name of the PK in every table. That just confuses you and everyone else and makes relating the tables impossible.
2. When you fix your schema, make sure that the FK name = the PK name of the table it points to. That means that anyone looking at the schema can figure out the relationships even if you didn't draw the lines. So VillageID is the PK for tblVillage and VillageID is the FK in tblFarm that says where the farm is located. It is pulled into the dispatch form only for reference.
3. Do not use special characters or embedded spaces in your object names. Only az, AZ, 09, and the underscore are valid. ALL other characters are invalid as far as VBA is concerned and so to solve this problem VBA will force you to constantly enclose your object names with square brackets []. These look like parentheses in a mass of text and just make the names harder for everyone to read.
4. All names that have numeric prefixes or suffixes are suspect including yours. the repeating group of the Invoice fields belongs in a separate table where you have one row per invoice and only as many rows as you have sent invoices.
5. Payment Status/Date belongs with the invoice so they go into the new Invoice table.
6. Who/what is a Dealer? Is that a Farmer? Be consistent with your names.
7. Now we get to the problem at hand. You need another table and this table can be called "load" or something like that and becomes the parent of Dispatch Details. So a "load" is what goes on the truck and the Dispatch Details is the details of each separate order.

I See:
tblFarm
tblDealer
tblTransporter
tblTransporterTruck
tblVillage
tblDispatch
tblInvoice

Technically Farm, Dealer, Transporter could all be in a single table since they are all businesses and have similar data. Name, address, contact info, etc. Then you would use a RollCD that says what type of business each is. This code would be used to filter the RowSources of combos so that the combo only shows dealers when you want to use it to pick a dealer. Then another combo only shows Farm when you want to use it to pick the farm you are delivering to, etc.
 
Hi

Do Farmers call you to place Orders for Items which you then Dispatch

Please explain your process in more detail.
My Work Process are as under,

- Every farmer (party) has a unique registration number,
- Dispatch done by farmer's call
- There are several farmer's material dispatch together in a single truck to save freight charges
 
We are going to need to know what kind of business operation this is and what data you want to track.
My operation process are as under,

- Each Farmer has unique registration number
- There are several farmer's material dispatch together in a single truck.
- So freight charges are calculated based on single truck\
 
From what you have given us and what you have said, it looks like you will need a table and relationship design something like this:
1732553777409.png

Where each farmer may have multiple orders, and each order dispatch has 3 components:
  1. Dealer
  2. FarmerOrder
  3. Transport truck driver
So, each truck may make deliveries to multiple farmers. Notice there is a new FarmerOrder table that holds each farmers order information.

Does this look like your business operations workflow?
 
My operation process are as under,

- Each Farmer has unique registration number
- There are several farmer's material dispatch together in a single truck.
- So freight charges are calculated based on single truck\
Hi

What does the field "Regi No" relate to? Is this the Truck registration Number?
 
  1. How do you calculate freight charges? Is there a standard rate?
  2. What is the Regi No field and how do you use it?
 
Last edited:

Users who are viewing this thread

Back
Top Bottom