Write to next row in continuous subform (1 Viewer)

[BenMason]

I have a popup form that has data entry input for the fields Trail_Code and Trail_Desc. The form has a “+” sign button to add the data to my continuous subform, which it does. The problem I’m having is that it only writes to one row of my subform. When I do another manual entry and click on the “+” button a second time, I want it to write to a new row in my subform, but it only overwrites the first row.

How can I get the popup form to write to my subform record row? I think there is a way to tell it to go to a new record and then write again to a different row, but I’m not sure how to do it. I think I have to save the INSERT record which I have SQL code for, but something is not right.

Below is my code that correctly writes it to my continuous subform but only one row, and overwrites the row when I write to it again. Please help.

'This vba write to the subform
Forms!f_Main!sfm_Trails!Trail_Code = sTrailCode
Forms!f_Main!sfm_Trails!Trail_Desc = sTrailDesc

Dim sSQL as string
Dim sTrailCode as string
Dim sTrailDesc as string

sSQL = “INSERT INTO Trail_Boxes”
sSQL = sSQL + “ ([Trail_Code], [Trail_Desc])”
sSQL = sSQL + “ VALUES (“ & sTrailCode & “, ‘” & sTrailDesc & “’) “
DoCmd.RunSQL sSQL

 

[June7]

An INSERT Action SQL will insert a new row into table. Form must be refreshed to display record added by INSERT action. But if you are inputting data into bound fields then you are simply updating same record if you don't move to new record row. Editing bound controls and running INSERT action should result in two records with same data.

How are variables sTrailCode and sTrailDesc populated? Also, referencing variables before they are declared makes no sense.

Why would you need to use INSERT action anyway? Use a bound form and move to new record row to enter new data.

 

[arnelgp]

to be able to duplicate the TrailCode to your table, you Index on TrailCode must not be Unique or Primary.

 

[Gasman]

Any particular reason your are using + and not & ?

 

[Pat Hartman]

Why are you using an unbound form? There is something logically wrong with what you are doing.

You are creating duplicate records but due to a logic error, you are simply overwriting a single record so just fixing the logic error will expose the duplicates.

The + and the & work differently in VBA. The & is the standard concatenation operand so unless you want to employ the special property of the + to suppress the entire string if part of it is Null, then stick to the standard operand.

 

[BenMason]

Thank you everyone for your help.

 

[BenMason]

An INSERT Action SQL will insert a new row into table. Form must be refreshed to display record added by INSERT action. But if you are inputting data into bound fields then you are simply updating same record if you don't move to new record row. Editing bound controls and running INSERT action should result in two records with same data.

How are variables sTrailCode and sTrailDesc populated? Also, referencing variables before they are declared makes no sense.

Why would you need to use INSERT action anyway? Use a bound form and move to new record row to enter new data.

You were right. I didn't need the insert. It's writing to the subform and saving to the table. I moved the focus to the subform, then me.dirty = false, goto next record is working. but sometimes it says it can't go to the next record. Sometimes it works then it stops working with the error. Maybe i need to move off the subform and then back to the subform so it knows it's ok to move to the next record.

 

[Pat Hartman]

I think you are making this harder than it needs to be. Do you have a save button on the subform record? In what event do you have the code that moves to a new record? If the user tabbed off the record he was entering, then when your code runs, it may already be on a new record and so that would generate an error.