sub form data not find

[deepaksharma]

thanks for reply...

sir, you are right in fact when i was uploading this file on this site then this site was blocking me due to size limit then i zipped this file then something went wrong with it.
please, download this file from this link and please check it......

https://drive.google.com/file/d/1U2N...w?usp=drivesdk

[deepaksharma]

If I want, I can go to the previous record and update the data, so i have used bound field.

[deepaksharma]

[12]

very very thanks for very very useful tips...

and i want to tell you that i can not use auto number in main table (grey offer), because there is a problem with this that when number is deleted, that number disappears permanently.


and i want to tell you that my data will go more than 2gb. please give me specific suggestion for this also.

[deepaksharma]

i am talking about code for a control

[deepaksharma]

I can open with Access 2010. Don't know why can't open with 2007.

There is no Private Sub TXT0_AfterUpdate() code in the posted db.

As already advised, control used to enter/select search/filter criteria must be UNBOUND otherwise you change data in record. Use UNBOUND controls in form header for search. Review http://allenbrowne.com/ser-62.html

Why is ID field in GREY_OFFER not an autonumber? Why is there a 0 ID record? A field named ID is commonly accepted to indicate a field with unique values. This is not the case with GREY_SHADE table. This table's ID field is a foreign key for GREY_OFFER ID value. Give the fields better names, like: OfferID and OfferID_FK. Both of these textboxes should be set as LOCKED and TAB STOP No to prevent user edit or don't have these textboxes at all.

You have designed other tables without autonumber key field and instead of saving a key you are saving text into GREY_OFFER. This does simplify queries as there is no need to join tables but uses more storage for the data. That may not be an issue if your db does not grow to 2GB.

Might want to rename OFFER_ENTERY to OFFER_ENTRY.

Also, field named SELVAGE might should be SALVAGE. Two words with very different meanings.

Using all upper case is not advised as it is harder to read. Also advise not to use spaces in naming convention as you have in field names.

In future, please post code between CODE tags. Use the # icon on post edit toolbar.

please explain it to me "You have designed other tables without autonumber key field and instead of saving a key you are saving text into GREY_OFFER. This does simplify queries as there is no need to join tables but uses more storage for the data. That may not be an issue if your db does not grow to 2GB.
"

[June7]

Sounds like you need to get better understanding of what primary/foreign key fields are. What exactly do you not understand about that statement? You are saving descriptive text values from lookup tables instead of a numeric key. Saving text uses more storage space. If you did use key then would have to join tables in query to retrieve related information. By saving the descriptive text table joining is not needed and query is simplified. Just have to decide which way is more beneficial.

Gaps in primary key autonumber sequence should be irrelevant. This field should not have any meaning to users and users shouldn't even have to be aware of its existence.

If you want to give meaning to this field and can't have gaps in sequence, then yes, autonumber is not really appropriate.

[deepaksharma]

First of all thank you for giving your valuable time.
I get your point.
By the way, my question was related to pick sub form data, but you have given me very valuable knowledge related to storage, I am very thankful to you.
Just now i want to know which field common to link all tables in query.
If I create a numerical column named offerid in all tables and link all those fields in the query then for example in one master file this series will run from 1 to 10 then in another master file it will run from 1 to 25 but in the main data file that is gray_offer Made in the name of this offerid will run till infinity.

[June7]

No, you don't create an offerid field in all tables. You create an autonumber field in each table and name them appropriately. ProcessID, PartyID, etc. Then instead of saving name "Kanchan" into Grey_Offer, you save ProcessID value. Your descriptive data is short and most of the Master tables have only one field so using text instead of number keys may be adequate. Another thing to consider is number fields perform faster in sorts and searches especially if they are indexed. However, if database remains small this performance impact may be insignificant.

Really should give ID field in Grey_Shade different name, such as Grey_Offer_FK.

[deepaksharma]

ok i will change and tell you...

[deepaksharma]

Hello,
Good afternoon Sir!!!!

I removed party process quality and transport columns from gray offer table as you said and instead created columns named party id process id quality id and transport id and linked them in gray table.
Now when I enter in form or direct entry in table then problem comes and plus sign is also not coming in gray offer file which shows relationship table. I have made this relation in many ways
Please download the file from this download link and check where the error is occurring.

https://drive.google.com/file/d/1UAz...w?usp=drivesdk

[mike60smart]

Hi
As June suggested there were numerous thing wrong with your relationships between tables.
I redefined field names and set relationships as they should be
I set the Form as a Main Form / Subform with the tables linked on "GreyOfferID"
See the relationships now in the Relationship Window.
I commented out all of the Code

Now when you enter data into the Main Form the related Foreign Key will be populated when you select a Grade.

[deepaksharma]

Hii mike,

Thank you so much for replying and working so hard on my program. Actually me and June wanted that when any master data is entered in the form, then only the code of that data from the autonumber field of the master data in the data file named gray offer. (which is in the auto number field of the master data file) should be saved so that the master data in the gray offer file will not be repeated and the file storage will be used less.
Second thing is that shade and meter are not fixed in gray shade sub form they will be entered manually and not by lookup wizard please help me to solve this problem.
Second also tell me how you reduced such a big file from MB to KB because every time I face problem in uploading the file on this site so I put the link of the file.

[mike60smart]

Hi
Not really understanding your Logic

If you enter details of a Grey_Offer (this should be given a proper name like tblGreyOffer) then the Primary Key is Unique

Then when you enter a GradeShade then the Foreign Key will populate automatically with the Autonumber from your Main Form.

As for entering a Shade manually Why!!!! When you have a table that lists all of the Shades together with the Meter measurement.

[June7]

For attaching database, see instructions at bottom of my post.

[deepaksharma]

Thank you both so much for replying

My program is such that first of all a master file of some fixed names will be created.
For example, there is a party transport quality and process table, after this, when the data is input into the gray offer file through the form, then the offer number, offer date, weight, instructions, etc. will be input manually, but the party, gst agent name, transport process quality will be looked up from the master file.

Now apart from these a sub form will be created in main form in which shade and meter will be input manually because color shade is more than 10000 and their number keeps on changing.And in which shed the party will get the order for how many meters written, it is also not fixed.
Now I want that whenever the data of the master file is inputted in the form, that data should not be input again and again in the gray offer file instead it should be the primary key input so that the file size is not much.