error 3251

[Guest]

I have code for a FindFirst method that works. Then I delete relationships to
the table that holds the data I am looking for to make a change, for example,
to change the data type of a field, recreate the relationships, and use the
same findfirst code I get the 'error 3251 operation not supported for this
type of object'. I have tried recreating the table and the form and still get
the error. Can someone give me a clue as to why this would happen? I also
tried deleting the indexes to the table.

Thanks very much,
Ann

 

[Wayne Morgan]

If you changed the data type of the field you're searching on, did you
change the syntax of the FindFirst to match the new data type? For example,
if the field is now text, you'll need to enclose the value in quotes.

 

[Guest]

This is the code I have:
1. the Combo data is text and the VENDORID is text

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

End Sub
2. the combo data is text and the COMM_CODE is text

Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[COMM_CODE] = '" & Me![Combo16] &
"'"
End Sub
3. the combo data is text and the FAIL_CODE is number

Private Sub Combo44_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] &
"'"
End Sub

 

[Wayne Morgan]

I see two potential problems.

1) In #3 you say that the combo data is text but the Fail_Code is Numeric.
You have a potential data type mismatch here. You are using the Value of the
combo box which comes from the Bound Column. Do you need to pull from a
different column in the combo box?

2) You are searching on Me.RecordsetClone. This won't move the form's
recordset. It will move the Clone recordset (a copy of the form's recordset)
that you created just for this search. However, since you haven't assigned
the clone to an object variable, as soon as the search is done the clone is
released. If you are wanting the form to move to the found record, try
Me.Recordset (i.e. leave off the clone). If the item isn't found, the forms
recordset shouldn't move. If it is found, the form will move to that record.

The clone can be used to do the search in the background then, if the item
is found, assign the bookmark of the clone to the bookmark of the form's
recordset to move the form to the same record. To do that, you would create
an object variable to hold the clone until you're done.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst <criteria>
If Not rst.NoMatch Then
Me.Recordset.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

 

[Guest]

I have a Combo Box that searches for the applicant name called VENDOR_NAME in
the applicant table. When the name is selected, I want the text box called
VENDORID to fill in with the applicants ID from the applicant table. I want
to do the same thing with combo box 2 and 3 but with different fields from
different tables. Would it matter if I use text data in a combo box to find
and populate a number data text box? I can delete the word Clone from my code
but it doesn't help with the error. When the form is complete with all the
info I want, I have a command button that has an addnew and update command to
enter the info into a table called INVENTORY_T. I had this code working
before but then I made a change to COMM_CODE. I changed it from a number data
type to a text data type because the comm_code always starts with 0. I did
not make any other changes but now the code does not work for any of the
combo box/text box fidfirst combinations. I did try to recreate the tables
and the form because I thought maybe there was a reference to the data that
did not match up (an index maybe). I appreciate your help. I am trying to
learn this by trial and error.

I see two potential problems.

1) In #3 you say that the combo data is text but the Fail_Code is Numeric.
You have a potential data type mismatch here. You are using the Value of the
combo box which comes from the Bound Column. Do you need to pull from a
different column in the combo box?

2) You are searching on Me.RecordsetClone. This won't move the form's
recordset. It will move the Clone recordset (a copy of the form's recordset)
that you created just for this search. However, since you haven't assigned
the clone to an object variable, as soon as the search is done the clone is
released. If you are wanting the form to move to the found record, try
Me.Recordset (i.e. leave off the clone). If the item isn't found, the forms
recordset shouldn't move. If it is found, the form will move to that record.

The clone can be used to do the search in the background then, if the item
is found, assign the bookmark of the clone to the bookmark of the form's
recordset to move the form to the same record. To do that, you would create
an object variable to hold the clone until you're done.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst <criteria>
If Not rst.NoMatch Then
Me.Recordset.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

--
Wayne Morgan
MS Access MVP

This is the code I have:
1. the Combo data is text and the VENDORID is text

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] &
"'"

End Sub
2. the combo data is text and the COMM_CODE is text

Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[COMM_CODE] = '" & Me![Combo16]
&
"'"
End Sub
3. the combo data is text and the FAIL_CODE is number

Private Sub Combo44_AfterUpdate()
' Find the record that matches the control.'
Me.RecordsetClone.FindFirst = "[FAIL_CODE] = '" & Me![Combo44]
&
"'"
End Sub

 

[Wayne Morgan]

Ok.

