Form Not Saving Data In Correct Place

[Neilbees]

Hello everyone

I'm fairly new to Access and hope someone can help with this problem. It strikes me as something incredibly simple to solve but my brain seems unable to provide a solution today. I've had a good look through the forums but can't find the answer.

I have a form to enter details of a product. For reasons too dull to explain, the description of the product needs to be split over no more than 10 separate fields of no more than 72 characters each. So if a product has a 100 character description this needs to be split in the form (and table) over the fields Description_Line1 and Description_Line2. (As you might guess there is also Description_Line3 - 10).

What I want to do is put a button on the form so that users can "Add Product Description" and a new form will open with the 10 Description fields for them to type into. Fine so far, but my problem is that when the new form opens it opens it with a new record ID. So if users are adding a new product with an ID of 1 and then try to add a description the new form opens with an ID of 2 - so the description is not added to the correct product. Both the forms come from the same table.

Is the problem here the structure of the database? Should the Product Description be a separate table? Any ideas?

Apologies if this is a ridiculous question, my brain just isn't working today!

Thanks

 

[Bilbo_Baggins_Esq]

YOu could add a colum to the table for "selected", and if there are multiple users, add another for user ID.

Then base your second form off a query where "selected" is true, and (userID matches).

If you base the second form off the table, you're essentially adding a new record whereas all you really want to do is edit selected record.

 

[ajetrumpet]

What I want to do is put a button on the form so that users can "Add Product Description" and a new form will open with the 10 Description fields for them to type into.

when the new form opens it opens it with a new record ID

Both the forms come from the same table.

It will insert a new record if both of your forms are based on the same table, because these are two DIFFERENT objects controlled by a source.

For reasons too dull to explain, the description of the product needs to be split over no more than 10 separate fields of no more than 72 characters each

Construct the pop-up form in design view and put 10 text box controls in it, each with a control source of the appropriate field names, with field lengths of 72. It shouldn't be a problem to view descriptions in your report either, if you have to you can always concatenate the field names. You do not need a new table for this, just add the fields to your current table.

 

[Bilbo_Baggins_Esq]

For reasons too dull to explain, the description of the product needs to be split over no more than 10 separate fields of no more than 72 characters each. So if a product has a 100 character description this needs to be split in the form (and table) over the fields Description_Line1 and Description_Line2. (As you might guess there is also Description_Line3 - 10).

I'm sure you have some other constaint but this is asking for trouble if you ask me.

I'm not sure what products you're describing, but assuming you have some other database or method that requires a 72 character limit.

for lots of reasons, I would suggest letting the users have 1 cell with the max limit of 255 characters.
Then, when ever it comes time to interface with the other database or method, use code to break the field down as needed.

1. No pop up form required
2. No employee slow downs for editing or wasting time over typing 72 the character limit(s)
3. More tightly administering the size, content, and quality of your data.

Even if I had to have more that the 255 characters, I would still keep the number of fields the employee deals with to a minumum. 720 characters would be 2@255 and 1@210 would still be better that 10 at 72 each, and you can still hard code it down to 72 character limits before you process it to other databases or methods.

What would you say is the average length of your descriptions?
regardless of it's entertainment value, what is the reason they need to be 10 fields of 72 each?

 

[Neilbees]

Hi chaps

Thanks for the replies, excellent stuff.

Bilbo - it is as you imagine - the data needs to come out of the Access database to eventually be imported into a SAP database and the SAP people tell me that they need the product descriptions in (up to) 10 chunks of 72 characters.

I agree that keeping user input/complication to a minimum would be the best way. How would I go about splitting the data afterwards if I was to just have one box for user input?

 

[Bilbo_Baggins_Esq]

This SQL assumes you provide 1 field of 255 characters named User_Desc in a table named User_Input

UPDATE User_Input SET User_Input.Desc_1 = IIf(Len(User_Input!User_Desc)>72,Left(User_Input!User_Desc,72),User_Input!User_Desc), User_Input.Desc_2 = IIf(Len(User_Input!User_Desc)>72,Mid(User_Input!User_Desc,73,72),Null), User_Input.Desc_3 = IIf(Len(User_Input!User_Desc)>144,Mid(User_Input!User_Desc,145,72),Null), User_Input.Desc_4 = IIf(Len(User_Input!User_Desc)>216,Mid(User_Input!User_Desc,217,39),Null);

On the main database window, select the query button
click NEW>DESIGN VIEW>CLOSE

On the top menu bar click VIEW>SQL View

Paste the above SQL (replacing the existing "SELECT;")

On the top menu bar click VIEW>DESIGN VIEW

Save the query as Update_SAP_Descriptions

Then add this line to whatever code you have when the user completes the original process

DoCmd.OpenQuery "Update_SAP_Descriptions", acNormal, acEdit

 

[Neilbees]

Fantastic stuff! I'll give it a try and let you know how I get on.

 

[Neilbees]

Hi again Bilbo

I followed your instructions but have hit a snag. When the code tries to run I get a "Enter Parameter Value" box asking for "User_Input.Desc_1". If you click OK it asks for "User_Input.Desc_2" etc through to 4 and then produces a Runtime error 3113 - Cannot update Desc_1 - field not updateable.

I assume I've set it up wrongly somehow, any ideas?

 

[Bilbo_Baggins_Esq]

Hi,
You'll need to replace the table/column names I used in my SQL example, with the table/column names in your usage.

User_Input = table name that your form is based off
User_Desc = the column in that table in which the user types the unedited description
Desc_1 = the column in that table for SAP description part 1
Desc_2 = the column in that table for SAP description part 2
Desc_3 = the column in that table for SAP description part 3
Desc_4 = the column in that table for SAP description part 4