Add an existing record from a mainform to a subform when you click on the save button

[grampiano]

Hey there good day can anyone help as i am struggling to finish the end result:
I got a unbound mainform
got a bound subform
got two tables Masterplant and PlantTransaction
When I edit a record it shows on the mainform and when i save the record it must duplicate an existing record in the subform which works but the trick is that the Opening Hours in the new record must become my the Closing Hours of the previous record, everything works its just the Opening Hours does not show from the Closing hours previous record and the TransactionID is a number field that must auto increment with a different TransactionID number any help will be appreciated thanks in advance!!!
Code below:

Private Sub cmdSave_Click()
Dim strSQL As String
Dim rst As DAO.Recordset
If IsNull(txtOpeningHRS) Then
Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
If Me.NewRecord Then
rst.MoveLast
Else
rst.Bookmark = Me.Bookmark
rst.MovePrevious
End If
txtOpeningHRS = rst!CloseHrs
End If
End If
If IsNull(Me.TransactionID) Or Me.TransactionID = 0 Then
Me.TransactionID = Nz(DMax("TransactionID", "PlantTransaction") + 1, 1234)
End If
strSQL = "INSERT INTO PlantTransaction(TransactionID,[Plant Number],Opening_Hours,[TransactionDate],[FuelConsumption],[Hour Meter Replaced],Comments,[Hours Worked]) " & _
strSQL & "VALUES(" & Me.txtTranID & ",'" & Me.txtPlantNo & "','" & Me.txtOpeningHRS & "',#" & Me.txtTransDate & "#,'" & Me.txtFuelConsFuelHr & "','" & Me.txtHrMtrRep & "','" & Me.txtComments & "','" & Me.txtHrsWorked & "');"

CurrentDb.Execute strSQL
Me.PlantTransactionQuery.Form.Requery
cmdNew_Click


End Sub

[rpeare]

can you provide a sample database for examination please.

[grampiano]

I did upload a sample database rpeare really appreciate your help

pbaldy: attachment deleted per OP request.

[June7]

grampiano, you should be able to edit your posts to add/remove attachments. If you want to provide a scrubbed db for analysis, make copy and remove confidential info.

If the main form is unbound, how does it show data?

Do you have 2 fields - Closing and Opening? Might not be necessary to save both. It is possible for a query to pull data from following or preceding record. For one method, review: http://allenbrowne.com/subquery-01.html#AnotherRecord

[grampiano]

Ive bein working in access now for 2 months dont have aloth of practical experience, im doing an Internship they gave me an exercise. What must actually happen the user is suppose to duplicate a record with a new id and for the new record the Closing Hours of the previous record will become the Opening Hours of the new record it must "not" write the data to the Masterplant because they will always import new assets it must write the records to the plantTransaction table and it not suppose to write the data to the Transaction Form on the subform it must write the data only to the plantTransaction table so i think my fields might be wrong and I think Masterplant table must have a id number for example MasterID and the [Plant Number] must come from the Master table not the Plant Transaction table, so which mean Masterplant is one and plantTransaction is many so it will be a one too many relationship. I think too do this duplicate record i must use sumthing lyk this, http://allenbrowne.com/ser-57.html but i dont know how to incorporate it into my save button for duplicate purposes.
This is how it work i click Edit and if I click Save it must duplicate the record into the plantTransaction table not too the Masterplant table if I have 5 plants in my subform on the Transaction Form I must have the ability too edit that record and Save it so it wont requery the subform it will requery the actual PlantTransaction table it will only clear the fields of the plantTransaction table in the subform the 5 records will still exist because i wrote it to the plantTransaction table we wont see it on a later stage ill make it visible for the user too see it but Ive bein struggling with this now for too long ill upload my exercise if theres any way we can simplify this Exercise please help me because i will atmost appreciate it just point me in the right direction. very thankful for your help. Check attachment for required exercise i think i made my own life difficult when I started this exercise.

[June7]

You need a properly designed form/subform arrangement. Review http://office.microsoft.com/en-us/ac...010098674.aspx

Get the forms properly structured and related and much of the code you are trying can go away.

The main form is bound, not unbound as stated in your post. But the textboxes on Transaction form are not bound. Why not? Some of those look like should be on the subform (TransactionID, Opening_Hours, Closing_Hours) and some already are (TransactionDate, Take on Hours, Fuel, Hours Worked, Fuel, Consumption). Maintenance is not a field in any table.

The RecordSource for each form should not be a join of MasterPlant and PlantTransaction tables.

RecordSource for Transaction form:
SELECT * FROM MasterPlant ORDER BY [Plant Number];

RecordSource for PlantTransactionQuery form:
SELECT * FROM PlantTransaction ORDER BY TransactionDate;

Set the subform container control Master/Child links properties to: Plant Number

The PlantNumber table looks unnecessary. MasterPlant has PlantNumber as primary key. There is a PlantNumber in MasterPlant not in PlantNumber.

Shouldn't TransactionID in PlantTransaction be a unique ID? Could use Autonumber field for that then no code is needed to generate this ID.

Advise no spaces in any names (fields, tables, queries, reports).

Should not save Description, Categories, Location into PlantTransaction. Just save the PlantNumber unique ID. The Master/Child links will automate that. The related info can be retrieved by joining tables in report RecordSource.

[grampiano]

Did you have a look at my attachment the actual Exersice.zip is what i should do, check previous post Thanks June7 for helping me

[June7]

I did read through it and little of it makes sense to me. It seems to have been written by someone for whom English is a second language. There are lots of typos, even incomplete statements (see item 1 of Part Two). If this is an assignment from a class, I find it seriously flawed not only in the text but in the concepts it is attempting to teach.