Searching for a record in multiple tables

[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

[Uncle Gizmo]

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.

[elainenguyen]

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

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

[Inanna]

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.

[elainenguyen]

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

[Inanna]

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.

[elainenguyen]

Thanks for your help anyway!
Elaine