CombcBox with multiple Fields to populate Text Boxes and requery

[vanlanjl]

My problem is that i have a combobox that displays two fileds, when selecting it will populate the data in the text boxes. Before I explain my problem though you may need to understand my database. Also i am building the frontend in Acess 2007, the backend is already built in SQL Server, the SQL data is linked to Network Visio Diagrams of each Network Location, displaying all the various types of network gear and the asscoiated data for each device. The access is for the user to edit or create new data for the SQL database.

I have 5 tables but only two matter here:
Device Attributes: DeviceName (PK); AttributeName(PK); AttributeValue(can contain Null)
example data:
OH01CSW01; IP Address; 127.0.0.1
OH01CSW01; Type; IEEE
OH01CSW01; SerialNumber; 745087250

There will be multiple DeviceName data that is the same.
Bascically this is a table for Network gear and the feilds of data for each device.


The next table is:
tblDevice: LocationID; DeviceName(PK); Manufactuer
example data:
OH01; OH01CSW01; Cisco
OH01; OH01CSW02; Cisco

As DeviceName is the PK there can not be duplicates

So I have a form that allows me to create and edit the tblDevice.

Now I need a form that allows me to edit the DeviceAttributes table.

I have created a table titled DeviceAttributes
The table has three text boxes: DeviceName; AttributeName; AttributeValue

Record Source: DeviceAttributes
Record Source Qualifer: dbo
I have an on load event that open the form blank.

I have a combo box and this is where things are starting to get hairy.

Row Source:SELECT [DeviceAttributes].[DeviceName] FROM DeviceAttributes ORDER BY [DeviceAttributes].[DeviceName];

After Update: Macro: Search For Record: , , First, ="[DeviceName] = " & [Screen].[ActiveControl]

So when I click on the arrow in the combo box it pulls the list down ie:

OH01CSW01
OH01CSW01
OH01CSW01
Now each of those contain differant attributenames and attribute values

If i select one it WILL populate the text boxes, BUT if i select the next one it will keep the same data from before in the text boxes. It seems like it is only allowing to show the data for AttributeName per DeviceName. I can slect another DeviceName, like AZ01CSW01 and it will show the new data for this but again if i select another AZ01CSW01 it will keep the original data from before in there.

Also i would like to have the combo box show the DeviceName and the Attribute name.

I have tried this:

Row Source:SELECT [DeviceAttributes].[DeviceName], [DeviceAttributes].[AttributeName] FROM DeviceAttributes ORDER BY [DeviceAttributes].[DeviceName];

Same After Update Macro
Column Count 2

So it show the combo box like this:

OH01CSW01 | Type
OH01CSW01 | IP Address
OH01CSW01 | Model
OH01CSW01 | Serial Number
OH01CSW01 | Number Of Ports

So again if i select one, say OH01CSW01 | Type, it will populate the text boxes, OH01CSW01 | Type | IEE803, but if I select OH01CSW01 | IP Address, it will keep the data from before. Now if i agian go to a new DeviceName like AZ01CSW01 and select it will populate the text boxes with the new data, but again when switching within the same device name but differant attributename it does not.

I hope this isnt two confusing and could really use the help
thanks!

 

[dhookom]

Is this an ADP? The invalid column name ": " suggests it's either not an Access query/row source or your location is way different from mine.

If you want to see only one device and attribute, you would need the unbound combo box to select the device and attribute. Then you would need some method for displaying that single device/attribute record. I prefer to use a subform (either single or continuous) with the Link Master/Child to filter the subform records. Your subform would display the attribute and attribute value.

If this was for my customers, I would select the device from a combo box in a main form and display all of the attributes in a nice continuous subform where they could see all the attributes and values and modify them. This would require zero code and performance would be great.


Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

yea as stated in the original post this is an access .adp database, meaning it is linked to a SQL server database

 

[dhookom]

The original post does not suggest this is an ADP. Access 2007 doesn't support ADPs other than in earlier formats. I connect to SQL Server regularly with standard MDBs.

If you choose to use and ADP, you should understand why you got the error message and how to correct it on your own. The syntax in an ADP or a pass-through query would be:

SELECT [DeviceName] + ': ' + [AttributeName] 
FROM DeviceAttributes 
ORDER BY [DeviceName],[AttributeName]

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

the pass-through query you provided does not work. Also the ADP format is supported as instructed in MSDN for Access 2007.

I think this is becoming more complicated then it needs to be. I just twant the user to be able to search for one deviceName and be able to edit the AttributeName and attributeValue of said deviceName.

Yes i could do a datasheet or a splitform but that defeats the purpose, the user would still have to search through over 7000 records to find the one he wants to edit.

I want the user to bea able to say "search for AZ01Client01" and get the following results:

DeviceName | AttributeName | AtributeValue |
AZ01Client01 | Addressing | DHCP |
AZ01Client01 | EndOFSubNet | 127.0.0.255 |
AZ01Client01 | IPAddress | 127.0.0.1 |
AZ01Client01 | Model | N/A |
AZ01Client01 | SubNetMask | 255.255 |
AZ01Client01 | Type | IEEE 802.3 |

Now i would accept that info and that info only in a subform, if we could get that to work? I just dont want the user to search through thousand of records to edit only one. My user is not very smart and if it isnt easy he will complain alot. lol

 

[MajP]

You do not seem to see what Duane is suggesting so here is a demo. There is only a thousand records, but I can find any record in about a second or two.

http://www.4shared.com/file/248300901/b3f4b1bc/FindByCombo.html

 

[dhookom]

Again, I would have a simple combo box in the main form to select a Device name from the device table. Add a continuous subform that display a combo box for AttributeName and a text box for the AttributeValue. Set the Link Master to the combo box name and the Link Child to the DeviceName field.

You are done. Your user simply selects the appropriate device to display all of the attributes. He/she doesn't have to do more than one search to make two or more changes to the same DeviceName.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I believe my demo does what Duane suggests. However, if loading the combo is slow with 10k records than it can simply be replaced by a textbox still using the linked subform. The textbox is a little awkward in that you have to lose focus to trigger an update. The subform can be formatted to look like part of the main form.