Subform auto populates with same data as last record I was on when I go to new record

[achammar]

Hi all! I've learned an awful lot since I started on this site but I'm still learning.

I have a form with a subform... fairly simple. Just 1 table for each form with a 1 to many relationship between the 2. The 1 side table has a field that is indexed and duplicates are not allowed (JobNumber) that I enter my own number using the main form, then I fill in the subform data. My problem is when I click on new record on my main form, it goes to a new record and the form and subform are blank, all just like it should. As soon as I start typing in the text box to enter a new JobNumber in the main table, the subform auto populates with all the records of the last record I was on. It doesn't save it.. if I backup 1 record then forward to the next record (the new record) the subform data is gone. It's very deceiving. It's a glitch of some kind I think but does anyone have an answer to this problem?

Thank you very much!
Albert

[Missinglinq]

It sounds as if the Link Master Fields - Link Child Fields aren't set properly.

Presumably the JobNumber as the Primary Key from the Main Form for the former and a corresponding Foreign Key for the latter...is that how you have it set up.

Linq ;0)>

[achammar]

Sorry, I guess I'm not that far along yet. I don't understand the link master and child fields property. The main table field is not a primary key, but it is set to indexed with no duplicates. It is the 1 side of the relationship. The related subform table is the many side of the relationship. I just looked at the properties of the tables and both the master and child link fields are blank on both tables. How should I set them? And thank you soo much!

[Missinglinq]

JobNumber as the Master Links Field Property...and you have to have a corresponding Field in the Table that the Subform is based on...the name of that Field goes in the Child Links Field Property.

When you enter a new JobNumber in the Main Form...Access will automatically enter that value in all Subform Records for that Main Form Record. Then, as you move from one Main Form Record to another, the Subform Record(s) will only be those related to that Main Form Record.

Linq ;0)>

[achammar]

Thank you very much for your help. Unfortunately it must be a glitch in my version of Access or something, although it's not that old. I've tried everything and the problem is still there. I've set the Master and child link properties, I've set the JobNumber field to be the primary key and changed the relationship to match, then set the master and child fields again.. the way I'm pretty sure they should be, and every other combination of ways there could be and it's still the same problem. Nothing changes at all. I think it is just a glitch in Access or something. It totally sucks.. Thank you again though for trying!

[ssanfu]

You could post your dB for analysis...

[achammar]

How do I do that? I tried the "Go Advanced" button but it only allowed my 500 kb to upload.. that's pretty dumb.. that's like dinosaur size files back in the floppy disk days.. lol I could barely upload a small text file at that size.. but I deleted every record except 2 in my database, all the queries, modules and unneeded forms, and it was still 600 kb so there's pretty much no way to upload a database unless there's a different way to do it. I sure would like to though so somebody else can see this weird problem.

[Bob Fitz]

How do I do that? I tried the "Go Advanced" button but it only allowed my 500 kb to upload.. that's pretty dumb.. that's like dinosaur size files back in the floppy disk days.. lol I could barely upload a small text file at that size.. but I deleted every record except 2 in my database, all the queries, modules and unneeded forms, and it was still 600 kb so there's pretty much no way to upload a database unless there's a different way to do it. I sure would like to though so somebody else can see this weird problem.

Compact and Repair the DB then zip it.

[achammar]

JobPackets.zip

I've attached a zip file of my database. Thank you very much for the suggestion by the way! tblJobNumber is the main table for frmJobnumber, and tblJobData is the secondary table for the subform frmJobData. tblJobNumber is the one side, and tblJobData is the many side of the relationship

There's only a couple records in it right now, but if you scroll to one of them, then click on New Record, as soon as you start typing in the Job number box, the subform fills with the data you was last looking at. Makes no sense to me. I hope one of you experience people can figure out what is wrong. I will be ecstatic and a very happy man! Thank you for any help you can give me!

Albert

[ssanfu]

Apologies, but I made a lot of modifications to your dB.

Some of the field names had spaces in the name and one field had a special character.
All tables had "ID" as the PK field - I renamed fields.
I changed a few field data types (mostly from Number to Text)
You have calculated fields in tables. This is considered bad. You should do the calculations in a query. (FWIW, All of my forms have a query as the record source, not a table.)
See http://www.allenbrowne.com/casu-14.html

The link between "tblJobNumbers" and "tblJobData" was wrong. I also modified the links for Vendors and Customers. Links should be between a PK field and a FK field.


Did some prettying up things and added nav buttons.......



How is this???

[achammar]

Thank you for all your effort and advice! I didn't know that putting calculated fields in a table was bad. And the navigation buttons are an excellent addition! I see you found the line of code that says: Cancel = True I copied that code from an Excel App I wrote which I am extremely good at..been doing that for 20 years... but Access not much at all. I'm still learning that as you can tell, but that Cancel = True wasn't supposed to be there.. I didn't catch that lol And I don't know yet how to make all those relationships you did either, so I will be studying that also along with what you told me about Pk's and Fk's so I can learn the proper way to do that. I ran into a couple errors with some code but I can figure that out. Now for the But... the original problem I was having is still happening. Did you try going to a new record on the main form and entering a number in the Job number text box? As soon as I hit a key, it still populates the sub form with the data from the last record I was viewing, but it's misleading. It's not really data for the new record, it just looks like it. It disappears as soon as I click on the sub form to start entering data. That was my original problem to begin with. Does that happen to you too? I thought it was glitch with my computer or version of Access or something, but the same thing happens with Office 365 at work. I don't get it.

[ssanfu]

You broke it!!!

Actually it was my bad:
So I looked at the form again and changed the code for the JobNumber text box afterupdate event a little.
I also didn't see the validation in the field for the jobnumber was set to ">0". Since I had changed the field type to Text, I deleted the validation.
And I had forgotten to add a text box control for the jobnumberID_PK field. Since an autonumber field should not be displayed, I also set the visible property to no. (if a control is hidden, I set the background color to pale yellow.)


Since I am a control freak (pun intended), I never set a (sub) form to datasheet view. I always "roll my own" datasheet form. More control on the visual dieplay.


See if you can break this!!

[achammar]

Wow.. works perfectly! Thank you so much! I will be learning from this! It's beautiful, I love it! So you solved my problem but if you want to answer one more question, I'd really like to know how you get the frmJobNumbers to open on start without an AutoExec module? I didn't know you could do that lol I also learned that if I set my subform's default view to anything other than datasheet, my original problem goes away.. that's so weird.. although I do realize that is not the correct fix lol

[ssanfu]

I'd really like to know how you get the frmJobNumbers to open on start without an AutoExec module?

FILE/Options/Current Database then "Display Form"



Good luck with your project.....