Copy fields to another table.

[projectpupil7]

Hi All, first question for me so here go's.
What i need to do is copy some fields from one table to another after updating via a pop up form. My coding skills are not excellent but would love to get this right.



Explanation:

Form [frmWorkItem subform] and [frmReactiveWorkPoList] are located on a tab control (Tab110, pages 0 and page1) and are linked by [ClientID] to the main form [frmReactiveTracker]. The functionig of this set up works fine.

The process of entering data starts with [frmWorkItem subform], here i use a popup form to enter a new record and when the popup closes the new record is seen in [frmWorkItem subform]. Unfortunately there are five duplicate fields in table frmReactiveWorkPoList which can't be changed now (the result of bad normalisation and now too much work to change it).

The duplicated fields in the newly entered record in [frmWorkItem subform] need to be copied to the respective fields in frmReactiveWorkPoList either when the popup closes or some other more efficient method.

I would very much like to work alongside someone to achieve what i need and to help i have attached the sample stripped down database. To start open frmReactiveTracker and pick the third name from the drop down list.

Hope you can help and thanks in advance.

[ranman256]

Are you talking about adding new FIELDS to an existing table?
or
adding DATA to fields in another table?

[June7]

Glad you understand that this data duplication is bad design. What are the 5 duplicated fields - ClientID, Address, AssetNumber, ProfessCode, DescriptionOfWorks?

Why do you say it is too much work to change? Is the posted db the full structure - 3 tables, 3 queries, 8 forms? Seems like a lot of work keeping them.


The Priority combobox has error - field doesn't exist in the RecordSource.

[projectpupil7]

Glad you understand that this data duplication is bad design. What are the 5 duplicated fields - ClientID, Address, AssetNumber, ProfessCode, DescriptionOfWorks?

Why do you say it is too much work to change? Is the posted db the full structure - 3 tables, 3 queries, 8 forms? Seems like a lot of work keeping them.


The Priority combobox has error - field doesn't exist in the RecordSource.

Thanks for your reply June7.

Yes, the duplicated fields are:
ClientID, Address, AssetNumber, ProfessCode, DescriptionOfWorks?

It's too much work to change at this moment in time due to workload but IT WILL be changed. The db is part of a suite of programs to be used by a maintenance team.

I would be interested in your comment "Seems like a lot of work keeping them" as you seem to imply that i don't need them all. By the way your perspective as a proffesional will be different from mine as a lay person. If it makes db better then i would like to know though - thanks.

With regard to the Priority combobox - short answer is ??????. I have opened my copy of the db here at work and don't get the error. I will have a look at the zipped copy and let you know what i found. Note, Priority doesn't need to be copied over to a new table, though.

Thanks again for your support, greatly apreciated.

[projectpupil7]

Are you talking about adding new FIELDS to an existing table?
or
adding DATA to fields in another table?

ranman256 hi, it's only the data that i need to copy over, hth.

Thanks. Are you really Arnie ????!!!!

[June7]

I meant keeping the duplicate fields and figuring out how to populate them seems like a lot of work.

Please understand I am not a professional programmer, not in the sense of contracting my services to build dbs for others. I learned on the job to meet the needs of my employer.

So when the record is initiated in frmReactiveWorkPoList you want to pick up the 4 duplicate values (ClientID should automatically populate). Grabbing the data should not be difficult, might use module declared variables. The real trick is figuring out what event to put code in. Should it be as soon as the subform gets focus? Should it be when data is entered into OrderNo field?

Not really understanding data relationships. Should WorkPO be related to WorkOrder instead of directly to Client?

The red backcolor of textboxes makes it really hard to read text, almost painful.

[projectpupil7]

Hi June7, no problem. The process of entering data is started in frmWorkItem subform. After the user enters data via the popup they would close the popup form and it is at this moment (Form OnClose), (perhaps), that the record fields should be copied over.
Yes, WorkPO is related to WorkOrder and like you i think that RED is a right PINA. IT HAS NOW BEEN CHANGED.


The error with the Priority combobox was due to a missing field in the form's query. Must have disappeared when sending to the list.

[June7]

So in Close button code on the popup form, one way to create WorkPO record is with INSERT SQL.

CurrentDb.Execute "INSERT INTO tblReactiveWorkPoList(ClientID, Address, AssetNumber, ProfessCode, DescriptionOfWorks) VALUES(" & Me!ClientID & ", '" & Me!Address & "', '" & Me!AssetNumber & "', '" & Me!ProfessCode & "', '" & Me!Description & "')"

However, not seeing the ProfessCode on the popup.

Then will need to requery the WorkPO subform so this new record is available.
Forms!frmReactiveTracker.frmReactiveWorkPoList.Req uery

[projectpupil7]

This is looking good June7. However, the form doesn't close when i click the close form button.

[projectpupil7]

Got it now June7 (any comments)

Private Sub cmdCloseEditWorkItemForm_Click()
CurrentDb.Execute "INSERT INTO tblReactiveWorkPoList(ClientID, Address, AssetNumber, ProfessCode, DescriptionOfWorks) VALUES(" & Me!ClientID & ", '" & Me!ADDRESS & "', '" & Me!AssetNumber & "', '" & Me!ProfessCode & "', '" & Me!Description & "')"
Forms!frmReactiveTracker.frmReactiveWorkPoList.Req uery
DoCmd.Close acForm, "frmEditWorkItem"
End Sub

[June7]

Works as desired? Solved?

[projectpupil7]

Works as desired so problem now solved. Grateful thanks June7. Hope you have a good weekend.