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.

 

[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.

 

[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.

 

[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.

 

[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.