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.FindFirs t" statement, with a error Compile Error - Methode or
Data Member not found.

Any help is greatly appreciated.

Private Sub Part_Number_Sel _cbo_AfterUpdat e()
Dim myset As Recordset
Dim strSearchDiviso n As String
Dim strSearchCustom er As String
Dim strSearchPart As String
Set myset = Me.RecordsetClo ne
strSearchDivsio n = Me!Divison_Sel_ cbo.Value
strSearchCustom er = Me!Customer_Sel _cbo.Value
strSearchPart = Me!Part_Number_ Sel_cbo.Value
myset.FindFirst "[Divison] = '" & strSearchDiviso n & "' and
[Customer] = '" & strSearchCustom er & "' and [Part Number] = '" &
strSearchPart & "'"
Me.Bookmark = myset.Bookmark


Thank you,

Darren

[Darryl Kerkeslager]

Re: Find Record mataching 3 criteria Fields

"ddecoste" <ddecost2@yahoo .com> wrote[color=blue]
> 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.FindFirs t" statement, with a error Compile Error - Methode or
> Data Member not found.
>
> Any help is greatly appreciated.
>
> Private Sub Part_Number_Sel _cbo_AfterUpdat e()
> Dim myset As Recordset
> Dim strSearchDiviso n As String
> Dim strSearchCustom er As String
> Dim strSearchPart As String
> Set myset = Me.RecordsetClo ne
> strSearchDivsio n = Me!Divison_Sel_ cbo.Value
> strSearchCustom er = Me!Customer_Sel _cbo.Value
> strSearchPart = Me!Part_Number_ Sel_cbo.Value
> myset.FindFirst "[Divison] = '" & strSearchDiviso n & "' and
> [Customer] = '" & strSearchCustom er & "' and [Part Number] = '" &
> strSearchPart & "'"
> Me.Bookmark = myset.Bookmark[/color]

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]

Re: Find Record mataching 3 criteria Fields

ddecoste wrote:
[color=blue]
> 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.FindFirs t" statement, with a error Compile Error - Methode or
> Data Member not found.
>
> Any help is greatly appreciated.
>
> Private Sub Part_Number_Sel _cbo_AfterUpdat e()
> Dim myset As Recordset
> Dim strSearchDiviso n As String
> Dim strSearchCustom er As String
> Dim strSearchPart As String
> Set myset = Me.RecordsetClo ne
> strSearchDivsio n = Me!Divison_Sel_ cbo.Value
> strSearchCustom er = Me!Customer_Sel _cbo.Value
> strSearchPart = Me!Part_Number_ Sel_cbo.Value
> myset.FindFirst "[Divison] = '" & strSearchDiviso n & "' and
> [Customer] = '" & strSearchCustom er & "' and [Part Number] = '" &
> strSearchPart & "'"
> Me.Bookmark = myset.Bookmark
>
>
> Thank you,
>
> Darren
>[/color]

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
strSearchDivsio n = Me!Divison_Sel_ cbo.Value
and then run the form and step thru your code.

[ddecoste]

Re: Find Record mataching 3 criteria Fields

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]

Re: Find Record mataching 3 criteria Fields

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]

Re: Find Record mataching 3 criteria Fields

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