How to store ID # of selected Combo box value 1

[73pixieGirl]

Hello,
I have a combo box on an input form that uses a query to get values of a field (deviceName) from another table (tblHardware). In the query I pull the deviceName and the deviceID. When the user selects a deviceName, I need to store the deviceID of the deviceName in a text field/table. The form's record source (tblSoftware) is the table that the data will be stored in. The query/combo box work great, I just don't know the syntax/method of grabbing the deviceID value and assigning that value to a field in another table. I have very very little VB experience. Can anyone help me please?
TIA!

 

[Ed2020]

It sounds like the combobox's recordsource is set to retrieve two values - DeviceName and DeviceID (in that order).

I would set the fields in the opposite order (DeviceID then DeviceName), give the combobox a column count of two, and optionally hide the DeviceID field by setting the first column's width to zero if the user has no need to see it.

You can then reference the combobox object as if the user was only selecting the DeviceID. In effect this is what they will be doing...

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

Hi Ed2020, thank you for your help. I think I understand what you're saying, but I failed to explain that I need to store the DeviceName and the DeviceID in tblSoftware. So when the user selects a DeviceName from the combobox, that value is stored in a field, then I need to assign that DeviceName ID value in another field. So after the combobox is updated with a value, set a text field to the DeviceID value.

 

[Ed2020]

73pixieGirl,

There is no need to store both fields in tblSoftware - you are storing redundant data.

Providing the DeviceID field is unique you can always retrieve the DeviceName data for display purposes in queries, reports, forms etc by joining in tblHardware.

Having said that you *really* want to you can refer to the two combobox columns like this:

DeviceID = Me.ComboboxName.Columns(0)
DeviceName = Me.ComboboxName.Columns(1)

You'll need to replace ComboboxName with the name of the control. Also note that columns index from 0, not 1.

How you then store the values in the table depends on whether the form is bound or unbound.

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

I see what you're saying, and I don't want to store redundant data. Maybe I don't have my tables set up correctly, and it will probably make more sense if I give you the big picture.

1) I have 3 tables - tblGeneralInfo, tblHardware, tblSoftware

2) The tblGeneralInfo fields need to be filled out for each Hardware and Software item, so I have itemID as a PK in tblGeneralInfo, and itemID as FKs in tblHardware and tblSoftware.

3) My main form has all the tblGeneralInfo fields for the user to input. Depending on if the user is inputting Hardware or Software data, the user clicks a button to take them to the respective form to enter either the Hardware or Software data.

4) If the user is entering Hardware data, the DeviceName is entered on the 2nd form.

5) If the user is entering Software data, the user clicks a combo box to select which Hardware device (DeviceName) the software is installed on.

6) On a separate form I need to show (for display only, no inputs) the tblGeneralInfo fields, the tblHardware fields, and a subform that lists all the tblSoftware records that belong to the tblHardware/DeviceName.

tblGeneralInfo -
itemID - PK
projectName,
contractName,
buildingLoc,
roomNum,
vendorName, etc.

tblHardware -
hwID - PK
hwMake,
hwModel,
hwSerialNum,
DeviceName,
hwComments, etc.

tblSoftware -
swID - PK
hwID - FK
hwDeviceNam,
swManufacturer,
swProduct,
swDescription,
swSerialNum, etc.

So I'm stuck on how to join all the tblSoftware records to the tblHardware record. Since the tblGeneralInfo fields need to be filled out for Hardware and Software items, the only link between tblHardware and tblSoftware is the DeviceName (b/c I can't figure out how to store the hwID in the tblSoftware hwID field).

Am I going about this all wrong?

 

[Ed2020]

I think you need to create a join between tblHardware.hwID and tblSoftware.hwID. This will create a one-to-many relationship and allow you to identify what software is installed on any given hardware device (and vice-versa).

If I understand correctly there will also be general information that can pertain to both hardware and software products. This will need to contain a Hardware/SoftwareID field which will store the hardware or software ID number (depending on which product type the record is referring to). The relationships on this table will be:

tblHardware.hwID = tblGeneral.HardOrSoftID

and

tblSoftware.swID = tblGeneral.HardOrSoftID

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Just spotted a flaw in my logic - this will cause problems as hwID and swID will contain the same values if you are using an autonumber field. This will mess up the relationship to tblGeneral.

