473,686 Members | 2,783 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Searching multiple tables in Access using Visual Basic

I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling. OpenRecordset(t ableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina
Nov 13 '05 #1
3 6018
Katrina wrote:
I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling. OpenRecordset(t ableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina


You might try

If (CurrentProject .Connection.Exe cute( _
"SELECT * FROM tbl2002Transact ions " _
& "WHERE fldDescription = """ & Description & """").BOF) Then
MsgBox Description & " not found"
Else
MsgBox Description & " found"
End If

substituting your own variables.

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....
Nov 13 '05 #2

"Katrina" <tr******@hotma il.com> wrote in message
news:8c******** *************** ***@posting.goo gle.com...
I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling. OpenRecordset(t ableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina

The first problem is references. While viewing your code, select
Tools>Reference s and make sure you have a reference set to Microsoft DAO 3.6
Object Library. Remove any references you don't need, such as ActiveX Data
Objects. You could alter the code to:
Dim dbsAddSampling As DAO.Database
Dim rstTable As DAO.Recordset
To make sure it is clear that you are using the DAO object library.

The next comment is that something must be a bit funny about the database
structure if you have to search multiple tables for the same thing.
Normally, you would expect similar information to be stored in a single
table. Perhaps that is out of your control.

The third is that it probably doesn't make sense to open an editable
recordset based on the whole table. It is a bit wasteful since you only
need to find if there is a matching entry or not. This recordset would be
better:
strSQL="SELECT STREETNM FROM MY_TABLE WHERE STREETNM=""" & strStreet & """"
Set rst=dbs.OpenRec ordset(strSQL, dbOpenForwardOn ly)
However, you could use other alternatives or even scrap most of the code and
use a function like DLOOKUP, DFIRST or similar.
Nov 13 '05 #3
Thanks Justin. What you suggested worked!!!

Katrina

Nov 13 '05 #4

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

Similar topics

6
6087
by: BlackFireNova | last post by:
Using Access 2002 I am writing a report which draws data from several different tables. I can't link all the tables in a query, as some can not be related without truncating the data. I plan to use sub-queries and sub-reports to filter and display the data in the unrelated tables in my report. The common information is a user-inputed date range. I want to avoid having the user prompted for the and variables repeatedly. Somehow I...
4
14468
by: anand | last post by:
Hi, I have an Access 2000 database, which contains some native tables, and some linked tables which belong to an ORACLE database, through ODBC. Using VB.NET, I am trying to fetch some data by joining two tables, one table native to the mdb file, and another belonging to ORACLE. I am using a connection string to connect to mdb file:
9
2766
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and wizards. But, I have found that trying to do something "outside the norm" adds a rather large level of complexity and/or data replication. Background I have been commissioned to create a web-based application for a client. It has a formsaunthentication...
33
2494
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following criteria: I have a list of about 100 numbers which correspond to the ID field and also another 40 say numbers corresponding to the numbers in the PRODUCT field. I want to show the rows that correspond to both these criteria.
9
3322
by: cj | last post by:
I'm trying to forge ahead with Visual Basic .Net but recently I've suffered several major set backs in demonstrating VB is the future and we should move from Visual FoxPro. I really need to find help on this is. My current problem is I've got to display some Visual FoxPro data from stand alone tables in a datagrid (Widows App). The main table is 50 meg with 540,000+ records. 1 of the others joined to it is 11 meg and the other 2 are...
0
2570
by: RLN | last post by:
I have a Microsoft Access2002 database that needs to connect to an Oracle Database. I need to map 2 tables from the Oracle DB to retrieve the proper data. I read somewhere (quite a while back) that if you use ADO in Access, you can connect without the need for ODBC, TNSNAMES.ORA and the Oracle client installed on the user's desktop. If I use the Microsoft driver for Oracle, is it possible to connect to an Oracle DB using straight VBA
21
3852
by: Al Christoph | last post by:
I posted this last week end in the MSDN forums. No luck there. Let's see what the experts here have to say:-)))) I have a rather convoluted project. The distributable will come in eight different flavors, each with its main program, libraries, datafiles, doco, etc. Each flavor will also have one or more standalone executables that can be called as tools from within the main program.
7
4536
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent records. Should I design a new form for this or can I somehow make this work in the same form. Thanks in advance, john
6
9418
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate report" button located on my form to print all of the list box values (that have been updated via selection from combo boxes) from the form to the report. I've tried using a macro with the code: Macro Name: cmdGenerateReport : On Click Action:...
0
8516
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9054
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8932
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6440
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4308
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2945
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1934
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.