Question for John Vinson

G

Guest

Hello John,
Some time ago (12/3/2004) you described how to select a Postcode & State
depending on the suburb selected.
I have been trying to do this for a while now but i am not having any luck,
your help would be most apperiated.

I have a table, tblPostcodes with the fields, Postcode, State, Suburb.
I have on my Customers form 3 combo boxes named, cboSuburb, cboState,
cboPostcode, the Control Source for each cbo is its field in the Customers
table. eg: the control source for cboSuburb is the field Suburb in the
Customers table.

I have connected the code below to the AfterUpdate events of the respective
combo's.

Private Sub cboSuburb_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all postcodes
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes ORDER BY
Postcode;"
' If a suburb is selected, limit the postcode to those in the selected
' suburb; if the suburb has only one postcode, just set it to that value
If Not IsNull(cboSuburb) Then
Set db = CurrentDb
strSQL = "SELECT Postcode, State FROM tblPostcodes WHERE " & _
"tblPostcodes.Suburb=" & Chr(34) & cboSuburb & Chr(34) & _
IIf(IsNull(Me.cboState), " ", Me.cboState) & " AND tblPostcodes.State =
'" _
& Me.cboState & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this suburb isn't in the postcode table
Case 1
' If there's just one suburb/postcode, set postcode and state
' to the selected one
Me.cboPostcode = rs!Postcode
Me.cboState = rs!State
Case Else
' to the selected suburb's postcodes
Me.cboPostcode.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboSuburb_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

_____________________________________________________________________

Private Sub cboState_AfterUpdate()
On Error GoTo PROC_ERR

If IsNull(cboState) Then
'State has been cleared, set postcode and suburb combos to all
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State" _
& "FROM tblPostcodes WHERE tblPostcodes.State='" & cboState & "'" _
& "ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "WHERE tblPostcodes.State='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

_____________________________________________________________________

Private Sub cboPostcode_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all suburbs
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State FROM " _
& "tblPostcodes ORDER BY Suburb;"
If Not IsNull(cboPostcode) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT Suburb, State FROM tblPostcodes" _
& "WHERE tblPostcodes.Postcode='" & cboPostcode & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
'do nothing
Case 1
Me.cboSuburb = rs!Suburb
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & "in cboPostcode_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
_____________________________________________________________________

Private Sub Form_Current()
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode " _
& "FROM tblPostcodes ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT City, State " _
& "FROM tblPostcodes WHERE [State]='" & cboState & "' ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes " _
& "WHERE [State]='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT



End Sub

_____________________________________________________________________

The problem i am having is when i select a suburb from the combo i get an
error message,
Error 3021 in cboSuburb_AfterUpdate: No Current Record

Can you see where i have gone wrong
regards David
 
A

Aaron Kempf

John Vinson is a MDB newbie dork

you should find a real database, kid

Access Data Projects make all this MDB crap obsolete



Dave said:
Hello John,
Some time ago (12/3/2004) you described how to select a Postcode & State
depending on the suburb selected.
I have been trying to do this for a while now but i am not having any luck,
your help would be most apperiated.

I have a table, tblPostcodes with the fields, Postcode, State, Suburb.
I have on my Customers form 3 combo boxes named, cboSuburb, cboState,
cboPostcode, the Control Source for each cbo is its field in the Customers
table. eg: the control source for cboSuburb is the field Suburb in the
Customers table.

I have connected the code below to the AfterUpdate events of the respective
combo's.

Private Sub cboSuburb_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all postcodes
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes ORDER BY
Postcode;"
' If a suburb is selected, limit the postcode to those in the selected
' suburb; if the suburb has only one postcode, just set it to that value
If Not IsNull(cboSuburb) Then
Set db = CurrentDb
strSQL = "SELECT Postcode, State FROM tblPostcodes WHERE " & _
"tblPostcodes.Suburb=" & Chr(34) & cboSuburb & Chr(34) & _
IIf(IsNull(Me.cboState), " ", Me.cboState) & " AND tblPostcodes.State =
'" _
& Me.cboState & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this suburb isn't in the postcode table
Case 1
' If there's just one suburb/postcode, set postcode and state
' to the selected one
Me.cboPostcode = rs!Postcode
Me.cboState = rs!State
Case Else
' to the selected suburb's postcodes
Me.cboPostcode.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboSuburb_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

_____________________________________________________________________

Private Sub cboState_AfterUpdate()
On Error GoTo PROC_ERR

If IsNull(cboState) Then
'State has been cleared, set postcode and suburb combos to all
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State" _
& "FROM tblPostcodes WHERE tblPostcodes.State='" & cboState & "'" _
& "ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "WHERE tblPostcodes.State='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

_____________________________________________________________________

Private Sub cboPostcode_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all suburbs
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State FROM " _
& "tblPostcodes ORDER BY Suburb;"
If Not IsNull(cboPostcode) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT Suburb, State FROM tblPostcodes" _
& "WHERE tblPostcodes.Postcode='" & cboPostcode & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
'do nothing
Case 1
Me.cboSuburb = rs!Suburb
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & "in cboPostcode_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
_____________________________________________________________________

Private Sub Form_Current()
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode " _
& "FROM tblPostcodes ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT City, State " _
& "FROM tblPostcodes WHERE [State]='" & cboState & "' ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes " _
& "WHERE [State]='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT



End Sub

_____________________________________________________________________

The problem i am having is when i select a suburb from the combo i get an
error message,
Error 3021 in cboSuburb_AfterUpdate: No Current Record

Can you see where i have gone wrong
regards David
Click to expand...
 