Perhaps you could prefix the hwID and swID fields with an "h" or an "s" to denote which product type it is? Alternatively you could just move the fields in tblGeneral to tblSoftware and tblHardware - this is a bit of a cheat but it works.

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

Ok, your HardOrSoftID in tblGeneralInfo is what I called itemID so I now have joined tblGeneralInfo.itemID to tblSoftware.swID and tblGeneralInfo.itemID to tblHardware.hwID as you said. But I'm still confused as to how the relationship between tblHardware and tblSoftware is created, other than matching up the DeviceName.

The user will enter the tblGeneralInfo record first, then go to a form to enter a tblHardware record, or another form to enter a tblSoftware record.

At no point is the user opening a form to enter a tblHardware record then going to a form to enter a tblSoftware record. tblGeneralInfo is the main form and must be filled out first, and the itemID gets stored in the other 2 tables.

 

[Ed2020]

If tblHardware.DeviceName=tblSoftware.hwDeviceName works then so will tblHardware.hwID=tblSoftware.hwID - they come to exactly the same thing. Unless I'm missing something....?

When a user enters details of a software product they will select from a combobox (either DeviceID or DeviceName) which hardware device it is installed on. In order for them to do this the details of the hardware device *must* already be in the system otherwise the system will not contain the hardware in the combobox's list.

Thinking of it in terms of a parent-child relationship all children (software records) must have a parent (hardware record), and the parent must be created before the child otherwise you will have orphaned records.

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

Thank you for sticking with me Ed2020, I really appreciate your help!! You are correct about hardware information being entered first, before any software records can be entered. tblHardware.DeviceName=tblSoftware.hwDeviceName will work, but using the IDs won't be I haven't figured out a way to store the hwID into tblSoftware.hwID. tblHardware contains hwID and DeviceName. I have created a field in tblSoftware, hwID, which joins tblHardware and tblSoftware. When I fill out the tblHardware information, the hwID is set to the tblGeneralInfo.itemID, and the tblHardware.DeviceName is entered. Then I go to add a Software record, first entering tblGeneralInfo data, then entering tblSoftware data, with tblSoftware.swID being set to tblGeneralInfo.itemID, and the hwDeviceName is selected from a combo box - the record source for the combo box is a query on tblHardware, grabbing the DeviceName and hwID. The DeviceName is stored, but no where do I have the hwID being stored into the tblSoftware.hwID. That's why I initially thought I needed to manually (using code I should say) set the tblSoftware.hwID equal to whatever the combo box value ID (hwID) was.

 

[Ed2020]

Aha! Now I understand.

I would add an ItemID field into tblHardware and make it your foreign key for the join to tblGeneral.

Make hwID is your primary key for tblHardware. Then tblHardware.hwID=tblSoftware.hwID will work.

tblGeneral - No parent table so only requires primary key.
tblHardware - Parent and child tables so requires primary and foreign keys to support one-to-many relationship
tblSoftware - Only has a parent table. Needs foreign key and should have a primary key.

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

Thanks, Ed2020, I'll make those changes. But how/when does tblSoftware.hwID get entered? tblGeneralInfo is the parent table of both tblHardware and tblSoftware. I must be missing something...

 

[73pixieGirl]

should all primary keys be auto numbers?

 

[Ed2020]

tblSoftware.hwID gets populated from the form the user enters the software details into. Your combobox's rowsource should be:

SELECT hwID, DeviceName FROM tblHardware;

Give the combobox two columns, set width of the first column to zero. Assuming your form is bound to tblSoftware set the combobox's Control Source property to hwID.

tblGeneralInfo is the parent table of both tblHardware and tblSoftware.

Sorry - yes you're right.

should all primary keys be auto numbers?

I think the primary keys in tblHardware and tblSoftware have to be unique across both tables in order for the joins to tblGeneral to work so using autonumber alone won't work. I'd prefix the hwID PK with "H" and swID with "S", followed by a sequential number. This ensures uniqueness across the two tables. Hope that makes sense!

Ed Metcalfe.

Ed Metcalfe.

Please do not feed the trolls.....

 

[73pixieGirl]

IT WORKED!!! FINALLY!!! Now we can put this thread to rest!!
Many many thanks to you, Ed2020, for sticking with me until my problem was solved! I wish I could give you more than one star!!!

 

[Ed2020]

You're welcome. Glad we got it sorted.

Ed Metcalfe.

Please do not feed the trolls.....