Searching Tables and setting data values

[Trigger]

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).

TLR - 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.

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.

 

[Uncle Gizmo]

If you have 4 tables all containing information about devices, then you should really consider changing your database structure.

 

[Trigger]

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

 

[sneuberg]

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.

 

[Trigger]

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.

 

[sneuberg]

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.

 

[Cronk]

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.

 

[sneuberg]

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.