J

John W. Vinson

Hello John,
Some time ago (12/3/2004) you described how to select a Postcode & State
depending on the suburb selected.
I have been trying to do this for a while now but i am not having any luck,
your help would be most apperiated.
Click to expand...

A bit tied up today... I'll try to get to this later this afternoon or
tomorrow.

And I'll plead guilty to Aaron's characterization, for what that's worth.

John W. Vinson [MVP]
 
G

Guest

Well Aaron, can you help with my problem

Aaron Kempf said:
John Vinson is a MDB newbie dork

you should find a real database, kid

Access Data Projects make all this MDB crap obsolete



Dave said:
Hello John,
Some time ago (12/3/2004) you described how to select a Postcode & State
depending on the suburb selected.
I have been trying to do this for a while now but i am not having any luck,
your help would be most apperiated.

I have a table, tblPostcodes with the fields, Postcode, State, Suburb.
I have on my Customers form 3 combo boxes named, cboSuburb, cboState,
cboPostcode, the Control Source for each cbo is its field in the Customers
table. eg: the control source for cboSuburb is the field Suburb in the
Customers table.

I have connected the code below to the AfterUpdate events of the respective
combo's.

Private Sub cboSuburb_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all postcodes
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes ORDER BY
Postcode;"
' If a suburb is selected, limit the postcode to those in the selected
' suburb; if the suburb has only one postcode, just set it to that value
If Not IsNull(cboSuburb) Then
Set db = CurrentDb
strSQL = "SELECT Postcode, State FROM tblPostcodes WHERE " & _
"tblPostcodes.Suburb=" & Chr(34) & cboSuburb & Chr(34) & _
IIf(IsNull(Me.cboState), " ", Me.cboState) & " AND tblPostcodes.State =
'" _
& Me.cboState & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this suburb isn't in the postcode table
Case 1
' If there's just one suburb/postcode, set postcode and state
' to the selected one
Me.cboPostcode = rs!Postcode
Me.cboState = rs!State
Case Else
' to the selected suburb's postcodes
Me.cboPostcode.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboSuburb_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

_____________________________________________________________________

Private Sub cboState_AfterUpdate()
On Error GoTo PROC_ERR

If IsNull(cboState) Then
'State has been cleared, set postcode and suburb combos to all
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State" _
& "FROM tblPostcodes WHERE tblPostcodes.State='" & cboState & "'" _
& "ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes" _
& "WHERE tblPostcodes.State='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

_____________________________________________________________________

Private Sub cboPostcode_AfterUpdate()
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all suburbs
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State FROM " _
& "tblPostcodes ORDER BY Suburb;"
If Not IsNull(cboPostcode) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT Suburb, State FROM tblPostcodes" _
& "WHERE tblPostcodes.Postcode='" & cboPostcode & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
'do nothing
Case 1
Me.cboSuburb = rs!Suburb
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error" & Err.Number & "in cboPostcode_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
_____________________________________________________________________

Private Sub Form_Current()
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboSuburb.RowSource = "SELECT DISTINCT Suburb, State " _
& "FROM tblPostcodes ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode " _
& "FROM tblPostcodes ORDER BY Postcode;"
Else
Me.cboSuburb.RowSource = "SELECT DISTINCT City, State " _
& "FROM tblPostcodes WHERE [State]='" & cboState & "' ORDER BY Suburb;"
Me.cboPostcode.RowSource = "SELECT Postcode FROM tblPostcodes " _
& "WHERE [State]='" & cboState & "' ORDER BY Postcode;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT



End Sub

_____________________________________________________________________

The problem i am having is when i select a suburb from the combo i get an
error message,
Error 3021 in cboSuburb_AfterUpdate: No Current Record

Can you see where i have gone wrong
regards David
Click to expand...
Click to expand...
 
L

Larry Linson

Dave said:
Well Aaron, can you help with my problem

"Aaron Kempf" wrote:
Click to expand...

Mr. Kempf rarely helps with problems. He mostly just snipes and spreads
misinformation.

Larry Linson
Microsoft Access MVP
 
J

John W. Vinson

I have a table, tblPostcodes with the fields, Postcode, State, Suburb.
I have on my Customers form 3 combo boxes named, cboSuburb, cboState,
cboPostcode, the Control Source for each cbo is its field in the Customers
table. eg: the control source for cboSuburb is the field Suburb in the
Customers table.
Click to expand...

ok... sounds reasonable...
I have connected the code below to the AfterUpdate events of the respective
combo's.
Click to expand...
The problem i am having is when i select a suburb from the combo i get an
error message,
Error 3021 in cboSuburb_AfterUpdate: No Current Record

Can you see where i have gone wrong
Click to expand...

Not really. I'd put a breakpoint in the Suburb AfterUpdate code (mouseclick in
the grey bar to the left of the code window next to an executable - not a Dim
- statement). Run the code by selecting a suburb. Execution will stop at that
line; you can then use the Debug menu options to step through the code (F8
will step one line for instance, learn the keyboard shortcuts from the menu
options!) You can hover the mouse over a variable to see its value, or type

?variablename

in the Immediate window to see its value.

Can you be sure that the State combo has been selected when you first select a
Suburb (e.g. does State have a default value)? If not, your code may have
problems: the query you're building uses the value of cboState, and that might
not exist!

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo box help 2
Combo Box field look up 24
Combo boxes and append query or what? 5
Fill Fields automatically on form based on a control's value 1
excluding certain field value from recordset output 3
Filter Command Button 2
use Record Selector to send records to web page 3
Invalid use of null 1

Top