473,394 Members | 1,759 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

How to search two Access tables for a specific thing and return info to a form?

I have 2 identical tables in an employee information database, one is called "active" the other "inactive". The field types in both are identical. When an employee leaves/resigns/retires etc I move their entire record line to the inactive table via a delete & append query. This works fine.

We track our employees by thier shield number. I want to setup a form to track disciplinary action. When you enter the members shield # in a field, his last & first name & Shield # & assigned station populate on the form. In addition, I would like certain items from thier disciplinary history to drop into a subform embeded in the main form.

Is there a way to have access search the active table first and then, if the employee is not found there, search the inactive table for the same employee and populate the form with that info?

How do I do it?
Nov 2 '10 #1
1 1336
TheSmileyCoder
2,322 Expert Mod 2GB
If you base the subform of a UNION query of the two tables, and then bind the subform to the mainform through the Shield #, you should get what your looking for.
Nov 2 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
1
by: Koen | last post by:
Hi all, I have built different databases for managing data I keep for my digital collections of mp3's, e-book's, divx movies and digital photographs. Until now, all these solutions lack one...
46
by: Adam Turner via AccessMonster.com | last post by:
If I had a field called "Name" in an Access table "Contact Info", and the field contained VBScript... Function Main(rstFields) Main = rstFields.Item("FirstName").Value End Function 1. How do...
1
by: David | last post by:
Hello, I use the VS .NET 2003. Say I have a Windows form (with C# code, of course) which is connected to a database to one table. I can browse through the records, but I don't know how to...
1
by: asavu | last post by:
Hello, I'm somewhat new to this, and I definately need some help. I have a db for a small business, and I have a main form, Customer Info, which has a subform Appliance Info, which has a subform...
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
3
by: SM | last post by:
Hello, Im trying to access elements in my XML file using the JavaScript DOM but i'm not sure how. I use AJAX to access the XML and then use the responseXML property to access the XML file data. I...
3
by: mckbill | last post by:
Is there a way I can direct the cursor to a specific field (variable) in a form by typing the field name while in form view? I have a form with many fields, and it would be nice if there were...
13
by: BrokenMachine | last post by:
Hi there, I'm using Access 2003 and Word 2003 and I'm trying to import data from the word form to multiple access tables in a one to many relationship, but I can't seem to figure it out. I have...
5
by: AiVrea | last post by:
Hi, I have 2 access tables (table1, table2), table2 is related to the field "Nr Crt"(which is an AutoNumber) in table1 in a "one-to-many" relation. Table1 is connected to the vb form to textboxes,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
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,...

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.