To get the Id field to show in a textbox on the form when you select the
Vendor_Name in the combo box the easiest way would be to include both fields
in the combo box's Row Source. Make the textbox a calculated control by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the column
that the Id field is in. The number is zero based, so the first column is 0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID and
Vendor_Name in the Row Source of the combo box, set the column with the ID
to zero width (Format tab) and set the Bound Column to the ID column (this
number is 1 based, not 0, so the first column is 1). The Bound Column will
become the ".Value" of the combo box and would be the value stored in the
table field listed as the combo box's Control Source. If this is the field
that you have changed the data type for, then you also need to change the
data type for this field in the form's Record Source (table) also since you
will now be storing a new data type in the form's table. Basically, the data
type in the table fields that match the combo box's Bound Column and the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the form's
table, just the ID is sufficient. To link the two together later, you would
create a query with both the form's table and the vendor table with the two
tables linked on this ID field. You would then select the fields you want
from each table, getting the Vendor_Name from the vendor table.

 

[Guest]

I have all that set up.

Ok.

To get the Id field to show in a textbox on the form when you select the
Vendor_Name in the combo box the easiest way would be to include both fields
in the combo box's Row Source. Make the textbox a calculated control by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the column
that the Id field is in. The number is zero based, so the first column is 0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID and
Vendor_Name in the Row Source of the combo box, set the column with the ID
to zero width (Format tab) and set the Bound Column to the ID column (this
number is 1 based, not 0, so the first column is 1). The Bound Column will
become the ".Value" of the combo box and would be the value stored in the
table field listed as the combo box's Control Source. If this is the field
that you have changed the data type for, then you also need to change the
data type for this field in the form's Record Source (table) also since you
will now be storing a new data type in the form's table. Basically, the data
type in the table fields that match the combo box's Bound Column and the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the form's
table, just the ID is sufficient. To link the two together later, you would
create a query with both the form's table and the vendor table with the two
tables linked on this ID field. You would then select the fields you want
from each table, getting the Vendor_Name from the vendor table.

 

[Wayne Morgan]

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

 

[Guest]

these are the lines of code that generate the error

Me.recordset.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst = "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] & "'"

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

--
Wayne Morgan
MS Access MVP

I have all that set up.

 

[Wayne Morgan]

Ok, did you try the syntax change mentioned? These lines should read:

Me.recordset.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst "[FAIL_CODE] = '" & Me![Combo44] & "'"

Note the removal of the first "=".

--
Wayne Morgan
MS Access MVP

these are the lines of code that generate the error

Me.recordset.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst = "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] & "'"

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

--
Wayne Morgan
MS Access MVP

I have all that set up.

:

Ok.

To get the Id field to show in a textbox on the form when you select
the
Vendor_Name in the combo box the easiest way would be to include both
fields
in the combo box's Row Source. Make the textbox a calculated control
by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the
column
that the Id field is in. The number is zero based, so the first column
is
0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID and
Vendor_Name in the Row Source of the combo box, set the column with
the
ID
to zero width (Format tab) and set the Bound Column to the ID column
(this
number is 1 based, not 0, so the first column is 1). The Bound Column
will
become the ".Value" of the combo box and would be the value stored in
the
table field listed as the combo box's Control Source. If this is the
field
that you have changed the data type for, then you also need to change
the
data type for this field in the form's Record Source (table) also
since
you
will now be storing a new data type in the form's table. Basically,
the
data
type in the table fields that match the combo box's Bound Column and
the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the form's
table, just the ID is sufficient. To link the two together later, you
would
create a query with both the form's table and the vendor table with
the
two
tables linked on this ID field. You would then select the fields you
want
from each table, getting the Vendor_Name from the vendor table.

--
Wayne Morgan
Microsoft Access MVP


I have a Combo Box that searches for the applicant name called
VENDOR_NAME
in
the applicant table. When the name is selected, I want the text box
called
VENDORID to fill in with the applicants ID from the applicant table.
I
want
to do the same thing with combo box 2 and 3 but with different
fields
from
different tables. Would it matter if I use text data in a combo box
to
find
and populate a number data text box? I can delete the word Clone
from
my
code
but it doesn't help with the error. When the form is complete with
all
the
info I want, I have a command button that has an addnew and update
command
to
enter the info into a table called INVENTORY_T. I had this code
working
before but then I made a change to COMM_CODE. I changed it from a
number
data
type to a text data type because the comm_code always starts with 0.
I
did
not make any other changes but now the code does not work for any of
the
combo box/text box fidfirst combinations. I did try to recreate the
tables
and the form because I thought maybe there was a reference to the
data
that
did not match up (an index maybe). I appreciate your help. I am
trying
to
learn this by trial and error.

 

[Guest]

Yes, I tried that and it did not help.

