Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-20-2015, 05:00 PM   #1
Trigger
Newly Registered User
 
Join Date: Oct 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Trigger is on a distinguished road
Lightbulb Searching Tables and setting data values

Hello, I am working on my first database with Access and am new to VBA. Until now I have been able to make most of my items work with Macros. I am now running into a point where I try to make a button do a large amount of work and Macros just wont cut it.

The database I am working on is to keep track of employees and the devices they are assigned (Laptop, Phone, Fax#, and Mifi). When an employee is terminated I need to set the device status from In-Use to Waiting On Return. I created a pseudo code of what I am trying to do and put it below. I need to search the tables and find matching records and edit the data. It is possible that the employee might have more than one of each device assigned (ex: two Laptops).

TL:DR - I need to search 4 tables for matching records and edit data in them. I have created a pseudo code of what i'm trying to do but I don't know VBA enough to implement it properly. I may be able to use queries but unsure how.

Code:
Search LaptopT{ 
	If ([LaptopOwner_FK] = [tmpContactID]){
	Me.[LaptopStatus_FK] = 5
	}
}
Search CellT {
	If ([CellOwner_FK] = [tmpContactID]){
	Me.[CellStatus_FK] = 5
	}
}
Search MifiT {
	If ([MifiOwner_FK] = [tmpContactID]){
	Me.[MifiStatus_FK] = 5
	}
}
Search FaxT {
	If ([FaxOwner_FK] = [tmpContactID]){
	Me.[FaxStatus_FK] = 1
	Me.[FaxOwner_FK] = (Number for Open Fax)
	Me.[FaxADUsername_FK] = (Number for Open Fax Username)
	Me.[FaxDepartment_FK] = 
	Me.[FaxUsername] = 
	Me.[FaxPassword] = 
	CreateNewRecord in DeviceHistoryT (,,,,[tmpContactID], , Date(), [FaxNumber])
	}
}

Thank you to anyone who helps. I greatly appreciate it as I have searched all over with no luck.

Trigger is offline   Reply With Quote
Old 10-20-2015, 05:10 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,068
Thanks: 472
Thanked 893 Times in 848 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
If you have 4 tables all containing information about devices, then you should really consider changing your database structure.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 10-20-2015, 05:13 PM   #3
Trigger
Newly Registered User
 
Join Date: Oct 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Trigger is on a distinguished road
Re: Searching Tables and setting data values

How so? Currently I have one table for each kind of device. A Laptop table, Phone table, etc.

Trigger is offline   Reply With Quote
Old 10-20-2015, 06:05 PM   #4
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Searching Tables and setting data values

Your probably should have table for the employee, a table for the devices and a junction table to connect the two. Things will get a lot easier if you have your data structured correctly.
sneuberg is offline   Reply With Quote
Old 10-20-2015, 06:32 PM   #5
Trigger
Newly Registered User
 
Join Date: Oct 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Trigger is on a distinguished road
Re: Searching Tables and setting data values

Thank you, I can see how that would make things easier. I am far enough where restructuring will be a little painful but not impossible.

So lets now assume that I have a single table to search, DevicesT. How would I search the table, matching the devices that are assigned to the owner, change their status to Waiting on Return(id 5), and then continue the search until the end of the table is reached? Again, thank you all for the help.
Trigger is offline   Reply With Quote
Old 10-20-2015, 07:02 PM   #6
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Searching Tables and setting data values

Once you have these tables and have relations defined between them you would go to the query builder and add the three tables. Access will automatically join the tables if you have the relations defined. If you select the employees information and the devices information you will see what employees have what devices. To narrow this down to what you want you put in criteria. You can create an update query and update a field to whatever you want. General you run these queries from a form where you specify these parameters.
sneuberg is offline   Reply With Quote
Old 10-20-2015, 10:33 PM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,113
Thanks: 3
Thanked 457 Times in 450 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Searching Tables and setting data values

Based on what I can see, I'd disagree and say two tables are required (with perhaps lookup tables in addition.)

I assume each of the equipment items is unique with an asset number.

The 2 tables are tblEmployees and tblEquipment.
tblEmployees
EmployeeID PK
Other employee fields including employment status

tblEquipment
EquipmentID PK
EquipmentTypeID (eg 1 = calculator, 2=laptop etc)
EmployeeID
AssetNumber
AssigmentStatusID (1= Assigned, 2= To be returned...)
More fields as required to record equipment item info (eg cost, date of purchase, supplier, serial number, whatever...)

Then it's just a matter of updating the AssignmentStatusID when the employment status changes.

On the other hand, if a history is to be maintained of all the employees who had held a particular equipment item, then another table would be required.

Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
sneuberg (10-21-2015)
Old 10-21-2015, 01:11 AM   #8
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Searching Tables and setting data values

Oops. Sorry about that. A junction table wouldn't make sense unless people shared equipment. A one to many relationship with two tables does appear to what Trigger would need.


Last edited by sneuberg; 10-21-2015 at 01:12 AM. Reason: revised wording
sneuberg is offline   Reply With Quote
Reply

Tags
queries , search , tables , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
searching tables for similar values confusedaccessuser1 Queries 1 05-08-2012 09:47 AM
searching same field data over 6 tables ImLost Queries 4 03-26-2012 09:21 AM
Searching multiple tables for values and returning a list rblbkl Queries 2 12-20-2011 08:55 AM
Searching two tables for a third table of values tallkris Queries 0 05-15-2009 08:19 AM
Query searching multiple tables' field names (not values) russi General 14 01-25-2007 10:20 AM




All times are GMT -8. The time now is 08:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World