Combo Box field look up

[Robin Chapple]

I have a table of suburbs and postcodes where I use a combo box to
select the suburb which is them stored in my database.

I am attempting to store the postcode field as well.

This is the code:

Private Sub cboSuburb_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PostCode] = '" & Me![cboSuburb] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


The PostCode field is a long integer number in each database and I get
the error message:

"Type mismatch"

What have I done wrong?

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

Your code is delimiting the value from the combo box with ' characters. Use
this instead:

rs.FindFirst "[PostCode] = " & Me![cboSuburb]

 

[Robin Chapple]

Ken,

I believe that you intended that I should replace one line of the
code:

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

This now gives"

Runtime error '3070'

" .. .. does mot recognise SuburbName as a valid field name or
expression.

What have I done wrong now?

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

Yes, what you changed is what I had suggested. That is because you said that
PostCode field is a long integer field, meaning that its data are numeric,
not text. The ' delimiters are used only for delimiting text data strings.

I see another change that you should make. Change
Set rs = Me.Recordset.Clone
to
Set rs = Me.RecordsetClone
rs.MoveFirst

You don't say on which code line the error occurs, but I'm assuming it's
this one:
rs.FindFirst "[PostCode] = " & Me![cboSuburb]

The error message indicates that the database engine is looking for a field
named SuburbName, which I don't see being used anywhere in your code. Is
cboSuburb control bound to a field -- possibly a field named SuburbName? On
the assumption that it is, change
rs.FindFirst "[PostCode] = " & Me![cboSuburb]
to this:
rs.FindFirst "[PostCode] = " & Me.cboSuburb.Value

I'm guessing that the Me![cboSuburb] syntax somehow is picking up the
ControlSource expression instead of the Value for cboSuburb, though I have
not seen that behavior before.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I believe that you intended that I should replace one line of the
code:

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

This now gives"

Runtime error '3070'

" .. .. does mot recognise SuburbName as a valid field name or
expression.

What have I done wrong now?

Thanks,

Robin Chapple

Your code is delimiting the value from the combo box with ' characters. Use
this instead:

rs.FindFirst "[PostCode] = " & Me![cboSuburb]

 

[Robin Chapple]

I have now changed the code to:

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]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I confirm that the PostCode field in each table is numeric- long
integer.

The error message part that I recorded as SuburbName was meant to
advise that the name of the selected suburb was part of the error
message. My apology for misleading information. So "Melbourne" would
be there if I selected Melbourne as the required suburb.

I then made your second change:

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.Value
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This produces a different error message:

" Run Time error '3077'
Syntax error (missing operator) in expression.

Thanks for your patience.

Robin Chapple

 

[Ken Snell [MVP]]

OK - what's happening is that your combo box's BoundColumn is the column
that holds the name of the suburb, not the column that holds the PostCode. I
am assuming that the PostCode is held in a different column in the combo
box's Row Source.

Assuming that the PostCode is in column 3 (for example) of your combo box's
Row Source, the code then would be
rs.FindFirst "[PostCode] = " & Me![cboSuburb].Column(2)

Can you tell us the line of code on which the error is occurring? When the
error occurs, you should be given the option to Debug on the popup
message - - click that button and the offending line of code will be shown
to you highlighted in yellow. This will help us further debug what is wrong.

--

Ken Snell
<MS ACCESS MVP>

I have now changed the code to:

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]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I confirm that the PostCode field in each table is numeric- long
integer.

The error message part that I recorded as SuburbName was meant to
advise that the name of the selected suburb was part of the error
message. My apology for misleading information. So "Melbourne" would
be there if I selected Melbourne as the required suburb.

I then made your second change:

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.Value
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This produces a different error message:

" Run Time error '3077'
Syntax error (missing operator) in expression.

Thanks for your patience.

Robin Chapple

Yes, what you changed is what I had suggested. That is because you said that
PostCode field is a long integer field, meaning that its data are numeric,
not text. The ' delimiters are used only for delimiting text data strings.

I see another change that you should make. Change
Set rs = Me.Recordset.Clone
to
Set rs = Me.RecordsetClone
rs.MoveFirst

You don't say on which code line the error occurs, but I'm assuming it's
this one:
rs.FindFirst "[PostCode] = " & Me![cboSuburb]

The error message indicates that the database engine is looking for a field
named SuburbName, which I don't see being used anywhere in your code. Is
cboSuburb control bound to a field -- possibly a field named SuburbName? On
the assumption that it is, change
rs.FindFirst "[PostCode] = " & Me![cboSuburb]
to this:
rs.FindFirst "[PostCode] = " & Me.cboSuburb.Value