Ok, did you try the syntax change mentioned? These lines should read:

Me.recordset.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst "[FAIL_CODE] = '" & Me![Combo44] & "'"

Note the removal of the first "=".

--
Wayne Morgan
MS Access MVP

these are the lines of code that generate the error

Me.recordset.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst = "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] & "'"

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

--
Wayne Morgan
MS Access MVP


I have all that set up.

:

Ok.

To get the Id field to show in a textbox on the form when you select
the
Vendor_Name in the combo box the easiest way would be to include both
fields
in the combo box's Row Source. Make the textbox a calculated control
by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the
column
that the Id field is in. The number is zero based, so the first column
is
0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID and
Vendor_Name in the Row Source of the combo box, set the column with
the
ID
to zero width (Format tab) and set the Bound Column to the ID column
(this
number is 1 based, not 0, so the first column is 1). The Bound Column
will
become the ".Value" of the combo box and would be the value stored in
the
table field listed as the combo box's Control Source. If this is the
field
that you have changed the data type for, then you also need to change
the
data type for this field in the form's Record Source (table) also
since
you
will now be storing a new data type in the form's table. Basically,
the
data
type in the table fields that match the combo box's Bound Column and
the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the form's
table, just the ID is sufficient. To link the two together later, you
would
create a query with both the form's table and the vendor table with
the
two
tables linked on this ID field. You would then select the fields you
want
from each table, getting the Vendor_Name from the vendor table.

--
Wayne Morgan
Microsoft Access MVP


I have a Combo Box that searches for the applicant name called
VENDOR_NAME
in
the applicant table. When the name is selected, I want the text box
called
VENDORID to fill in with the applicants ID from the applicant table.
I
want
to do the same thing with combo box 2 and 3 but with different
fields
from
different tables. Would it matter if I use text data in a combo box
to
find
and populate a number data text box? I can delete the word Clone
from
my
code
but it doesn't help with the error. When the form is complete with
all
the
info I want, I have a command button that has an addnew and update
command
to
enter the info into a table called INVENTORY_T. I had this code
working
before but then I made a change to COMM_CODE. I changed it from a
number
data
type to a text data type because the comm_code always starts with 0.
I
did
not make any other changes but now the code does not work for any of
the
combo box/text box fidfirst combinations. I did try to recreate the
tables
and the form because I thought maybe there was a reference to the
data
that
did not match up (an index maybe). I appreciate your help. I am
trying
to
learn this by trial and error.

 

[Wayne Morgan]

Ann,

Please zip the database and send it to me. Include a message telling me
where to find the code and the combo boxes. Send the file to
(e-mail address removed).

--
Wayne Morgan
Microsoft Access MVP

Yes, I tried that and it did not help.

Ok, did you try the syntax change mentioned? These lines should read:

Me.recordset.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst "[FAIL_CODE] = '" & Me![Combo44] & "'"

Note the removal of the first "=".

--
Wayne Morgan
MS Access MVP

these are the lines of code that generate the error

Me.recordset.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst = "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] & "'"

:

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a
syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

--
Wayne Morgan
MS Access MVP


I have all that set up.

:

Ok.

To get the Id field to show in a textbox on the form when you
select
the
Vendor_Name in the combo box the easiest way would be to include
both
fields
in the combo box's Row Source. Make the textbox a calculated
control
by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the
column
that the Id field is in. The number is zero based, so the first
column
is
0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID
and
Vendor_Name in the Row Source of the combo box, set the column with
the
ID
to zero width (Format tab) and set the Bound Column to the ID
column
(this
number is 1 based, not 0, so the first column is 1). The Bound
Column
will
become the ".Value" of the combo box and would be the value stored
in
the
table field listed as the combo box's Control Source. If this is
the
field
that you have changed the data type for, then you also need to
change
the
data type for this field in the form's Record Source (table) also
since
you
will now be storing a new data type in the form's table. Basically,
the
data
type in the table fields that match the combo box's Bound Column
and
the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the
form's
table, just the ID is sufficient. To link the two together later,
you
would
create a query with both the form's table and the vendor table with
the
two
tables linked on this ID field. You would then select the fields
you
want
from each table, getting the Vendor_Name from the vendor table.

--
Wayne Morgan
Microsoft Access MVP


