Docmd.Filter creates new record

[JBG]

Hi,

I have an Access 97 form that uses a query (made up of two tables) as its record source.

I am using the following code within an after update event to populate the form with the correct record after a user enters a social security njumber:

 varResult = DLookup("[ipkT801ID]", "tblT801", "[txtSSN] = '" & Me.txtSSNform & "' AND txtOmniID = '" & Me.cboFindPlan.Column(0) & "'")
If Not IsNull(varResult) Then
    Me.Filter = "[ipkT801ID] = " & varResult
    Me.FilterOn = True
    Me.cmdEdit.Enabled = True
    Me.cmdAdd.Enabled = True
    Me.cmdEdit.SetFocus
Else
    me.cmdAdd.Enabled = True
    Me.cmdEdit.Enabled = False
    Me.cmdAdd.SetFocus
End If

The problem is that just after the filter command, a new record is created in the main table of the underlying query, with justone field (the social security field) getting populated. The form, however, does properly filter to the correct record.

If I am making sense, anyone know why a new record (with just one field populated) is being created each time I filter a form?

Thanks,

JBG

 

[mrf1xa]

Is the text box into which the user enters the security number bound to the underlying table? If so, each time a number is entered, a new record will be started regardless of what you do afterwards (filtering etc).

You would need to make this text box unbound, then if a match is found use code to copy this value to a bound box if necessary.

HTH Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[JBG]

Thanks Nigel. Yup, I was at this point right away, and tried a millionm other things. but no-go. The form was given to me by another programmer, and I did not want to tear it apart , so I did a quick and dirty work around to clear errant files....

JBG