Solved How to make a form to enter multiple entries in table with same form

[hrdpgajjar]

Hi

What does the field "Regi No" relate to? Is this the Truck registration Number?

No Regi. No is Unique code given to each farmer (Party) and its not duplicate

 

[bob fitz]

That's not the case mate. Anyways helping is your decision solely.

JarryE said:

Yeah, they come in here wanting help using ACCESS as a spreadsheet and wonder why it doesn't work

I'm a little bit confused. If you think you have a good and functional design why would you not ask why the comment has been made?
The file you posted in post #7 is indeed, an Access file. However, IMHO it can not be argued that it has a good and functional design.
For example:
It has four tables without any relationships.
Three of the tables have a PK called ID. The other has no PK.
The names of Tables, Forms, Fields, and Controls have spaces. One table even uses a hyphen in numerous fields.
The use of field names like Invoice No-2, Invoice No-3, Invoice No-4, Invoice No-5 is typical of a poorly designed db.

Databases have a similarity to houses. If the design and foundations are poor, they soon become unfit for purpose.

Good luck with your project.

 

[LarryE]

@hrdpgajjar :
I took the time to re-design the structure so it is in proper relational format and the data is normalized. That was the only way you can allow a single truck to deliver to multiple farmers on a single day. If you are interested, I will attach the file. Just let me know if you are interested.

 

[Pat Hartman]

@hdrpgajjar None of us gets paid to help you. We do it because we like solving problems and helping people. It is frustrating and disappointing when you completely ignore posts and eventually, people stop trying to help you. I also spent time thinking about your problem and offering constructive suggestions for moving forward and was completely ignored. Hope the others can solve your problem for you.

 

[mike60smart]

Freight Charges are standard and Registration No field is unique per farmer (no duplication)

Hi
Your Freight Charges are as shown below/ How do you determine how much to charge for Freight Charges?

Freight Charges

₹ 3,000.00

₹ 6,500.00

₹ 7,000.00

₹ 8,500.00

₹ 9,000.00

₹ 10,000.00

₹ 10,500.00

₹ 11,500.00

₹ 12,500.00

₹ 12,000.00

 

[mike60smart]

No Regi. No is Unique code given to each farmer (Party) and its not duplicate

Hi

You should have your Relationships between tables set out as shown below. This might need changing depending on further details of your process.

Also, when you do your data input you would use a Main Form for your Customer, a Subform for the related Order Date details and a further related Subform to record the Actual Order details.

Then for Each Order you need a further Subform to record the Invoice Details.

 

[hrdpgajjar]

Hi

You should have your Relationships between tables set out as shown below. This might need changing depending on further details of your process.

Also, when you do your data input you would use a Main Form for your Customer, a Subform for the related Order Date details and a further related Subform to record the Actual Order details.

Then for Each Order you need a further Subform to record the Invoice Details.

Ok, i got it now. Will let you know if i found some problem doing this. Thanks

 

[hrdpgajjar]

Hi
Your Freight Charges are as shown below/ How do you determine how much to charge for Freight Charges?

Freight Charges

₹ 3,000.00

₹ 6,500.00

₹ 7,000.00

₹ 8,500.00

₹ 9,000.00

₹ 10,000.00

₹ 10,500.00

₹ 11,500.00

₹ 12,500.00

₹ 12,000.00

Standard freight charges means, charges are fixed based on distance from ware house to delivery address.

 

[mike60smart]

OK so instead of having to manually enter the freight charge you can automate this by using an IIf statement.

 

[hrdpgajjar]

OK so instead of having to manually enter the freight charge you can automate this by using an IIf statement.

yes i can do that. but my prime concern is how to enter values in multiple tables with a single form. Which is now solved by your previous post with screen shot. thank you.

 

[hrdpgajjar]

@hrdpgajjar :
I took the time to re-design the structure so it is in proper relational format and the data is normalized. That was the only way you can allow a single truck to deliver to multiple farmers on a single day. If you are interested, I will attach the file. Just let me know if you are interested.

pls send the attachments, it will be great help. thanks in advance

 

[mike60smart]

pls send the attachments, it will be great help. thanks in advance

The idea is that you make an effort to create the Forms as shown in the example screenshots.

 

[hrdpgajjar]

@hdrpgajjar None of us gets paid to help you. We do it because we like solving problems and helping people. It is frustrating and disappointing when you completely ignore posts and eventually, people stop trying to help you. I also spent time thinking about your problem and offering constructive suggestions for moving forward and was completely ignored. Hope the others can solve your problem for you.

I can understand your concern. But the problem is that we are living in different places and different time zones. When i see your answer and reply to that it will take some time due to this. so technically i am not ignoring any reply or posts. Just need some time nothing more. Thanks for the reply though.

 

[LarryE]

@hrdpgajjar :
I just noticed that each farmer could have multiple planting areas which could have multiple irrigation delivery orders. That means a new table to hold each planting area for each farmer. Did I get that right?

 

[LarryE]

Here is a file for you that is relational with data normalized into appropriate tables. I assumed the following:

1. You sell irrigation equipment to farmers around your region
2. Each farmer may have multiple planting areas
3. Each planting area may require equipment resulting in an order call
4. Each order call results in a dispatch order
5. Each dispatch order contains an Equipment dealer designation an MIS cost, a dispatch date and Transport company truck and driver information.
6. Each dispatch results in a least one invoice

I added a FreightRatePerKm field to the Transporter table so each transporter can have a Rate Per Km, and each planting area contains a distance field which can be multiplied by the Transporters FreightRatePerKm to calculate it. I also add some test rates for each transporter.
You need to go through each farmer and order to determine it was converted correctly.

 

[hrdpgajjar]

@hrdpgajjar :
I just noticed that each farmer could have multiple planting areas which could have multiple irrigation delivery orders. That means a new table to hold each planting area for each farmer. Did I get that right?

Yes thats correct

 

[LarryE]

Yes thats correct

Thank you. I noticed problems with the file I attached, so just delete it if you downloaded it. I will work on a new one.

 

[LarryE]

@hrdpgajjar :
Here is a file that does what you asked. I added a confirmation message if deleting records for safety reasons. You can search in the Farmer form like you did before but you just hit the Enter key instead of clicking a button. The subform attached to the Dispatch form will find Farmer and Dispatch information by selecting the record you want to display and then click the button. It does not filter the data, it simply finds it.

You are welcome to use it as you wish. Make sure each Farmer, Area, Order and Dispatch record is accurate. The Freight Charge on the Invoice form is a calculated value now based upon the standard freight rate for each transporter and the distance the planting area is. You need to complete these values.

 

[hrdpgajjar]

@hrdpgajjar :
Here is a file that does what you asked. I added a confirmation message if deleting records for safety reasons. You can search in the Farmer form like you did before but you just hit the Enter key instead of clicking a button. The subform attached to the Dispatch form will find Farmer and Dispatch information by selecting the record you want to display and then click the button. It does not filter the data, it simply finds it.

You are welcome to use it as you wish. Make sure each Farmer, Area, Order and Dispatch record is accurate. The Freight Charge on the Invoice form is a calculated value now based upon the standard freight rate for each transporter and the distance the planting area is. You need to complete these values.

Thanks for your all the efforts, appreciate it.