Adding field to existing subform prevents ability to move to next record

[Tammysdatabase]

I have a database that I've developed for Purchase Orders and project tracking that has been working great for the last year and a half. It has been asked of me to add in a hidden field in the line items for the purchase orders whereby a "code" is placed based on the input of the Job Number field. So, if there is a random job no that is >1 the code becomes "1". If the specific job no of "10001" is used, the code becomes "3" and if the field is left blank, the code is "2". With this code I will make a private form for one of the managers so he can manipulate the data in the details table where the line items are held and see what the cost is for project purchases, consumables (10001) and sundry or Nil items.

I have the "code" control working just fine, however, I have found that by adding this new "Code" control into the subform I now cannot tab or arrow out of the current record unless I press Escape. This is not going to work well with the users here and I have tried everything in my power to fix the problem. I have tried adding code to move to the next record, I have checked the tab order for both the main and subform.....I'm at a loss. I keep old copies of my FE to test new additions to the database and have tested this. It definitely happens when I add in the new field. The table in the BE has the new field set as general number with field size "Long Integer", Indexed-Yes(NoDups).

The subform has the control formatted as a general number as well. Control is enabled. Subform is set to allow addition, deletions, edits and filters. Recordset: Dynaset.
Record source is a query with one outer join, but this has always been ok...nothing here changed.

The only thing I have really changed here is to add a new field to the table and control to the subform and to add in vbcode for the subform to manage the codes as data is input. I don't understand why this makes the record hang until escape is pressed. What have I done?

 

[pbaldy]

I would expect an error, but the NoDups doesn't sound appropriate. You'd only be able to have 1 record with each code.

 

[Pat Hartman]

This seems to be totally convoluted. If the Job is in the main form's PurchaseOrder record, why is the hidden field on the subform and when is it populated? Are you populating it when the subform record is added?

It has been asked of me to add in a hidden field....

I'm pretty sure no one actually asked you to do this. You decided that this was the correct way to solve the actual problem. Maybe if you shared the actual request, in English, not in code, I'm pretty sure we can come up with some rational solution.

 

[The_Doc_Man]

I'm with Paul on this one. Odds are that your problem is the NoDups on something that will only have three possible values. That means you can only have 3 records.

 

[Tammysdatabase]

My bad....that should have read "Indexed: Yes (Dups Ok). So my issue still exists. Any other thoughts?

 

[Tammysdatabase]

This seems to be totally convoluted. If the Job is in the main form's PurchaseOrder record, why is the hidden field on the subform and when is it populated? Are you populating it when the subform record is added?


I'm pretty sure no one actually asked you to do this. You decided that this was the correct way to solve the actual problem. Maybe if you shared the actual request, in English, not in code, I'm pretty sure we can come up with some rational solution.

The hidden field is on the subform because this is where the line items come from for the purchase order. We assign project or "job" numbers to these line items to tag the material for a project so having it on the main form would be ineffective. Sometimes we will order material from a vendor with many line items on it that only have a couple that are tagged to a job. The other material would be either consumables or sundry items. I would like the code to be populated when the record is added or when a job number is added (or left blank).

I was tasked with adding this to the database and yes, you are correct, this is the way I decided to tackle the problem. Didn't realize my wording of the situation would be an issue.

 

[Pat Hartman]

I can't tell what is wrong with the form but it might have something to do with the tab order. If you post a db with the problem, we can look at it.

I was tasked with adding this to the database and yes, you are correct, this is the way I decided to tackle the problem. Didn't realize my wording of the situation would be an issue.

The wording isn't a problem. It is the fact that you are doing it at all. But since you still haven't told us what problem you are trying to solve, we can't really offer a definitive alternative.

What is wrong with just using the job number to select these rows for other processing? The query you build to select these items for the special purpose just uses the job so I'm not sure what purpose the "tag" serves. "flags" based on logic applied to the fields in the record are redundant. If you can define the logic to set the "tag", you can use that logic in the select query for the special purpose.

 

[Tammysdatabase]

The wording isn't a problem. It is the fact that you are doing it at all. But since you still haven't told us what problem you are trying to solve, we can't really offer a definitive alternative.

I did actually....

"I have found that by adding this new "Code" control into the subform I now cannot tab or arrow out of the current record unless I press Escape."

Just want to know why I can't tab or move out of a line (record) after adding in one simple field on the table and one control on the subform. The database works fine otherwise.

