Ad:

VB.NET Discussion Thread
Unsolved
Views: 12515

Nov 20th, 2004

Searching for a record in multiple tables

Expand Post »

Here is my problem:

I have a search form in the project I am currently working on, and I have no idea how to go about making it work. I want it to search and display data for a “Tag ID�? entered by the user. Here is the fun part. I want it to check for the record in 4 different tables (“QCHold�?, “Rework�?, “Recut�?, and “Scrap�?). Now, the record will not be in multiple tables, there is only one instance of it. For example, I want it to search through “QCHold�?, and if it finds it there, display the data and exit the sub. But, if it doesn’t find it, I want it to go on to another table to check. I want it to keep searching until either it finds the record in one of the tables, or until it reaches the end of the last table (if the record doesn’t exist).

Language: VB.Net
Database: MS Access

Possible future problem:

If I get this to work, how would I go about deleting a record from the search? I have tried this before, but haven’t gotten it to work since I didn’t have an index for the record, because I just displayed the data. I would always get a reference error.

I would be grateful to anyone that can help or point me in the right direction, and I could send whoever helps a mix cd or something.

...just a warning, if I don't understand, I'll probably have a bunch of questions since I have no formal training in VB.Net. I've been teaching myself with help files, but I couldn't find anything for this particular task.

Thanks for your time,
Wes

Similar Threads

Help with Select Query from Multiple Tables??? in PHP
SQL Query Help... Searching one column in multiple tables? in MS Access and FileMaker Pro
Searching a record in 3 tables in Visual Basic 4 / 5 / 6

WesDolezal

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2004

Permalink

Nov 29th, 2004

Re: Searching for a record in multiple tables

I'm guessing here, but are all your tables the same layout? Do they all have the same fields storing the same information? If so I would recommend that you combine all the tables into one table, then identify for different sets of information with an identifier Field. then you will only need to search in one table.

Uncle Gizmo

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

16 posts
since Mar 2004

Permalink

Oct 5th, 2006

Re: Searching for a record in multiple tables

HI wes,
I have the same problem as you do. I can do it with one table, but i couldn't make it with 3 tables. I wonder if you have found a way.

Elaine

Click to Expand / Collapse Quote originally posted by WesDolezal ...

Here is my problem:

I have a search form in the project I am currently working on, and I have no idea how to go about making it work. I want it to search and display data for a “Tag ID‿ entered by the user. Here is the fun part. I want it to check for the record in 4 different tables (“QCHold‿, “Rework‿, “Recut‿, and “Scrap‿). Now, the record will not be in multiple tables, there is only one instance of it. For example, I want it to search through “QCHold‿, and if it finds it there, display the data and exit the sub. But, if it doesn’t find it, I want it to go on to another table to check. I want it to keep searching until either it finds the record in one of the tables, or until it reaches the end of the last table (if the record doesn’t exist).

Language: VB.Net
Database: MS Access

Possible future problem:

If I get this to work, how would I go about deleting a record from the search? I have tried this before, but haven’t gotten it to work since I didn’t have an index for the record, because I just displayed the data. I would always get a reference error.

I would be grateful to anyone that can help or point me in the right direction, and I could send whoever helps a mix cd or something.

...just a warning, if I don't understand, I'll probably have a bunch of questions since I have no formal training in VB.Net. I've been teaching myself with help files, but I couldn't find anything for this particular task.

Thanks for your time,
Wes

elainenguyen

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Oct 2006

Permalink

Oct 5th, 2006

Re: Searching for a record in multiple tables

I'm gonna guess that Wes isn't around anymore since he only has one post and this thread is from 2004.

But I can prolly help you. When you say 'table', do you mean a DataSet in VB.NET or tables in your database? If it's in the database then you can use a cross join or inner join to link the tables together and query all of them at once.

Inanna

Reputation Points: 53

Solved Threads: 6

Junior Poster in Training

Offline

90 posts
since Sep 2006

Permalink

Oct 5th, 2006

Re: Searching for a record in multiple tables

Thanks for your response.
I am doing Acess database with VBA.
I have 3 tables (you can look at the code below). The codes works when searching for records that are exist in the tables. But when I try a record that doesn't exist in the table, I din't get the warning message that I want. Not sure what happen, I didn't see anything wrong with the code.
Thanks alot!
Elaine

Elaine Nguyen's code:
Dim db As Database
Dim rst As Recordset
Dim stLinkCriteria As String

'Open form and carry account number over first
stLinkCriteria = "[CVACCTNO]=" & Me![txtAcctNo]
DoCmd.OpenForm "frmCerner", , , stLinkCriteria
[Forms]![frmCerner]![txtPtName] = Me.txtPtName

'Find if record is exit. Find from beginning of recordset to ending of recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCernerMicro", dbOpenDynaset)
rst.FindFirst "[FNBR] = " & Me![CVACCTNO] & ""
If rst.NoMatch Then
Else
Set rst = db.OpenRecordset("tblCernerSuscept", dbOpenDynaset)
rst.FindFirst "[FNBR] = " & Me![CVACCTNO] & ""
If rst.NoMatch Then
Else
Set rst = db.OpenRecordset("tblCernerUA", dbOpenDynaset)
rst.FindFirst "[FNBR] = " & Me![CVACCTNO] & ""
If rst.NoMatch Then
MsgBox "No Cerner record was found under this account number: " & CVACCTNO & "", vbOKOnly, "No Record On File"
DoCmd.Close acForm, "frmCerner", acSaveNo
Else
stLinkCriteria = "[CVACCTNO]=" & Me![txtAcctNo]
DoCmd.OpenForm "frmCerner", , , stLinkCriteria
[Forms]![frmCerner]![txtPtName] = Me.txtPtName
End If
End If
End If

elainenguyen

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Oct 2006

Permalink

Oct 5th, 2006

Re: Searching for a record in multiple tables

Quote ...

I am doing Acess database with VBA.

Oh, I thought you were linking to an Access database with VB.NET. VBA is different. I haven't worked with VBA in an age and a half, so I'm probably not much help. There's an MS Access board on this forum that might be a better place for you to start a new thread with the problem.

Sorry.

Inanna

Reputation Points: 53

Solved Threads: 6

Junior Poster in Training

Offline

90 posts
since Sep 2006

Permalink

Oct 5th, 2006

Re: Searching for a record in multiple tables

Thanks for your help anyway!
Elaine

elainenguyen

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Oct 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Problem using a collection

Next Thread in VB.NET Forum Timeline: Autocad.Net

DaniWeb Message

Cancel Changes

User Name
Password