Re: Filter a Form-----Frustration Factor High

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


.

Relevant Pages

  • Filter a Form-----Frustration Factor High
    ... Dim rst As DAO.Recordset ... Dim strSearchName As String ... Dim AMOUNT As Integer ...
    (comp.databases.ms-access)
  • Re: Separating 1 workbook into multiple workbooks
    ... It will filter on column A this example ... I assume that your headers are in row 1 so the header "Supervisor" is in A1 ... Dim CalcMode As Long ... 'Name of the sheet with your data ...
    (microsoft.public.excel.misc)
  • Re: Separating 1 workbook into multiple workbooks
    ... It will filter on column A this example ... I assume that your headers are in row 1 so the header "Supervisor" is in A1 ... Dim CalcMode As Long ... 'Name of the sheet with your data ...
    (microsoft.public.excel.misc)
  • Re: Separating 1 workbook into multiple workbooks
    ... It will filter on column A this example ... I assume that your headers are in row 1 so the header "Supervisor" is in A1 ... Dim CalcMode As Long ... 'Name of the sheet with your data ...
    (microsoft.public.excel.misc)
  • Re: Separating 1 workbook into multiple workbooks
    ... If you want we can make a macro to mail the 120 workbooks also. ... It will filter on column A this example ... Dim CalcMode As Long ... 'Name of the sheet with your data ...
    (microsoft.public.excel.misc)