I solved the problem of making "Filter by Form" work in harmony with a combo box and the "Not in List" event. I set the property of the combo box Limit to List to No. That allowed me to use the Filter by Form as I wanted.
The next step was to validate what the user chose or typed into the combo box. I used the Before Update event as follows:
Private Sub strSource_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
If (Nz(Me.strSource) = vbNullString) Then
' Debug.Print "Nothing to test therefore exit sub"
Exit Sub
End If
' Check to see whether the supplied sales source exists in the database
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tlkpNewSource.newsource as newsrc, tlkpNewSource.Action From tlkpNewSource WHERE (((tlkpNewSource.Action) = 'Current')) ORDER BY tlkpNewSource.newsource", dbOpenDynaset)
Do Until rst.EOF
If Me.strSource = rst!newsrc Then
' Debug.Print "OK Source in list"
GoTo TidyClose
End If
rst.MoveNext
Loop
'Debug.Print "Fell out of loop therefore not in list"
MsgBox "Referred by value not in system, please pick one from the list", , gstrDatabaseName
Me.strSource.Undo
' Set the Response variable to tell Access NOT to move out of the field
' but to remove the data and wait for the corrected data
Cancel = vbYes
TidyClose:
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Exit_strSource_BeforeUpdate:
End Sub
It worked but seems a bit clunky – is there a more elegant solution? All ideas welcomed.
---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar