Full Version: Insert Into Query Creates An Extra Record
UtterAccess Forums > Microsoft® Access > Access Forms
NineIron
The following code enters an extra record into my table then, displays in continuous form. How can I avoid this?
!--c1-->
CODE
Private Sub cboMeasurePeriod_Click()
Dim lngOpenArgs As Long
Dim dtToday As Date
    lngOpenArgs = OpenArgs
    dtToday = Date
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "insert into BIOMETRICS(patient_id, biometric_id, measure_date, measure_period_id, create_dt) " & _
                "select " & lngOpenArgs & ", biometric_id, #" & dtToday & "#, " & Me.cboMeasurePeriod & ", #" & dtToday & "# " & _
                "from ref_Biometrics;"
                
    DoCmd.RunCommand acCmdSaveRecord
        
    DoCmd.SetWarnings True
    
    Me.Requery
    
End Sub
ipisors
Weird I swear I answered this exact question (almost) about a week ago. (someone else, I mean) - can't find the post.
Because your code is entering it twice - one in an unbound fashion, by executing the SQL statement, and one by executing a Save command on what (I'm assuming) is a Bound form.
Basically ..... You probably should step back and consider your requirements and all the factors involved, and then choose 1 or the other.
NineIron
Same thing without the DoCmd.RunCommand acCmdSaveRecord. However, I just noticed that when my continuous form opens, there are two blank records. Shouldn't I only expect one?
ipisors
Based on the fact that you were coding to do both a SQL insert and an action that only makes sense if the form is bound, I assumed you had a bound form and that was the issue. Now it seems that is in question.
Maybe you could start by providing more details about your end of things? What kind of form(s) do you have? what are their recordsources? do you have code in the form's current, open or load events? what is the dataentry property of the form?
NineIron
I've attached the db.
From the Switchboard, choose SCOTT CAMPBELL, go to the Biometrics tab. Click Add Metrics. Click the dropdown and choose Baseline. What I want to happen is to have all of the Biometrics from the table ref_Biometrics to be appended into BIOMETRICS. Then, have the form requery to show the values. Then, the user can just enter values for the different metrics.
NineIron
Does it have something to do with the OpenArgs? See what happens with it commented out vs not.
ipisors
Ok, I think the problem is that you have the code in the Click event of the combobox. I'm 99% sure you wouldn't want to do that. That means stuff is going in the table every single time it's clicked!! You want that to happen AfterUpdate, probably.
NineIron
Thanx. I'll give it a shot.
NineIron
This worked out well. Thanx again.
ipisors
I'm glad it's working now
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.