I'm guessing that the Me![cboSuburb] syntax somehow is picking up the
ControlSource expression instead of the Value for cboSuburb, though I have
not seen that behavior before.

 

[Robin Chapple]

Thanks Ken,

You are right. Suburb is Column 1 and PostCode is Column 3

Bound column is column 1.

I have changed that row of code

On Sat, 27 Nov 2004 16:20:51 -0500, "Ken Snell [MVP]"

Now I get this error message:

"" Runtime error: '-2147352567(80020009)
Update or CancelUpdate without AddNew or Edit""

With this code highlighted:

Private Sub Form_BeforeUpdate(Cancel As Integer)

[Updated] = Date

End Sub

Many thanks,

Robin

 

[Ken Snell [MVP]]

We're now in a different procedure, right?

I assume that Update is the name of a field in the form's recordset. You
cannot change the value of a field directly unless you open the form's
recordset for editing first, and then update the recordset.

However, I think that is overkill for what you want to achieve here. Simply
use this line of code instead of the one you have:

Me.Updated = Date

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

You are right. Suburb is Column 1 and PostCode is Column 3

Bound column is column 1.

I have changed that row of code

On Sat, 27 Nov 2004 16:20:51 -0500, "Ken Snell [MVP]"

Now I get this error message:

"" Runtime error: '-2147352567(80020009)
Update or CancelUpdate without AddNew or Edit""

With this code highlighted:

Private Sub Form_BeforeUpdate(Cancel As Integer)

[Updated] = Date

End Sub

Many thanks,

Robin

OK - what's happening is that your combo box's BoundColumn is the column
that holds the name of the suburb, not the column that holds the PostCode. I
am assuming that the PostCode is held in a different column in the combo
box's Row Source.

Assuming that the PostCode is in column 3 (for example) of your combo box's
Row Source, the code then would be
rs.FindFirst "[PostCode] = " & Me![cboSuburb].Column(2)

Can you tell us the line of code on which the error is occurring? When the
error occurs, you should be given the option to Debug on the popup
message - - click that button and the offending line of code will be shown
to you highlighted in yellow. This will help us further debug what is

wrong.

 

[Robin Chapple]

Ken,

Now I show my lack of knowledge. I assume the term procedure means the
routine that I am running. In which case the answer is that this is
the same page of code. It is a membership entry form. The Suburb and
post code are fields on the form and I record the date of the most
recent update.

I changed to the alternate code anyway and the error message persists.

Here are the two pieces of code:

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

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Updated = Date

End Sub

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

Procedure is a subroutine or function.

What is Updated on your form? Is it a control on the form? Is it a field in
the form's Recordset? Or are you trying to use it as a method of the form in
some way? A bit more info, please.

Ken,

Now I show my lack of knowledge. I assume the term procedure means the
routine that I am running. In which case the answer is that this is
the same page of code. It is a membership entry form. The Suburb and
post code are fields on the form and I record the date of the most
recent update.

I changed to the alternate code anyway and the error message persists.

Here are the two pieces of code:

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

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Updated = Date

End Sub

Thanks,

Robin Chapple

We're now in a different procedure, right?

I assume that Update is the name of a field in the form's recordset. You
cannot change the value of a field directly unless you open the form's
recordset for editing first, and then update the recordset.

However, I think that is overkill for what you want to achieve here. Simply
use this line of code instead of the one you have:

Me.Updated = Date

 

[Robin Chapple]

Ken,

Thanks for your patience.

So that definition means that the update is not part of the same
procedure?

The Updated field is a field in the database and it is a control on
the form.

Thanks,

Robin Chapple

Procedure is a subroutine or function.

What is Updated on your form? Is it a control on the form? Is it a field in
the form's Recordset? Or are you trying to use it as a method of the form in
some way? A bit more info, please.

Ken,

Now I show my lack of knowledge. I assume the term procedure means the
routine that I am running. In which case the answer is that this is
the same page of code. It is a membership entry form. The Suburb and
post code are fields on the form and I record the date of the most
recent update.

I changed to the alternate code anyway and the error message persists.

Here are the two pieces of code:

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

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Updated = Date

End Sub

Thanks,

Robin Chapple

We're now in a different procedure, right?

I assume that Update is the name of a field in the form's recordset. You
cannot change the value of a field directly unless you open the form's
recordset for editing first, and then update the recordset.

However, I think that is overkill for what you want to achieve here. Simply
use this line of code instead of the one you have:

Me.Updated = Date

 

[Ken Snell [MVP]]

Yes, this "Updated" code step is in a different procedure than the one we
were working on.

Is the name of the field Updated or Update? You've used both names in your
posts. Update is also the name of a method for a recordset, telling ACCESS
to make the changes that you've done to the recordset's record.

