Form Field Behaviour Conundrum

Question

Hi all

I have been trying to add a field to a form in an inherited database and am not clear what is happening.

I copied an existing text box and altered the control source of the copy to a newly created field in a back-end db.

The form and the control are actually based on a query on the back-end tables that includes the new field.

When I try to make an entry in the new field nothing happens, although entries into the copied donor field are possible.  So I moved the copied field text box out of the way and created a whole new text box.  The control source of this I set to the same field as the copied text box.  I can make entries into the new text box and these are mirrored in the original copied text box.

So now I am struggling to find why the copied text box was locked for data entry as it is obviously linked to and displaying the correct data.  The original text box, used to copy from, has the locked property set to yes and yet data entry is still possible.

One feature on this form is the ability to lock certain fields based on a combo box selection.  It could be this that is causing the issues, but I don't understand the code.  Looking at the original donor text box, it has a before update property of:

Private Sub pp_code_BeforeUpdate(Cancel As Integer)
Call Checking(Me.pp_code)
End Sub

This may be what is blocking the data entry... can anyone advise where the me.pp_code will be found?   The field name is pp-code (hyphen not underscore) so I'm not sure what this refers to.

Any pointers gratefully received.

thanks

Phil

Answer 1

Hi Phil, The underscore sometimes refers to a space character. If you go to the Properties window and click on the Control drop down, do you see a "pp code?"

Answer 2

Thanks DBGuy

No, looking at the names of controls under the control source property, they are all of the form pp-code with no spaces.

Phil

Answer 3

> based on a query on the back-end tables
Are you able to add a row to that query, including the new field?

> This may be what is blocking the data entry
No; it might if it set Cancel to True, but it isn't. Still, comment out that line to see if it makes a difference. Someone could conceivably not follow the yellow brick road and implement their own Undo code inside of the Checking procedure.

---

-Tom. Microsoft Access MVP

Answer 4

Thanks DBGuy

No, looking at the names of controls under the control source property, they are all of the form pp-code with no spaces.

Phil

Sorry, I didn't mean the "Control" dropdown but the "Selection type" dropdown. See below...

What do you see there?

Answer 5

I am seeing the same label in the drop down box as in the control as shown below



Answer 6

Hi Tom

<Are you able to add a row to that query, including the new field?>

Do you mean manually in data sheet view?

Phil

Answer 7

Okay, thanks. If you need help locating pp_code, you might consider posting a copy of your db without any data. You can also email it to me.

Answer 8

Yes.

---

-Tom. Microsoft Access MVP

Answer 9

Thanks for the offer DBGuy

This is a split DB with personal data etc., so for a newb like me deleting data without stopping the application operating would probably be an undertaking.

As I have worked round this for now by introducing a whole new text box I'm inclined to take the pragmatic approach and battle on regardless.  Hopefully any major gaffs will fall out in testing.

One other minor thing I have been struggling with though is the linking of multiple tables from backend to front.  Sometimes I just click the "Always prompt for new location" button, select tick boxes next to all tables and navigating to the backend once links all tables in one operation.  Other times the connection box pops up for every single backend table even though all bar one is in the same db file, which is a serious annoyance.

Do you know where I am going wrong here?

Thanks

Phil

Answer 10

I tried that Tom but there are relationships which are not being fulfilled using this direct data input method.  So I am getting alert messages and computer-says-no situations which my knowledge level has me tripping over.

Answer 11

Data entry through the form is no different. It probably doesn't work either.

I asked the question because it's unusual that a form is bound to a query over multiple tables. It's often reflective of imperfect understanding of how to build a form. That may be the case here too, but we can't know for sure without more information, to include at least:
1. objective of the form

2. relationship diagram of the tables in question

3. sql statement of that query

4. recordsource of the form

---

-Tom. Microsoft Access MVP

Answer 12

Thanks Tom

The objective of the form is to record details of contracts won by the company.  These include enquiry numbers, contract numbers, contacts specific to that contract etc.  The query is based on only two tables, with one table providing just three fields containing customer address data.

The relationship between the tables is shown below:

The sql statement for the query is:

SELECT tbAccount.[ac-id], tbAccount.[ac-title], tbAccount.[ac-customer-ref], tbAccount.[ac-d-date], tbAccount.[ac-e-cost], tbAccount.[ac-a-cost], tbAccount.[ct-code], tbAccount.[pp-code], tbAccount.[ac-amount], tbAccount.[ac-pound], tbAccount.[pp-CustomerContact], tbAccount.[pp-Employee], tbAccount.[ct-CustomerContact], tbAccount.[ct-Employee], tbAccount.[ct-date], tbCustomerAddress.[ca-code], tbCustomerAddress.[ca-name], tbAccount.[pp-date], tbAccount.[ac-date], tbAccount.[ca-id], tbAccount.[ac-currency], tbAccount.[ac-status-id], tbAccount.[ac-revision], tbAccount.[ac-margin], tbAccount.[ac-additionalmargin], tbCustomerAddress.[ca-country], tbAccount.[ac-rev-date], tbAccount.[op-code]
FROM tbCustomerAddress INNER JOIN tbAccount ON tbCustomerAddress.[ca-id] = tbAccount.[ca-id]
ORDER BY tbAccount.[ac-id];

The record source of the form is the query above.

Thanks

Phil

Answer 13

Hi Phil,

>> As I have worked round this for now

>> One other minor thing

For original issue, it seems you have found a workaround, and you have a new issue. Am I right?

If so, I would suggest you mark your reply as answer to close this thread.

For this new issue which is related with “the linking of multiple tables from backend to front.”, I would suggest you post a new thread, and share us more information in the new thread like detail steps to reproduce this issue. Then we could focus on this new issue.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].