Form field is editable only after a sort

[Wayne Cramer]

I have a form with a comment field. The comments are a seperate table liked with a one to one relationship to a main table. As comments are added it creates a new record to the comment table. The form is controlled by ba query which has a specific field order ie customer, purchase order, item number. The problem is that the comment field cannot be edited when the form is opened in this order, but once any sort takes place using the up/down arrow key on any field then the comment field can be edited and creates a new record. The problem is that this removes the needed sorting.
The comment box in the form is set to enabled = yes, locked = no, and allow auto correct = yes. Any help would be greatly appreciated.

 

[MarkK]

Why not put the comments fields into the main table? Putting a single field in a different table and then linking it back one-to-one seems like a make work project.

 

[Bilbo_Baggins_Esq]

What are the FORM property settings for:
Allow Edits:
Allow Deletions:
Allow Additions:
Data Entry:

Do you have any code running OnLoad, or OnGotFocus or other events?

And is there a particular reason you have a seperate 1 to 1 related table?

 

[Wayne Cramer]

The comments are a seperate table because the data in the main table changes each week and the comments become an archive of activity on tasks.

 

[Rabbie]

The comments are a seperate table because the data in the main table changes each week and the comments become an archive of activity on tasks.

I am a little puzzled here. Can the Main table entry have more than 1 comment associated with it. if so then its a 1 to many relationship.

 

[GAB]

Hi Wayne,

I did the same thing and created a query using the two tables making one the dominant table . In my case I have two tables one called customer and the other called customernotes. Both have a field accountnumber and I joined accountnumber to accountnumber making customer the dominant table and having only records matching from customernotes appear.

(include all records from "customer" and only those records from "customernotes" where the joined fields are equal)

From the customernotes table I used a memo field on the form linked to that table and the rest of the fields on the form are linked to the customer table.

This has worked like a charm and keeps the notes made for each account with that account from record to record. Hope this makes sense.

Gary

 

[Wayne Cramer]

Thanks everyone. Here's some additional information. The main form is created from a SAP download each week. The previous week mainform has all recoreds deleted by a delete query. So, the comments cannot be part of the main form. No, there is only one comment per main form record so it truly is one to one. It is actually a running commentary on the order status. A single record comes on and off the table week to week for a variety of reasons (query critetria) such as days from order, days till shipping, days overdue etc.
Here are the property settings you asked for:
Allow Edits = Yes
Allow Deletions = No
Allow Additions = No
Data entry = No
There is an OrderBy event On Open to sort the form by Vendor, PO#, PO Item. No other events.

 

[MarkK]

So you save the comments after the related record is deleted?
Nobody's doubting the relationship is one-to-one, but there's also a one-to-one relationship between the first name and last name of a contact. This doesn't argue that the two names should be in separate tables.

 

[Bilbo_Baggins_Esq]

I have a concern to make sure we are all on the same page with regard to what you're actually saying and asking, so I have added here, you first and most recent posts, with edits highlighted in bold. Please confirm if this is really what you intend to say.

I have a form with a comment field. The comments are a seperate table linked with a one to one relationship to a main table. As comments are added it creates a new record to the comment table. The form is controlled by a query which has a specific field order ie customer, purchase order, item number. The problem is that the comment field cannot be edited when the form is opened in this order, but once any sort takes place using the up/down arrow key on any field then the comment field can be edited and creates a new record. The problem is that this removes the needed sorting.

Thanks everyone. Here's some additional information. The main form is created from a SAP download each week. The previous week mainTABLE has all recoreds deleted by a delete query. So, the comments cannot be part of the main TABLE. No, there is only one comment per main TABLE record so it truly is one to one. It is actually a running commentary on the order status. A single record comes on and off the table week to week for a variety of reasons (query critetria) such as days from order, days till shipping, days overdue etc.
Here are the property settings you asked for:
Allow Edits = Yes
Allow Deletions = No
Allow Additions = No
Data entry = No
There is an OrderBy event On Open to sort the form by Vendor, PO#, PO Item. No other events.

If these edits are correct, then:

Is it true, that once SAP populates the main table, there could be some records in the main table that do not yet have corresponding comments in the comments table? I ask this because you have mentioned more than one time that you intend to be able to ADD A COMMENT RECORD to the comment table.
If so, your form source query must have a LEFT JOIN to allow all the main table records to appear in the form. If this is true, then you should not initially be able to actually ADD a record to the comments table (for a main table record that does not yet have a comment), but you should be able to EDIT the existing comments. There is a fine distinction here because with a LEFT JOIN in the source query, it may LOOK like there is a empty field, but in fact there actually is no record. Additionally, with the Allow Additions set to NO, you should not be able to add to the Comments table anyway.

If this is off base, then please elaborate further.