Help - Need to finish project today! (1 Viewer)

[epicmove]

Hi guys,

I need to finish the Asset Management System I have been creating today.

The main Asset Form works fine for Data Entry. However when I view existing records, the Model of the Asset (I.T Equipment) appears blank.

The reason for this is:
I have three tables linked in the following way:
Manufacturer > Model Group > Model

When an Asset is recorded in the DB, the user must select a Model.
To minimise duplicating data I decided to only store the Model ID in the Asset Table. This way, if I need to view the Manufacturer and Model Group, I can just do a reverse lookup based on the Model ID.

Problem:
The Manufacturer and Model Group are unbound combos. The Model is a bound combo filtered by the Model Group filtered by Manufacturer (aka Cascading Combos).

However, as I am not storing the values for Manufacturer and Model Group in my Asset Table, when viewing existing records, all combo boxes are blank.

Has anyone got a solution to this problem. I downloaded an example by Pat Hartman that requeries the filtered combo on the forms current event. However in that example the main combo was stored in the underlying table.

Any suggestions. Would really like to knock this one on the head today.

Can post an example DB if it would help

Many thanks
BF

 

[KeithG]

Will you post the example please?

 

[epicmove]

Example DB

Please find attached an example DB demonstrating the problem.

You can see that the records already entered do not appear to have anything entered in the Model field. However if you look in the TblAsset table you can see these values.

The value will only show if the unbound combos Manufacturer and Model Group are related to the stored value i.e. if SS9 is stored and the combos still display Dell and Smartstep, you will be able to see SS9.

Thanks for the help
BF

 

[Pat Hartman]

You need to include the lookup tables for the unbound combos in the mainform's RecordSource query. Then you can add textboxes bound to the "lookup" values underneath the combos. Set the background of the combos to transparent. That way when a value is not showing in the combo, you can still see what is in the lower textbox.

 

[epicmove]

Works okay...

Hi Pat,

Thanks for your post. Problem was that my form was based on the underlying table not a query.

I have reposted my database after doing what you suggested. This works fine when viewing records. However, when entering a new record the combo the text boxes do not update with the combo values until the Model is selected.

This is because the Manufacturer and Model Group text boxes are based on the Model text box which is based on the Model combo box which is in turn based on the Model Group combo box?

Any way round this?

Quick question. Is it good practice to base all forms on queries of the tables rather than the tables directly? What are the advantages of doing this? If I have subforms based on queries, will the Parent ID from the main form still be added?

Thanks for the great help
BF

 

[rborob]

had a look and im baffled...exactly what is it that your trying to do? why do you have 2 combo boxes for each selection?

 

[epicmove]

Cracked it!!!

Think ive just found the solution.

Using Pat's good advice I based my form on a query that contained the Manufacturer and Model Group tables, linked by the Model ID stored in the Asset Table.

This meant that by placing the Manufacturer and Model Group Text Boxes over the Manufacturer and Model Group (unbound) combo boxes, it allowed me to view these values for existing records.

However, when adding a new/editing an existing record, the Manufacturer and Model Group Combos are used to filter the Model Combo. As the text boxes were covering these combo boxes, the combos did not appear to update until a model was selected. This is because the text boxes are dependent on the model ID stored in the AssetMain table.

Solution:
Add the following code to the after update event of the Manufacturer/Model Group combos.

Private Sub CboManufacturer_AfterUpdate()
Me.CboModelGroup.Requery
Me.Manufacturer_Name.Visible = False 'hides the text box
End Sub

Then add this code the afterupdate event of the Model Combo

Private Sub CboModel_AfterUpdate()
Me.Manufacturer_Name.Visible = True 'shows the text box
Me.Model_Group_Name.Visible = True 'shows the text box
End Sub

As the Model combo was filtered by an unbound combo (that will be blank on form open) I removed the "where model_group_id = forms!frmassetmain!cbomodelgroup" from the combo's underlying query and instead added the criteria to the after update event of the modelgroup combo (as this is the only time I need to use the criteria):

Private Sub CboModelGroup_AfterUpdate()
Me.Model_Group_Name.Visible = False
    Dim strSQL As String

    strSQL = "SELECT QryModel.Model_ID, QryModel.Model_Name " & _
    "FROM QryModel " & _
    "WHERE (((QryModel.Model_Group_ID)=[Forms]![FrmAssetMain]![CboModelGroup]));"

Me.CboModel.RowSource = strSQL
Me.CboModel.Requery

End Sub

I have attached a new copy. Would appreciate anyones thoughts / suggestions on this. Thanks