I have a Combo Box that searches for the applicant name called
VENDOR_NAME
in
the applicant table. When the name is selected, I want the text
box
called
VENDORID to fill in with the applicants ID from the applicant
table.
I
want
to do the same thing with combo box 2 and 3 but with different
fields
from
different tables. Would it matter if I use text data in a combo
box
to
find
and populate a number data text box? I can delete the word Clone
from
my
code
but it doesn't help with the error. When the form is complete
with
all
the
info I want, I have a command button that has an addnew and
update
command
to
enter the info into a table called INVENTORY_T. I had this code
working
before but then I made a change to COMM_CODE. I changed it from a
number
data
type to a text data type because the comm_code always starts with
0.
I
did
not make any other changes but now the code does not work for any
of
the
combo box/text box fidfirst combinations. I did try to recreate
the
tables
and the form because I thought maybe there was a reference to the
data
that
did not match up (an index maybe). I appreciate your help. I am
trying
to
learn this by trial and error.

 

[Wayne Morgan]

Ann,

I found several errors (typos) in control names used in code. I added Option
Explicit to the top of the code to catch any undeclared variables in that
module. You also need to do this for the rest of the modules. The code
wouldn't even compile due to the typos. Once these were solved, I tried
running the code and never did get your 3251 error.

To automatically add Option Explicit to any new modules, in the code window
go to Tools|Options|Editor Tab and check the box "Require Variable
Declaration". This will automatically add it to any new modules, but you'll
need to manually add it to any current modules.

--
Wayne Morgan
MS Access MVP

Yes, I tried that and it did not help.

Ok, did you try the syntax change mentioned? These lines should read:

Me.recordset.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst "[FAIL_CODE] = '" & Me![Combo44] & "'"

Note the removal of the first "=".

--
Wayne Morgan
MS Access MVP

these are the lines of code that generate the error

Me.recordset.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"

Me.recordset.FindFirst = "[COMM_CODE] = '" & Me![Combo16] & "'"

Me.recordset.FindFirst = "[FAIL_CODE] = '" & Me![Combo44] & "'"

:

Which line of code generates the error?

I also went back and looked at the code you supplied. There is a
syntax
error in the FindFirst lines.

Me.RecordsetClone.FindFirst = "[VENDORID] = '" & Me![Combo0] & "'"<<

Should be

Me.RecordsetClone.FindFirst "[VENDORID] = '" & Me![Combo0] & "'"

--
Wayne Morgan
MS Access MVP


I have all that set up.

:

Ok.

To get the Id field to show in a textbox on the form when you
select
the
Vendor_Name in the combo box the easiest way would be to include
both
fields
in the combo box's Row Source. Make the textbox a calculated
control
by
placing the equation

=cboMyCombo.Column(0)

in the Control Source for the textbox. The 0 is the index for the
column
that the Id field is in. The number is zero based, so the first
column
is
0,
the second is 1, etc.

A common way to set up combo boxes would be to return both the ID
and
Vendor_Name in the Row Source of the combo box, set the column with
the
ID
to zero width (Format tab) and set the Bound Column to the ID
column
(this
number is 1 based, not 0, so the first column is 1). The Bound
Column
will
become the ".Value" of the combo box and would be the value stored
in
the
table field listed as the combo box's Control Source. If this is
the
field
that you have changed the data type for, then you also need to
change
the
data type for this field in the form's Record Source (table) also
since
you
will now be storing a new data type in the form's table. Basically,
the
data
type in the table fields that match the combo box's Bound Column
and
the
combo box's Control Source have to be the same data type.

It is not necessary to store both the ID and Vendor_Name in the
form's
table, just the ID is sufficient. To link the two together later,
you
would
create a query with both the form's table and the vendor table with
the
two
tables linked on this ID field. You would then select the fields
you
want
from each table, getting the Vendor_Name from the vendor table.

--
Wayne Morgan
Microsoft Access MVP


I have a Combo Box that searches for the applicant name called
VENDOR_NAME
in
the applicant table. When the name is selected, I want the text
box
called
VENDORID to fill in with the applicants ID from the applicant
table.
I
want
to do the same thing with combo box 2 and 3 but with different
fields
from
different tables. Would it matter if I use text data in a combo
box
to
find
and populate a number data text box? I can delete the word Clone
from
my
code
but it doesn't help with the error. When the form is complete
with
all
the
info I want, I have a command button that has an addnew and
update
command
to
enter the info into a table called INVENTORY_T. I had this code
working
before but then I made a change to COMM_CODE. I changed it from a
number
data
type to a text data type because the comm_code always starts with
0.
I
did
not make any other changes but now the code does not work for any
of
the
combo box/text box fidfirst combinations. I did try to recreate
the
tables
and the form because I thought maybe there was a reference to the
data
that
did not match up (an index maybe). I appreciate your help. I am
trying
to
learn this by trial and error.