Combo Box field look up

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
OK - then you don't need to do anything with the recordset or the
recordsetclone.

This is what you currently have as code for the AfterUpdate event of the
cboSuburb combo box:

Private Sub cboSuburb_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.MoveFirst
rs.FindFirst "[PostCode] = " & Me![cboSuburb].Column(2)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Replace it with this code to do what you want:

Private Sub cboSuburb_AfterUpdate()
' Write the value of PostCode into the PostCode textbox
Me.PostCode.Value = Me![cboSuburb].Column(2)
End Sub


--

Ken Snell
<MS ACCESS MVP>

Robin Chapple said:
Sorry that an earlier message got lost in cyberspace.
I do not understand the reference to moving the form's recordset.

When I understand that technique I may be able to progress with a
response.

What I am trying to do is locate a suburb, enter that suburb in the
database and also add the postcode to the database which I believe is
what you have said.

Thanks for your patience

Robin
I have looked at the database ... a few questions.

(1) Why are you trying to move the form's recordset to one that has a
matching post code for the suburb that is being selected? The post code is
not the primary key of the form's recordset... I think the logic that you're
using here is not what you intend to be doing? Why do you want to change the
form's record just because a suburb is selected? Aren't you trying to enter
the suburb that is for the person who is the main data item for the form's
record? Perhaps you're wanting to write the PostCode value from the combo
box into the field that holds the PostCode value for the current record? If
yes, then you want this as the code:
If Len(Me.cboSuburb.Value & "") > 0 Then _
Me.PostCode.Value = Me.[cboSuburb].Column(2)

(2) Your code that is trying to move the recordset after you select a suburb
uses a logical test of
If Not rs.EOF Then ....
Instead of this test, if you want to do the move so long as a match is found
(but again, see my question in (1) above), I'd use
If Not rs.NoMatch Then .....

(3) The error that you're getting with the code in Form_BeforeUpdate
procedure is being caused by the code in the cboSuburb_AfterUpdate
procedure...namely, the attempt to move the recordset after editing the
value in the cboSuburb control. As soon as I comment out that
cboSuburb_AfterUpdate procedure's code, the error no longer occurs.

(4) I strongly recommend that you rename the textbox that is bound to the
Updated field to "txtUpdated", and then change the code in the
Form_BeforeUpdate procedure to change the value of the txtUpdated control,
not the Updated field/control.
Click to expand...
Click to expand...
 
Thanks Ken,

That is just what I needed. Just shows the folly of copying something
that I did not understand.

Greatly appreciated.

Robin Chapple
 
I have lost the plot.

The original problem is solved. I have attempted to apply the same
idea to another database designed by someone else. He call the Suburb
field Town.

I am using the same PostCode table. I have a combo box, cboTown, which
looks up the town.

I have added this to the VBA:

Private Sub cboTown_AfterUpdate()

' Write the value of PostCode into the PostCode textbox
Me.Postcode.Value = Me![cboTown].Column(2)

End Sub

It does not add the postcode. What have I missed?

Thanks,

Robin Chapple
 
Most likely, the Row Source query for cboTown is not the same as the one for
cboSuburb.

You'll need to use the column number minus 1 (column is a zero-based
property, meaning that the first column in the query is column 0, the second
is column 1, etc.) that corresponds with the PostCode field in the Row
Source query.

--

Ken Snell
<MS ACCESS MVP>


Robin Chapple said:
I have lost the plot.

The original problem is solved. I have attempted to apply the same
idea to another database designed by someone else. He call the Suburb
field Town.

I am using the same PostCode table. I have a combo box, cboTown, which
looks up the town.

I have added this to the VBA:

Private Sub cboTown_AfterUpdate()

' Write the value of PostCode into the PostCode textbox
Me.Postcode.Value = Me![cboTown].Column(2)

End Sub

It does not add the postcode. What have I missed?

Thanks,

Robin Chapple


OK - then you don't need to do anything with the recordset or the
recordsetclone.

This is what you currently have as code for the AfterUpdate event of the
cboSuburb combo box:

Private Sub cboSuburb_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.MoveFirst
rs.FindFirst "[PostCode] = " & Me![cboSuburb].Column(2)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Replace it with this code to do what you want:

Private Sub cboSuburb_AfterUpdate()
' Write the value of PostCode into the PostCode textbox
Me.PostCode.Value = Me![cboSuburb].Column(2)
End Sub
Click to expand...
Click to expand...
 
Thanks again Ken,

Right in one and I have learned another piece of information.

This time I have not used a key field so there were two fields instead
of three.

Cheers,

Robin Chapple
 
Back
Top