I cannot post a copy of the db as it houses all of our project and PO information, but I'm happy to answer questions about set up or copy code for analysis.

I do appreciate any help.

 

[Pat Hartman]

Just want to know why I can't tab or move out of a line (record) after adding in one simple field on the table and one control on the subform. The database works fine otherwise.

There is no reason why this should not work. Therefore, there is something wrong with the implementation. We are not prescient. Extract the form if possible with just the tables necessary. Obfuscate the data. At a minimum, post the code module behind the form.

Don't bother with normalization. Rules are for peasants.
PS, I never said to put the flag on the main form, I implied that it belonged with the job.

 

[Tammysdatabase]

I've copied the necessary forms, tables and queries into a blank database and removed sensitive data to show you what's happening. If you open the Purchase Order Details form and click New PO, you can tab through the fields to see how it works. When you tab into the subform and put a line Item number in it populates the corresponding PO number into the line and you can add in your info. The code (last field) should populate based on the Job number and it does on my full database and then I get the "hang" where I have to press escape to move out of the line, but I see in this little version the code does not populate until I press escape.

Hoping you can take a look and give me some insight. Thank you.

 

[Tammysdatabase]

Figured this out. It's working now. I'm not sure how, but at the table I had lost my Autonumbering on the ID field??? I must have gone and looked at the table several times without catching it. I've set it back (on my og database) and changed the placement of the vbCode for the Code control and it's all good and working now. Sometimes you just need to walk away from the problem for a while and the solution comes to you.

 

[The_Doc_Man]

Sometimes you just need to walk away from the problem for a while and the solution comes to you.

A very true statement. Congrats on finding the fix.

 

[Pat Hartman]

You have other problems. Some are merely poor practice. Others are outright wrong.

1. Validating data in the form's AfterUpdate event is like closing the barn door after the horses have escaped. If you want to validate data, you MUST do it BEFORE the bad data is saved. Putting the validation in the form's BeforeUpdate event allows you to stop Access from saving the bad data.
2. You are also using the AfterUpdate event of the form to modify the form. BAD, BAD, BAD. In earlier versions of Access this would have put the application into an infinite loop. WHY?

When a form is dirty and you attempt to move to another record or close the form, Access runs the form's BeforeUpdate event. Then it saves the record and then AFTER the record is saved, it runs the AfterUpdate event but you have code there that dirties the record. SOOOOOOOOOOO, Access runs the form's BeforeUpdate event. Saves the record again. Then runs the AfterUpdate event where your code dirties the form again. SOOOOOO, Access runs the form's BeforeUpdate event, Saves the record again. Then runs the AfterUpdate event where your code dirties the form yet again, and here we go back into the never ending loop. Lucky for you, Access has gotten smarter over the years and when the stack gets too deep, Access gracefully exits the infinite loop instead of going into a flickering frendzy causing you to reboot to exit.

Take the code in the AfterUpdate event that dirties the code and move it to the form's BeforeUpdate event. That way, your code changes the record BEFORE it gets saved and does not cause a loop.
3. You are not using Option Explicit in ALL your code modules. Turning on the feature AFTER procedures are created does not cause the option to be inserted into existing code modules. It is only added to new modules so you need to fix ALL modules that are missing the Option Explicit.
4. You have lots of controls with default names. Sloppy. Who knows what Text63 and Text71 mean????
5. You have multiple fields duplicated in the table. Item and ItemID, Vendor and VendorID are two in the subform's table. ONLY the "id" fields need to be saved. You are using combos on the WRONG fields. Omit Vendor and Item and just use the combo on VendorID and ItemID. You will see the text value but store the ID value which is the way relationships are supposed to work.
6. Naming the autonumber fields all "ID" just causes confusion and makes it impossible to map PK - FK without a diagram.
7. embedding spaces and special characters in object names is poor practice.
8. Your relationships are incorrect. For example The field [Purchase Order ID] in PODetails1 should be the FK to "ID" in [Purchase Orders]. You have the relationship pointing to a data field but relationships ALWAYS point to the FK!!!!!!

 

[Tammysdatabase]

Ok. Thanks for all the feedback Pat. I know it’s not great…learning as I go. This is my first crack at it so your info is appreciated.

 

[Pat Hartman]

You're welcome. Some of what you are doing is dangerous. Please take some time to understand the points and address them as soon as possible.