It's very likely that ACCESS is becoming confused by your use of a reserved
word as the name of a control and a field. Can you change that to something
else? And also, it'll be good if the textbox that's bound to that field has
a different name than the field (say, txt prefixed to the field name).

But using your current setup, if you use
Me.[Updated] = Date

you are getting this error about an Update statement with no previous Edit
or AddNew method?
--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks for your patience.

So that definition means that the update is not part of the same
procedure?

The Updated field is a field in the database and it is a control on
the form.

Thanks,

Robin Chapple

Procedure is a subroutine or function.

What is Updated on your form? Is it a control on the form? Is it a field in
the form's Recordset? Or are you trying to use it as a method of the form in
some way? A bit more info, please.

Ken,

Now I show my lack of knowledge. I assume the term procedure means the
routine that I am running. In which case the answer is that this is
the same page of code. It is a membership entry form. The Suburb and
post code are fields on the form and I record the date of the most
recent update.

I changed to the alternate code anyway and the error message persists.

Here are the two pieces of code:

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

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Updated = Date

End Sub

Thanks,

Robin Chapple


On Sat, 27 Nov 2004 19:58:39 -0500, "Ken Snell [MVP]"

We're now in a different procedure, right?

I assume that Update is the name of a field in the form's recordset. You
cannot change the value of a field directly unless you open the form's
recordset for editing first, and then update the recordset.

However, I think that is overkill for what you want to achieve here. Simply
use this line of code instead of the one you have:

Me.Updated = Date

 

[Robin Chapple]

The name of the field is "Updated".

I have changed the name of the control to "Updatedtxt" and I now have
this error message:

" Compile error - Method or data members not found "

The yellow highlight is on the line:

Private sub etc etc

Robin

 

[Ken Snell [MVP]]

ACCESS seems to have lost track of that field in your form's recordset.
Delete the control that is bound to it, and close the form (save it). Then
open it again, and then recreate that control (bind it to the Updated
field). See if that works.

--

Ken Snell
<MS ACCESS MVP>

The name of the field is "Updated".

I have changed the name of the control to "Updatedtxt" and I now have
this error message:

" Compile error - Method or data members not found "

The yellow highlight is on the line:

Private sub etc etc

Robin

Yes, this "Updated" code step is in a different procedure than the one we
were working on.

Is the name of the field Updated or Update? You've used both names in your
posts. Update is also the name of a method for a recordset, telling ACCESS
to make the changes that you've done to the recordset's record.

It's very likely that ACCESS is becoming confused by your use of a reserved
word as the name of a control and a field. Can you change that to something
else? And also, it'll be good if the textbox that's bound to that field has
a different name than the field (say, txt prefixed to the field name).

But using your current setup, if you use
Me.[Updated] = Date

you are getting this error about an Update statement with no previous Edit
or AddNew method?

 

[Robin Chapple]

I am not sure that I have understood your instructions.

I have deleted the control "Updated". I closed and saved the form. I
reopened the form and installed the field "Updated".

I still get the error message:

"" Runtime error: '-2147352567(80020009)
Update or CancelUpdate without AddNew or Edit""

With this code highlighted:

Private Sub Form_BeforeUpdate(Cancel As Integer)

[Updated] = Date

End Sub

Robin

 

[Ken Snell [MVP]]

Yes, you did what I suggested.

Post the SQL statement of the form's recordsource.

--

Ken Snell
<MS ACCESS MVP>

I am not sure that I have understood your instructions.

I have deleted the control "Updated". I closed and saved the form. I
reopened the form and installed the field "Updated".

I still get the error message:

"" Runtime error: '-2147352567(80020009)
Update or CancelUpdate without AddNew or Edit""

With this code highlighted:

Private Sub Form_BeforeUpdate(Cancel As Integer)

[Updated] = Date

End Sub

Robin

ACCESS seems to have lost track of that field in your form's recordset.
Delete the control that is bound to it, and close the form (save it). Then
open it again, and then recreate that control (bind it to the Updated
field). See if that works.

 

[Robin Chapple]

I know it is not the way I would design it now. Too many redundant
fields.

Here it is:

