Unbound controls to insert record

[epicmove]

Hi guys. I hope someone replies to this thread....I am struggling with this one.

I am developing an Asset Management System for our IT Equipment.
We wish to record the software installed on each PC.

The Manufacturer and Software names are stored in separate tables.
The AssetMain table has a one-to-many relationship with the AssetSoftware table.

I originally had a (continuous) subform for software on my main Asset form. However, as I am using cascading combo boxes (to filter the Software Combo by the Manufacturer) it mean that changing the combo on each record would appear to change the existing values too (common problem).

I would therefore like four unbound controls.
1) Combo Box - Manufacturer
2) Combo Box - Software Package
3) Combo Box - Package Edition
3) Text Box - Serial Number

When the user selects/enters these values and clicks an "Add" button I would like add a new record into the AssetSoftware table where Asset_ID = Parent.Asset_ID.

I will then have a list box that queries the software for that particular Asset. This will requery on each CmdAdd_Click.

Attached is a screenshot of what I would like to achieve (if I click Add it should add Microsoft Office XP to the list box below)

Would really appreciate some help (do not normally get many replies to my threads )

Thanks
BF

 

[Banana]

Sounds like this is something you want.

Docmd.OpenForm, "ThatForm"
Forms![ThatForm]![ThatID] = Me.ThatID

then when you're done with adding that form, insert this code in Save button or whatever it is you use to close-

Forms![ThisForm]![Listbox].Requery
Forms![ThisForm]![Listbox] = Me.ID

HTH.

 

[epicmove]

thanks for the reply. not quite what I would like to achieve though.

I will not be opening the form separately so no need for the openform and link criteria.
Just really need to know how I can insert a record into a database table without having to set the record source of the particular form to that table.

Im sure I can use an SQL insert into command.....will investigate

 

[KeithG]

Yeah, I would just use an Insert SQL statement

 

[epicmove]

Think ive cracked it....please see db attachment

Hey guys,

Think ive done it. Would it be possible for someone to look at my attached database.
Ive created FrmAssetMain form with the Software form as a subform. When you click the "Add" button on the subform it inserts the values from the software edition combo box and the AssetID from the parent form into the AssetSoftware table. The results are then displayed on the listbox on the subform.

But....I still want more.

I would like to add a delete button so that the user can add and remove Software on the same screen.

Can I use SQL to delete a record based on that records unique identifier?
Ammendments to my own db would be even better.

Thanks
BF

 

[epicmove]

Thanks Pat, will give that a go. One question....I can see how this would work for the fields stored in my table. However as only software package edition is stored and not manufacturer/package name, how would I be able to show this data? Would I need a reverse lookup on the package edition? - not sure howt to achieve this.

Also, while im on the subject of list boxes, what property do I need to refer to, to find the selected item.
I have a search form where the results are displayed in a list box. On double clicking a result, I would like to open the full details in a different form.

Thanks
BF

 

[Banana]

If it's a simple listbox,

me.lstbox.itemselected(i)

With multiselect listbox, it's now an array so you need to loop through it. Search for Pat's code on how to add new records based on multiselect listbox on this board.