SQL & Programming

Private Sub City_List_AfterUpdate()

Dim SQLStmt, R As Recordset

SQLStmt = "SELECT DISTINCT state FROM businesses " _

& "WHERE state IS NOT NULL AND City = '" & Me![City List] & " ';"

Me![State List].RowSource = SQLStmt

Me![State List].Requery

Set R = CurrentDb.OpenRecordset(SQLStmt)

If R.RecordCount > 0 Then

R.MoveLast

If R.RecordCount = 1 Then

Me![State List] = R![State]

End If

End If

SQLStmt = "SELECT DISTINCT zip FROM businesses” _

& “WHERE city = '" & Me![City List] & "' AND state = '”_

& Me![State List] & "’ AND zip IS NOT NULL;"

Me![Zip List].RowSource = SQLStmt

Me![Zip List].Requery

Set R = CurrentDb.OpenRecordset(SQLStmt)

If R.RecordCount > 0 Then

R.MoveLast

If R.RecordCount = 1 Then

Me![Zip List] = R![Zip]

End If

End If

End Sub

Previous slide

Next slide

Back to first slide

View graphic version