473,574 Members | 2,360 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Find Record mataching 3 criteria Fields

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

Nov 13 '05 #1
5 5045
"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.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


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


Nov 13 '05 #2
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.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


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.

Nov 13 '05 #3
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.

Nov 13 '05 #4
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,

Nov 13 '05 #5
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

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2240
by: John | last post by:
Hi, I have simple database based on 3 tables. Relationship is 'one to many' between table 1 and 2, also between 2 and 3 table'one to many'. I have made form where I enter data in all 3 tables. Form has 2 subform based on relationships. I want to make report to print data which is shown on form. I want to print only data from form, not...
3
4594
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: tblItems ItemID ItemLabel
2
1304
by: Neil Ginsberg | last post by:
I have a client using an Access 2002 front end and back end. A certain record wasn't showing up in several reports. He sent me the back end (I have the same front on my machine), and I ran the reports using Access 2000, and the record showed up. I then notified him, he tried again in Access 2002, and the record still didn't show up. He then...
8
5472
by: jquest | last post by:
Hi Again; I have had help from this group before and want to thank everyone, especially PCDatasheet. My database includes a field called HomePhone, it uses the (xxx)xxx-xxx format to include area code. When a customer calls, I currently use Ctrl F with the HomePhone field highlighted. Then I enter the last 4 digits and use the find next...
4
4408
by: kufre | last post by:
How can I use three criteria to find a record? I've done this before where I only use one criteria to find a record and set the focus to that criteria only. Thanks in advance.
3
1587
by: kaosyeti | last post by:
i need to do a record search in a form but there are a few twists. 1. there is no unique field to any of these records 2. the form has 9 fields, 5 of which are required for creating a record. 3. the solution has to be super easy for the end-users of this database. i'm thinking of a command button that pops up a form that let's a person...
6
3789
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after you enter data to the first field on the main form (a VIN#), that is not the case. The SubForm control becomes disabled and will not accept the...
1
1899
by: emmaruwa | last post by:
I have a form with two text boxes (in its details section) that pull data from two fields in my database table. This same form also has a button beside the text boxes which is supposed to open another form and populate that form with a record from the table using the data in the previous two text boxes as record selection criteria. However, it...
3
6125
by: igendreau | last post by:
I'm trying to clean up a database of mine, and I need to convert some old DAO code over to ADO. When I was using DAO, I had no problem running this script and deleting the record using rs.Delete. But when I rework the code in ADO, it tells me I can't delete it because there are related records in another table. Never had that problem in DAO...
0
7742
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8089
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8260
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7840
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5638
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3765
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3783
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1362
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1092
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.