Apply filter if current record = new record

[capndave]

I want to apply a filter(s) to table(s) that are used in combo boxes on a form using a macro if the current record is a new record.

The goal is to prevent the assignment of obsolete personnel codes to new records while still allowing the user to view the obsolete codes as they apply to older records.

 

[anasazii]

capndave,

does it have to be a macro?

otherwise in the current event of your form you can test to see if it's a new record and then apply the appropriate filters for your combo boxes.

the code would be something like:

Private Sub Form_Current()
If (Me.NewRecord) Then
'code to filter comboboxes or change rowsource
End If
End Sub

I would imagine the macro would be similar...testing for newrecord in the conditional and then setting the rowsources and requery comboboxes???

Does that help at all?
Janel

 

[kpal29]

In the forms current event enter this code:

If Me.NewRecord = True Then
Me.cmbobox.RowSource = "sELECT tbl.id, tbl.personnelcode, tbl.inactive FROM tbl WHERE (((tbl.inactive)=False)); "
Else
Me.cmbobox.RowSource = "sELECT tbl.id, tbl.personnelcode FROM tbl; "
End If

 

[capndave]

Kpal29-

Thanks for your response. Code is new for me, that is why I was trying a macro, but I am trying to get this to work. This is where I am




Private Sub Form_Current()
If Me.NewRecord = True Then
Me.INSERTEDBy.RowSource = "sELECT xInsertedBy.ID, xInsertedBy.Name, xInsertedBy.Archived FROM tbl WHERE(((tbl.inactive)=False));"

Else
Me.INSERTEDBy.RowSource = "sELECT xInsertedBy.ID, xInsertedBy.Name FROM tbl"
End If

End Sub

I get an error message at line 3.

Appreciate any assistance. Thanks Capndave

 

[kpal29]

What is the error message?

 

[capndave]

I resolved the error message- Thanks

But I have a new ?

As I said I am new to Code-

I got this routine to run for one combo box on the form. How do I format code to apply filters to several combo boxes if current record= new record.

Do I just repeat the similar code for other filtered combo boxes between the last END IF and END SUB?

 

[kpal29]

Yes, that is exactly what you do.