Help with Forms

[natewsmith]

When you look at the attached photo look at the bottom of the picture. Under the capitalization and Repositioning table record we have to add in trucking and other costs that apply to the Repo and cap cost fees up above. The problem is when we have more than 1 unit with the same number but with different prefixes, when we add anything in it it automatically applies to the other unit with same number but different prefix, which is not what we want. When we try to delete off of the other unit it deletes it off of both. I didn't build this data base but I need to figure out this bug.

Any help would be great!

Thanks,

Nate

 

[dkinley]

The problem is when we have more than 1 unit with the same number but with different prefixes, when we add anything in it it automatically applies to the other unit with same number but different prefix ...

As a quick stab, and not assuming any db corruption has taken place, I would look at the keys that are linking the units to the Capitalization and Repositioning tables and how those are set up, especially since these were added after the initial design of the database.

I would almost say that the links are a combination of a couple of fields including the numbers so that if your additional subform was linked by just a number, then it would show changes to the other units as well.

Again, just guesswork. Review the other tables' linking process and make this additional one exactly thus. Or, create new primary and foreign keys based on an autonumbering scheme and let Access do all of that work.

-dK

 

[natewsmith]

I don't seem to be able to find where they are linked to. I also try the design view and I don't see at all where this is linked. I'm viewing this database in Access 2007. The database is 2000 converted to 2003. It's on a server and I don't see it on there either. What am I missing?

 

[dkinley]

I would probably look at the forms in design view and the properties of the other subforms. There you will see a master/child link property which will tell you what fields they are linked on.

Then, I'd probably check to see when a new master record is added, how the primary key is created for it and the foreign keys for the other sub-tables (assuming not an autonumber and a combination of keys perhaps) and compare and contrast that with the new table.

I'd also check the relationships of the old versus the new to try and spot something there.

Mostly alot of comparing to see if I noticed similarities or differences that might provide a clue. There is probably some better advice out there that has ran into this before, but this is just me hacking at a problem trying to pare down what it could be.

-dK

 

[natewsmith]

I know exactly where you were trying to get me to look. I've been staring at that all day! HAHA.. The problem is I don't see a master/child anywhere on that property.

 

[dkinley]

Just as a check. Under the properties of the subform select the Data tab. In that list are the link fields.

Now, it could be difficult getting the properties you want because it is sometimes tricky. What I do is get the properties screen up and click off somewhere on the form to make sure nothing is selected. Then I do the mouse button drag trick on the form to make the selector box expand until it touches the subform ... the subform does not have the little black box in the top left hand corner - this lets you know you have it right.

When you have the little black box in the top left hand corner that accesses the form properties (of the subform). When you have what I am describing selected, you have the actual subform properties selected.

Good luck!
-dK

 

[natewsmith]

Ok Thanks that worked. The Source object is SubRepair History and the Lined Master Fields and Child Fields are the same: Prefix;UnitN.

When I click on the properties of the subform on the Lease field above it uses Prefix;UnitN;CheckN;SequenceN

 

[natewsmith]

When I add the other fields into the properties it now does what I want it to do. It only adds it to one container. The problem is now the information I had before I changed it dissapears. But the ammount in the field for what money is due continues to stay. Also what ever I did now while working on the database, it only seems to happen on my pc but when I hit next record this now appears:

The expression you entered has a field, control, or property name that Microsoft Office Access can't find.

What does this mean?

 

[dkinley]

The (sub record) information you had before would disappear. These are now 'orphaned' records. I bet if you looked in the table they are still there. The reason you don't see them with the proper record is because you have adjusted the linking between the parent table and child table. You changed the rules and the old record foreign keys do not meet them.

I am not sure why the dollar amount continues to stray. Curious.

The error you are getting is because Access wants to do something but you have told it to automatically do something based on a control that it can't find. I would guess that it is part of the primary/foreign key creation and a control name misspelled perhaps.

Again, I would stress the usage of using autonumbering fields for primary keys and let Access manage the primary and foreign keys. I understand that you did not create this database, but yet now have to modify it.

The fields you listed don't have any meaning for me because they are individual to your database. I can take a look at your db if you would post it here but can't promise 100% results if the original design had fundamental flaws.

I would basically follow the same process here attempting to understand what the previous designer did and try to copy that process to the new subform (and table) to band aid the database for you.

-dK

 

[natewsmith]

ok Thanks,

I'll give all that a try.

 

[natewsmith]

So I have fixed the error problem I was getting with the records.

If I choose to do the auto numbering how will that affect everything? Will I still be able to find records like we normally do? Will things still work like the do now?

You are correct in that if I go back to the way it was the records show everything below. Is there a way to get the orphaned information on those records witht the corrected parent/child relationship?

 

[dkinley]

You can do the repair by manually entering the foreign key by editing the table directly. First, find the parent record it is supposed to be attached too and either copy or cobble together what the foreign key is and put that into the appropriate field in the child record.

Using an autonumber field simply puts all of the headache on Access and let it do what it was built for. Then for the human interface you can use whatever you want. An example is a social security number. Instead of letting that be the primary key in a table and a foreign key in another table, just let that be a regular ole field and use an autonumber field. Fields like this (because someone can change their SSN) and hybrid keys (where two fields make up the key) I steer clear of for keys.

Now, the question - can you implement this into your existing? Yes, you can. Will be tedious? Probably. You would need to create the primary and foreign key fields in all of your tables and then update those (through update queries or manually) so the linking is correct. Then re-establish the relationships (if any) and then finally tear out all of the old (or leave it there a month or so just to make sure and leave you a roll-back path).

-dK

 

[natewsmith]

DK-

Well it's already set up for automated numbers.

 

[dkinley]

Hmmm. If a seperate form is used to add child records, I would check for where the ID number is stored on a form for a child-table and see how it is pulling the primary key for the parent-table.

-dK

 

[natewsmith]

If I make the change and all those records become orphaned, is there a way to be able to view those easily? So I could then correct?

 

[dkinley]

No. Well, you could make another form. Easiest way is to just open the table and make those changes manually from there. If you have a slew of records and can filter accordingly an update query is the easiest way to do it.

-dK