Two values from one field

[patfree]

I have a computer inventory program in VB6, the front end to an Access DB. The company asset tag number being the main unique identifier. All equipment, computer, monitor, printers, etc, are broken into separate tables. Some computers have multiple monitors assigned. The problem I am having is getting the SQL, which creates the recordset to populate the form, to fetch data for both monitors. The main DB, TblPC, has a foreign key from the monitor table, TblMonitor. The primary monitor is field fMonAsset and the second monitor is fMonAsset2. I suspect my problem is in the FROM section of the SQL but I am not sure how to resolve it. I would greatly appreciate any help and I thank you in advance.

My SQL statement is:

SELECT TblCompany.CoID, TblCompany.Company, TblDepartment.Department, TblDepartment.DepartID, TblNewUsers.UserID, TblNewUsers.UserName, TblNewUsers.fCoID, TblNewUsers.fDepartID, TblPC.PCID, TblPC.PCAsset, TblPC.HD, TblPC.SerialNo, TblPC.CDRW, TblPC.ZIP, TblPC.fUserID, TblPC.fNICID, TblPC.fOfficeID, TblPC.fOSID, TblPC.fModelID, TblPC.Speed, TblPC.Notes, TblPC.fMonAsset, TblPC.OSFactory, TblPC.OSKey, TblPC.OffFactory, TblPC.OffKey, TblPC.NICHWAddress, TblPC.Type, TblPC.Spd, TblPC.fMemID, TblPC.fVidID, TblPC.fSndID, TblPC.fMdmID, TblPC.fMonAsset2, TblPC.fVidID2, TblPC.Monitor2, TblMemory.MemID, TblMemory.Memory, TblModem.MdmID, TblModem.Modem, TblMonitor.MAsset, TblMonitor.MonAsset, TblMonitor.Manufacturer, TblMonitor.MonModel, TblMonitor.MonSerNo, TblMonitor.MonSize, TblMonitor.MAsset AS MAsset2, TblMonitor.MonAsset AS MonAsset2, TblMonitor.Manufacturer AS Manufacturer2, TblMonitor.MonModel AS MonModel2, TblMonitor.MonSerNo AS MonSerNo2, TblMonitor.MonSize AS MonSize2, TblNIC.NICID, TblNIC.NIC, TblOffice.OfficeID, TblOffice.OfficeSuite, TblOS.OSID, TblOS.OS, TblPCModel.ModelID, TblPCModel.PCModel, TblSound.SndID, TblSound.Sound, TblVideo.VidID, TblVideo.Video, TblPeripheral.PAsset, TblPeripheral.PerAsset, TblPeripheral.PerDescription, TblPeripheral.PerSerNo, TblPeripheral.PerMfr, TblPeripheral.PerModel, TblPeripheral.ConnectPC
FROM (TblVideo RIGHT JOIN (TblSound RIGHT JOIN (TblPCModel RIGHT JOIN (TblOS RIGHT JOIN (TblOffice RIGHT JOIN (TblNIC RIGHT JOIN ((TblModem RIGHT JOIN (TblMemory RIGHT JOIN ((TblDepartment RIGHT JOIN (TblCompany RIGHT JOIN TblNewUsers ON TblCompany.CoID = TblNewUsers.fCoID) ON TblDepartment.DepartID = TblNewUsers.fDepartID) RIGHT JOIN TblPC ON TblNewUsers.UserID = TblPC.fUserID) ON TblMemory.MemID = TblPC.fMemID) ON TblModem.MdmID = TblPC.fMdmID) LEFT JOIN TblMonitor ON (TblPC.fMonAsset2 = TblMonitor.MonAsset) AND (TblPC.fMonAsset = TblMonitor.MonAsset)) ON TblNIC.NICID = TblPC.fNICID) ON TblOffice.OfficeID = TblPC.fOfficeID) ON TblOS.OSID = TblPC.fOSID) ON TblPCModel.ModelID = TblPC.fModelID) ON TblSound.SndID = TblPC.fSndID) ON TblVideo.VidID = TblPC.fVidID) LEFT JOIN TblPeripheral ON TblPC.PCAsset = TblPeripheral.ConnectPC;

 

[tedsmith]

You dont exactly say what your problem is.
If you have the source for the boxes in your forms the same as the name it the query it will always go to the correct monitor (assuming you have the X,Y coordinates of each the form shifted to suit if you are using one form per monitor)
I suggest you run the query in access to see what the returned table looks like. You might be getting a double up in the results.

 

[royalcheese]

I agree, try using the code in an access query, i have had this problem may times before ,

Maybe you can break it down into smaller pieces ,

as in you type the pc URN (Unique reference number) in to a textbox , and then click a search button, that will load the attached monitors URN into a monitor listbox.

then when you click on one of the monitor URN in the list box , this will load the monitors URN information into some textboxes - for editing and saving or labels for displaying.

Hope this is helpful, I have code for this if needed

Chris