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!

 

[PHV]

Use VBA instead of macros.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vanlanjl]

I dont know VBA, usally people on here help me with that

 

[dhookom]

I'm having trouble picturing your form and what/why.

However, your combo box should always have a unique value as the bound column. If not, you can't ever be assured of selecting the proper item in the list if there are duplicates.

If you just want to edit/add attributes, I would use a main form based on tblDevice and a continuous subform based on Device Attributes.

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

Bound column is set to one.

I want to use the combobox to search for the appropiate device

I have over ten thousand records in here, if i did a form and subform it would never load.

 

[dhookom]

One" is no a unique value so it won't work.

"ten thousand records" why won't this work. You generally want to open the main form to a single device (one record) with the attributes on the subform. This is typically and should not be an issue.

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

I just want help with the question I had please.

If anyone can help much appreciated.

 

[dhookom]

As I have stated, your combo box Row Source must generate a unique value.
Row Source:SELECT [DeviceName] & ": " & [AttributeName] FROM DeviceAttributes ORDER BY [DeviceName],[AttributeName];




Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

sorry - i thought you said "bound column"

invalid at ":

 

[vanlanjl]

i meant " invalid column name ":

 

[vanlanjl]

maybe it needs some way to requery when selecting again?

 

[dhookom]

Sorry, you are confusing me more and more with each post. I can't get my head around how your form is set up and why? Is there a reason why you just don't use a form and subform?

Is your main form continous or single?

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

why would I want a subform?
the form is very basic

it has a combo box at the top

and three text boxes. The combobox selection should populate the text boxes

I dont want a subform

 

[vanlanjl]

Subforms are sloppy and confuse users. i just need a form wher the user can look up the device by the device name and the edit the attributeName and the AttributeValue
Sometimes we may swap a network device and the ip address will change or the serila number etc. Or maybe we add a new type of device that has different fields (atributeValues) that need to be added

 

[dhookom]

Are you going to answer my question regarding the form being continuous or single? Is the combo box at the top bound or simply used to find a record?

Subforms aren't sloppy. They are used almost everywhere such as shopping carts on the web etc. This is a basic user interface.

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

the combobox is unbound. I have no idea what you mean by coninous or single

 

[dhookom]

Search Help on Continuous.

Again, if you want to select a device and attribute in the combo box, change its Row Source to do this:

SELECT [DeviceName] & ": " & [AttributeName] 
FROM DeviceAttributes 
ORDER BY [DeviceName],[AttributeName];

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

i tried that and i get the error: invalid column name ": "

I stated that in the above post

 

[vanlanjl]

it is not contious it will show only one record at a time depending on the selection in the combobox

 

[vanlanjl]

Okay i changed it to continuous and now when i make a selection form the combobox it will show all 7062 records.
this defeats the whole purpose on making it so the user does have to search through all the reords to find one