Data Entry Form - Combobox Value After Insert New Record

[JCW32]

I have spent a lot of time searching this issue/trial and error, and have had no luck. I have created a Data Entry Form that has five Combo Boxes, each box linking to its respective column in the table. Right now, I have the form working to where when I enter the values in each Combo Box, I click the Push Button that has the action "New Record", and the values are entered into the table, and the Combo Box values are emptied, which is how it's suppose to work. Also, I have the "Add Data Only" on "Yes" so that when I open the form, the Combo Boxes are empty, and are ready to be entered in a new blank record in my table.

**What I want to happen: I, or the user, will be entering in multiple new records that are almost identical, besides maybe one Combo Box having a different value every time. So, when I click on the button to enter the new record, I want the values in my Combo Boxes (besides the one that will change every time--ToteNumber) to remain in the Combo Boxes, so that I only have to re enter the value for my one field, instead of having to re-type all the values over again.

As I said, I have done a lot of research and have come across posts about "Copy Field / Record Cloning" but have not been successful executing it. What I have now is a Macro that I've have assigned to the "Execute Action" Event (with NO action on the general tab of the push button assigned--i.e. "New Record") is as follows:

Code: Select all

Sub Clone_To_New_Record (oEvent As Object) 'Button > Execute > event

	DIM oForm As Object
	DIM oStatement
	DIM sColumns As String
	DIM iTransactionID As Integer
	DIM sSQL As String
	
	oForm = oEvent.Source.Model.Parent 
	IF oForm.isNew THEN Exit Sub
	
	iTransactionID = oForm.Columns.GetByName("TransactionID").Value        ' "TransactionID" is the Primary Key in my table "Transaction"
	oForm.updateRow()
	oForm.moveToInsertRow()
	oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
	sColumns = "" 
	sColumns = sColumns & """Date"""                                
	sColumns = sColumns & ", ""ScanLocID"""
	sColumns = sColumns & ", ""ToteNumber"""		
	sColumns = sColumns & ", ""BOLNumber"""
	sColumns = sColumns & ", ""Process"""
	sSQL = "INSERT INTO ""Transaction"" (" & sColumns & ") SELECT " & sColumns & " FROM ""Transaction"" WHERE ""TransactionID"" = " & iTransactionID
	oStatement.executeUpdate( sSQL ) 'Execute the SQL command
	oForm.reload()
	oForm.last()
End Sub

However, the problem I run into is, is that if I leave the "Add Data Only" to "No", then my Combo Box values are of the last record entered in my table, which I do not want (but everything else works as far as inserting the new record, and my values remaining in the Combo Boxes). If I turn the "Add Data Only" to "Yes", then the Macro does not execute properly by not inserting my record into the table. I then tried changing the push button action on the general tab to "Save Record" while keeping the Macro assigned to the "Execute Action" Event, and kept the "Add Data Only" to "Yes", and when I hit the button, it does insert the new record into the table, and keeps my previous values in the Combo Boxes, but when I go to insert the next record, it rewrites over the same row/record in my table.

Combo Boxes: ScanLocID ; ToteNumber ; BOLNumber ; Process ; Date

Not sure where to go from here and appreciate any advice/guidance provided. Thanks in advance!

[JCW32]

Another thing to add--Like I said in my first post, when I have the Macro assigned to the "Execute Action" Event, with the "Action" on the general tab for the push button to "Save Record", with the "Add Data Only" set to yes, the new record is inserted properly into my table, and my values in my combo boxes remain, but when I go to insert a new record after that, it just rewrites over in the same record/row in my table. So, I had the idea to maybe add a line at the end of my code to "Move to the next Record" or something like that, but haven't been able to find the write code. I tried adding "oForm.moveToInsertRow()" to the end (Already at the top), but that didn't do anything.

Not sure if this is an option to fix my problem, but just adding it on here just in case anyone knows the proper way to execute this, or any other ideas. Thanks in advance, again!