SELECT tblRYLA.ID, tblRYLA.Year, tblRYLA.FirstName, tblRYLA.BadgeName,
tblRYLA.Surname, tblRYLA.Street, tblRYLA.Suburb, tblRYLA.PostCode,
tblRYLA.DOB, tblRYLA.Occupation, tblRYLA.PhoneAH, tblRYLA.PhoneBH,
tblRYLA.Mobile, tblRYLA.Comments, tblRYLA.RYLArian, tblRYLA.Sponsor,
tblRYLA.SponsorOLD, tblRYLA.FirstEntered, tblRYLA.Updated,
tblRYLA.Male, tblRYLA.Married, tblRYLA.Email, tblRYLA.Fax,
tblRYLA.Employer, tblRYLA.Interviewed, tblRYLA.AGID, tblRYLA.FeePaid,
tblRYLA.DatePaid, tblRYLA.SuggestedBy, tblRYLA.Medical,
tblRYLA.Position, tblRYLA.Organisation, tblRYLA.Title,
tblRYLA.RosterGroup, tblRYLA.Notes, tblRYLA.ThisYear,
tblRYLA.RYLASupporter, tblRYLA.SupporterOnSite,
tblRYLA.RYLA_OthersInterested, tblRYLA.RYLAYear, tblRYLA.Doctor,
tblRYLA.Emergency, tblRYLA.Fee, tblRYLA.Drawer, tblRYLA.Bank,
tblRYLA.Branch, tblRYLA.Print, tblRYLA.BusStop,
tblRYLA.AcceptLetterSent, tblRYLA.AcceptDate, tblRYLA.PhoneConfirm,
tblRYLA.PhoneConfirmDate, tblRYLA.RepLetterConfirm,
tblRYLA.RepLetterDate, tblRYLA.DatabaseChecked,
tblRYLA.DateDatabaseChecked, tblRYLA.Room, tblRYLA.Leader,
tblRYLA.ARYLA, tblRYLA.FridayNight, tblRYLA.FirstAid, tblRYLA.RTS,
tblRYLA.ClubName2, tblRYLA.ClubName
FROM tblRYLA
WHERE (((tblRYLA.Year) Like [Enter the year])) OR ((([Enter the year])
Is Null));

Thanks,

Robin

 

[Ken Snell [MVP]]

I don't see anything obvious in the posts. If you want to zip up a sample of
the database that is showing this error and email it to me, I'll take a look
as time permits.

Be sure to tell me which form and how to make the error occur.

You can get my email address by removing the words this is not real from my
reply email address.

--

Ken Snell
<MS ACCESS MVP>

I know it is not the way I would design it now. Too many redundant
fields.

Here it is:

SELECT tblRYLA.ID, tblRYLA.Year, tblRYLA.FirstName, tblRYLA.BadgeName,
tblRYLA.Surname, tblRYLA.Street, tblRYLA.Suburb, tblRYLA.PostCode,
tblRYLA.DOB, tblRYLA.Occupation, tblRYLA.PhoneAH, tblRYLA.PhoneBH,
tblRYLA.Mobile, tblRYLA.Comments, tblRYLA.RYLArian, tblRYLA.Sponsor,
tblRYLA.SponsorOLD, tblRYLA.FirstEntered, tblRYLA.Updated,
tblRYLA.Male, tblRYLA.Married, tblRYLA.Email, tblRYLA.Fax,
tblRYLA.Employer, tblRYLA.Interviewed, tblRYLA.AGID, tblRYLA.FeePaid,
tblRYLA.DatePaid, tblRYLA.SuggestedBy, tblRYLA.Medical,
tblRYLA.Position, tblRYLA.Organisation, tblRYLA.Title,
tblRYLA.RosterGroup, tblRYLA.Notes, tblRYLA.ThisYear,
tblRYLA.RYLASupporter, tblRYLA.SupporterOnSite,
tblRYLA.RYLA_OthersInterested, tblRYLA.RYLAYear, tblRYLA.Doctor,
tblRYLA.Emergency, tblRYLA.Fee, tblRYLA.Drawer, tblRYLA.Bank,
tblRYLA.Branch, tblRYLA.Print, tblRYLA.BusStop,
tblRYLA.AcceptLetterSent, tblRYLA.AcceptDate, tblRYLA.PhoneConfirm,
tblRYLA.PhoneConfirmDate, tblRYLA.RepLetterConfirm,
tblRYLA.RepLetterDate, tblRYLA.DatabaseChecked,
tblRYLA.DateDatabaseChecked, tblRYLA.Room, tblRYLA.Leader,
tblRYLA.ARYLA, tblRYLA.FridayNight, tblRYLA.FirstAid, tblRYLA.RTS,
tblRYLA.ClubName2, tblRYLA.ClubName
FROM tblRYLA
WHERE (((tblRYLA.Year) Like [Enter the year])) OR ((([Enter the year])
Is Null));

Thanks,

Robin

Yes, you did what I suggested.

Post the SQL statement of the form's recordsource.

 

[Robin Chapple]

I have sent a stripped down version that I have tested for the
symptoms.

There is only one form. Just try to add or change a suburb.

Many thanks,

Robin Chapple

 

[Ken Snell [MVP]]

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.