Find Record mataching 3 criteria Fields

[ddecoste]

I am trying to create a form to make it easier to modify a record
without having to page through all the records. I have started the
form with 3 cascading combo boxes. ie #1 asks for division , #2 is
populated with just the customers in the division specified by #1 and
then #3 is just the Parts for the customer in #2. This part works
great. I now need to set the code in the after update of the #3 combo
box to pull up the record that goes with this data by using the choices
of the 3 combo boxes.

Here is the code that I have but it is bombing out on the
"myset.FindFirst" statement, with a error Compile Error - Methode or
Data Member not found.

Any help is greatly appreciated.

Private Sub Part_Number_Sel_cbo_AfterUpdate()
Dim myset As Recordset
Dim strSearchDivison As String
Dim strSearchCustomer As String
Dim strSearchPart As String
Set myset = Me.RecordsetClone
strSearchDivsion = Me!Divison_Sel_cbo.Value
strSearchCustomer = Me!Customer_Sel_cbo.Value
strSearchPart = Me!Part_Number_Sel_cbo.Value
myset.FindFirst "[Divison] = '" & strSearchDivison & "' and
[Customer] = '" & strSearchCustomer & "' and [Part Number] = '" &
strSearchPart & "'"
Me.Bookmark = myset.Bookmark
Thank you,

Darren

[Darryl Kerkeslager]

"ddecoste" <dd******@yahoo.com> wrote

I am trying to create a form to make it easier to modify a record
without having to page through all the records. I have started the
form with 3 cascading combo boxes. ie #1 asks for division , #2 is
populated with just the customers in the division specified by #1 and
then #3 is just the Parts for the customer in #2. This part works
great. I now need to set the code in the after update of the #3 combo
box to pull up the record that goes with this data by using the choices
of the 3 combo boxes.

Here is the code that I have but it is bombing out on the
"myset.FindFirst" statement, with a error Compile Error - Methode or
Data Member not found.

Any help is greatly appreciated.

Private Sub Part_Number_Sel_cbo_AfterUpdate()
Dim myset As Recordset
Dim strSearchDivison As String
Dim strSearchCustomer As String
Dim strSearchPart As String
Set myset = Me.RecordsetClone
strSearchDivsion = Me!Divison_Sel_cbo.Value
strSearchCustomer = Me!Customer_Sel_cbo.Value
strSearchPart = Me!Part_Number_Sel_cbo.Value
myset.FindFirst "[Divison] = '" & strSearchDivison & "' and
[Customer] = '" & strSearchCustomer & "' and [Part Number] = '" &
strSearchPart & "'"
Me.Bookmark = myset.Bookmark

Is either [Divison] or [Part Number] numeric? You have them in single
quotes. If the query is looking for a numeric value, do away with the
single quotes.

Is this ADO code? Because ADO does not have a FindFirst method. Is it code
in an Access 2000+ mdb - because it defaults to ADO. Try changing this
line:

Dim myset As Recordset

to

Dim myset As DAO.Recordset
Darryl Kerkeslager

[Salad]

ddecoste wrote:

I am trying to create a form to make it easier to modify a record
without having to page through all the records. I have started the
form with 3 cascading combo boxes. ie #1 asks for division , #2 is
populated with just the customers in the division specified by #1 and
then #3 is just the Parts for the customer in #2. This part works
great. I now need to set the code in the after update of the #3 combo
box to pull up the record that goes with this data by using the choices
of the 3 combo boxes.

Here is the code that I have but it is bombing out on the
"myset.FindFirst" statement, with a error Compile Error - Methode or
Data Member not found.

Any help is greatly appreciated.

Private Sub Part_Number_Sel_cbo_AfterUpdate()
Dim myset As Recordset
Dim strSearchDivison As String
Dim strSearchCustomer As String
Dim strSearchPart As String
Set myset = Me.RecordsetClone
strSearchDivsion = Me!Divison_Sel_cbo.Value
strSearchCustomer = Me!Customer_Sel_cbo.Value
strSearchPart = Me!Part_Number_Sel_cbo.Value
myset.FindFirst "[Divison] = '" & strSearchDivison & "' and
[Customer] = '" & strSearchCustomer & "' and [Part Number] = '" &
strSearchPart & "'"
Me.Bookmark = myset.Bookmark
Thank you,

Darren

I get that message when I search on a field and the name of the field on
the form is incorrect. Ex:
rst.FindFirst "ID= '" & Me.ID1 & "'"
and there is no ID1.

If I did
rst.FindFirst "ID1 = '" & Me.ID & "'"
and ID1 did not exist in the recordset, but ID did, I'd get a message
that the object was not found.

WHere is it blowing up? On the findfirst line or one of the lines your
are assigning values?

I could click the vertical bar to the left on
strSearchDivsion = Me!Divison_Sel_cbo.Value
and then run the form and step thru your code.

[ddecoste]

Thank you for the input.

All three of the of the fields that I am using (Division, Part Number
and Customer) in the query are strings.

This is being done in Access 2002. I did try to change the declaration
of the recordset from:

Dim myset as Recordset
to
Dim myset as DAO.Recordset

When I then try the code it bottoms out on the changed line of
Dim myset as DAO.Recordset. With an error of Compile Error:
User-defined type not defined.

Any other ideas? or is there an easier way of doing this??
Thank you for the help.

[ddecoste]

I double checked and the field names are correct, these are just some
unbound combo boxes doing a cascading lookup on the database.

The place that it bottoms out is the .FindFirst statement during the
compile. It does not even make it to the running of the code..

Any other suggestions.

Thank you for all your help also. If an easier way is possible please
let me know.

Thank you,

[ddecoste]

Thank you Darryl and Salad for your help.

After doing a bunch more research and looking at what you told me I
did get it to work.

1st: I am a horrible speller and I had spelled my field name wrong in
the database and all the references to it. Salad, you hit that one ( I
dont know how to spell division) Damm computer wants the correct
spelling what gives:)
2nd. Under Tools, references in Access I did not have the DAO
selected. After searching on the error codes that I received after
changing the declaration, I found the article on changing the
references to include the DAO.

Thank you both for your help.

Darren