Probably a simlpe thing to do! 1

[tekskin]

Hi everyone. I'm a bit weak on access, but perhaps someone out there can help:

I have a table called SR, which has a field call PROGRAM (which looks at a table called PROGRAM, and a field called APPLICATION (which looks at a table called APPLICATION).

Program holds the main titles e.g. microsoft, citrix, appsense etc.. APPLICATION has the applications for each program e.g. Microsoft Office, Citrix MetaFrame etc..

I would like to open a simple columnar form, and when I select Microsoft from the PROGRAM field, the APPLICATION field will only show applications which are in the Microsoft group.

Probably very easy, but I'm a bit slow in this one!

Thanks

Kevin

 

[grande]

Not a problem, but one question, how do you plan on "selecting" Microsoft, Citrix, etc?

-------------------------
Just call me Captain Awesome.

 

[hkaing79]

I would suggest using a combobox with rowsource = "Select Program FROM SR ORDER BY Program". This will allow you to select your application. Use this value to filter out your applications. I would use a listbox to display the results, using rowsource = "SELECT Application FROM SR WHERE Program='" & cboProgram & "' ORDER BY Application". Then you can select your application from the listbox and run it whichever way you like.

 

[famehrie]

Kevin,

Wow thats my name too

I beleive what you are trying to do can be found all over this forum. I have seen it in many threads but can't remember any of them. I found a thread once when I was trying to filter a field based on a combo box so try searching for that. I put together a simple DB based on what you are looking for and I will put some details, on how to do it, together and post it as soon as I am done. It is not overly dificult but it will require some lite VB coding but nothing to in depth though. Look back in a few days.

Kevin

 

[tekskin]

Thanks famehrie, that sounds like a great help!

Looking forward to seeing what you can come up with!

Kevin

 

[famehrie]

Sorry for the wait been busy. Here is what I did. May seam like alot of work but if you do it once it will become almost second nature. I have incorporated this method in many of my databases. The forum may have formated my text a little different then I had planned but it should be close.

3 tables
1 query
1 form

Tables

tblProgram – Contains the data for the different OEMs such as Microsoft, Citrix. Should have 2 fields.
Field1 = OEMID – Set it as the Primary Key and auto number it
Field2 = OEMName – This is a text field where you will enter the OEM data such as Microsoft, Citrix.
OEMID OEMName
1 Microsoft
2 Citrix

tblApplication – Contains the data for the different applications such as “Microsoft Office 2000”. It should have three fields.
Field1 = APPID – Set it as the Primary Key and auto number it.
Field2 = APPName – This field will be for the name of the application such as Office 2000
Field3 = OEMID – Set this field as a number field. The number that goes into this field will directly reference the primary key number for a particular OEM in the TblProgram table. As an example if the OEMID for Microsoft in the tblPrograms table is 1 then the OEMID you put in the OEMID field in the tblApplication table will be 1 if the product is a Microsoft APP.
APPID APPName OEMID
1 Office 2000 1
2 Win XP 1
3 GoToMyPC 2

tblSR – Contains the full details of a particular product. this is your main table for storing your data. It contains three fields.
Field1 = ID – Set it as the Primary Key and auto number it.
Field2 = Program – Set as a number field.
Field3 = Application – Set as a number field.

Now that the tables are done lets set up our form.
Use the form wizard and base your form on the tblSR table. Just easier that way. Next change the Progam box to a combo box. Now open the properties for this box and go to the All tab and change the following properties.
Name = txtProgram
Row Source = tblProgram
Column Count = 2
Column Width = 0";2"
Bound Column = 1
After entering the above goto the Events tab and in the after update event set it for [Event Proceedure] and then click the elipse (…). This will open the VB editor. At the prompt enter in Me.txtApplication.Requery. Save and then close the VB editor.

Now back on the form change the Application box to a list box. Now open the properties for this box and go to the All tab and change the following properties.
Name = txtApplication
Row Source = qryApplications
Column Count = 4
Column Width = 0";0";2";0"
Bound Column = 1
Were done with the form. Save the form as frmSoftware and close it.

Now we need to set up a query
Create a query in design view and select the tables tblApplications and tblPrograms as part of this query. This query is going to have 4 columns. Insert the columns in the following order.
Column1 = OEMID from the tblPrograms table
Column2 = AppID from the tblApplication table
Column3 = AppName from the tblApplication table
Column4 = OEMID from the tblApplication table

Oh I forgot something we need to set up a relationship. Goto tools and relationships. Choose the tables tblApplication and tblProgram for the relationship. Click and drag the OEMID in the tblProgram table over to the OEMID in the tblApplication table. In the window that comes up check all three check boxes and click OK. Close and save the relationships window.

Now back to the the query. In the criteria field for Column1 enter in the following exactly is it appears below.
[Forms]![frmSoftware_Inventory]![txtProgram]
Save the query and name it qryApplications.

Now open the form and test it. If all goes well the Applications will list only those for the OEM selected in the Program combo box.

I hope all goes well and if you need help let me know.

 

[willir]

famehrie

Sounds like a continous subform will work here. You can change the record source of the form and subform on the fly.

On the main form, you select the OEM.
The record source for the subform is changed to accommodate the selected vender.

Assumptions
cmbSelectOEM - combo box to select the OEM vender
sbfrmApplication - subform used to display applications

For the AfterUpdate event for cmbSelectOEM

Dim strSQL as String

strSQL = "SELECT * FROM tblApplication"

If Nz(Me.cmbSelectOEM, 0) Then
   strSQL = strSQL & WHERE OEMID = Me.cmbSelectOEM
End If

Me.sbfrmApplication.Form.RecordSource = strSQL
Me.sbfrmApplication.Requery

Richard

 

[tekskin]

Thanks famehrie - I will give this a shot over the weekend and report back! You are a star, so you get a star!

Kevin

 

[tekskin]

Famehrie, that works great except for one small thing.

In the frmSoftware form, when I click on the Applications field, the list appears perfectly, but no matter which one I select, it will always display the first entry in the list.

Any ideas?

Kevin

 

[famehrie]

Try changing the list box to a combo box. That will then only display the item you selected.

 

[tekskin]

I have tried that, and when you click on the down arrow, it displays the items correctly, but as soon as you click on the item you want, the box closes up again, but still displays the original item at the top of the list!

I am going to have another look in case I have made a boo-boo!, and I will update you shortly.

Kevin

 

[tekskin]

famehrie, I have just checked and everything is inputted as you described,but is does not work.

When I had applications as a list box, even when I have clicked on the correct application from the list, if I then go to another record, and then back again, the application is not selected. It is the same for the combo box.

Any ideas?

Kevin

p.s. Thanks for the time your are taking to help me with this.

 

[grande]

Is the Control Source set to the field name of the Application?

-------------------------
Just call me Captain Awesome.

 

[tekskin]

Yes, I have got the control set to Applications.

After I have selected the Programs list, when I click on the applications field the correct Applications from the Table are listed, but it is just getting the field to accept the selection of the entry I want by clicking on it.

Kevin

 

[famehrie]

OHHH!

It sound like it is not saving the record. Double check the record source for all of the controls.

First open the form in design view and open the properties for the form. On the data tab make sure that tblSR is in the Record Source field.
Next check the properties for the ID text box. On the data tab make sure that ID is selected in the Record Source.
Similarly make sure the Record source for Programs is set to Programs and that Application is set to Application.

Let me know what happens.