Filter a Form-----Frustration Factor High

[birt]

I know I am getting in my own way .... so someone please save me from
myself!

I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?

Private Sub cboRFP_AfterUpdate()

Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer

AMOUNT = DCount("[sol_no]", "RFP*NosTbl", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)

If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh

End If
rst.Close

End Sub

[Rick Brandt]

birt wrote:

I know I am getting in my own way .... so someone please save me from
myself!

I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?

Private Sub cboRFP_AfterUpdate()

Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer

AMOUNT = DCount("[sol_no]", "RFP*NosTbl", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)

If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh

End If
rst.Close

End Sub

All you need is...

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

If there is a match that record will be filtered on and if there is not you
will be taken to the new record position.

Is Sol_No really a character field? If not drop the single quotes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[birt]

On Sep 28, 4:36 pm, "Rick Brandt" <[e-mail]:

birt wrote:

I know I am getting in my own way .... so someone please save me from
myself!

I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?

Private Sub cboRFP_AfterUpdate()

Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer

AMOUNT = DCount("[sol_no]", "RFP*NosTbl", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)

If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh

End If
rst.Close

End Sub

All you need is...

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

If there is a match that record will be filtered on and if there is not you
will be taken to the new record position.

Is Sol_No really a character field? If not drop the single quotes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Hello Rick,

Thank you for your response. I do have a few questions. The sol_no
field is a combo of text and # I am using Test1.

1.) When I changed the code for the before update event to the below I
get the Error Msg 2115 - Function set to the
before update property for this field is preventing Access from
saving data in this field. Debug takes me to
Me.FilterOn = True

Private Sub cboRFP_BeforeUpdate(Cancel As Integer)

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

End Sub

2.) When I put the above code into the After Update Event I get the
Error Msg - The changes were not successful because
they would create duplicate values in the primary key field. It
too takes me to Me.FilterOn = True

What am I doing wrong?

Thank you,
Deb

[Rick Brandt]

birt wrote:

Hello Rick,

Thank you for your response. I do have a few questions. The sol_no
field is a combo of text and # I am using Test1.

1.) When I changed the code for the before update event to the below I
get the Error Msg 2115 - Function set to the
before update property for this field is preventing Access from
saving data in this field. Debug takes me to
Me.FilterOn = True

Private Sub cboRFP_BeforeUpdate(Cancel As Integer)

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

End Sub

Nees to be AfterUpdate as you have below.

2.) When I put the above code into the After Update Event I get the
Error Msg - The changes were not successful because
they would create duplicate values in the primary key field. It
too takes me to Me.FilterOn = True

What am I doing wrong?

The ComboBox that you use to apply the filter needs to be an UNBOUND
control. Otherwise you are actually changing data in whatever record you
are currently looking at. Just remove whatever ControlSource property you
currently have in the ComboBox.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com