Dynamic form? Maybe?

[accessnewbness]

Hi all. I'm completely self-taught in Access and VBA, and have come across a problem on a database that I'm trying to put together to manage rental equipment for my company.

Simply put, we have different categories that this equipment falls under, with different numbers of physical attributes about the equipment being tracked.

Simplified example:
Pumps, attributes including Voltage, Horsepower, Material
Tanks, attributes including Height, Diameter, Capacity, Material

There are many categories, and some things we want to track up to 10 different attributes. I originally was going to place each individual attribute into its own column in a large table, but it looked like I would end up with something like 50 different columns, and that just didn't make sense.

So, what I decided to do was make an "Attributes" table (tblAttributes) with basically three columns (ID, Category, Attributes), where the user would be able to enter

ID (auto), Category, Attribute
1, Pumps, Horsepower
2, Pumps, Voltage
3, Tanks, Capacity
4, Pumps, Material
5, Tanks, Diameter

Etc.

By doing this, i was able to limit my attributes fields in the rental equipment table (tblRental) to only 11 fields, which is an ok number to work with. For reports and searching, this is fine, I know how to consolidate those fields and present them. That way anything dealing with a particular piece of equipment would only present non-null fields when describing the equipment.

What I want to do is make an Rental Equipment Entry form that is dynamic, so that when the user picks a particular category (let's say pumps) of equipment from a combo box, the form looks at the Attributes table, finds all the attributes for Pumps, counts that there are 3entries, presents controls for Attribute1, Attribute2, Attribute3 and makes the associated labels to guide the user to input the data.

Then, on another category (Tanks), the form finds only 2 attributes, so it creates text boxes associated with Attribute1 and Attribute2, and creates the associated labels for them.

This way, I only have to make one form, and as we add more equipment into this rental pool, I have to enter the equipment's category and the particular attributes I want to track. Right now I have specific forms created for each category all laying overtop of one another, and I'm playing with the .visible property with an If/Then statement to bring the right one up to the foreground.

This is definitely messy, and is a pain in the butt when I want to make a layout change, as I have to change all the different forms.

Please keep in mind i've really simplified the example, and am fine with using easy bits of code and modifying them to my needs.

I don't know if this is really a "form" question, a VBA question, a data structure question, or what, but I'm just freaking stuck because I really don't know what i'm doing.

I appreciate any and all advice and help (in advance), and will be more than willing to learn about things recommended...I just don't even know what I'm looking for here.

Please fire away with any questions, but keep them simple...I'm not the smrtest!

Thanks!

 

[Fifty2One]

Do you mean like making cascading combo boxes so on the screen you could choose the Category in the first combo pulldown then the second combo pulldown would be a pull down with only the Attributes related to the Category so you could drilldown the data that way?

 

[Uncle Gizmo]

>>> Processed on shared equipment that may have come in contact with nuts. <<<

LOL ........

 

[DCrake]

You main problem here is deployment. If you deploy the application in a compiled state you are never going to be able to dynamically create new controls on a form as save the form. For a start how are you going to handle events on each control. For example lets say the user selects "Pumps" and your code identifies there are 5 atttributes, it then goes away and "Creates" 5 texboxes for the user to be able to ente r the attribute(s). Now lets say the enter one into newtextbox 1 then miss out newtextbox2 and enter something into newtextbox3. How are you going to know this.

My solution would be to firstly have a combo box that lists the category, then have a cascaded listbox containing the known attributes for the particular category. Then by making the listbox muti-select the user can simply highlight the attributes as per requirement. This idea is fine if you do not need to priorotise them (something you have not discussed). If priority is a factor then you would employ a third listbox.

Combo box to pick category
List box one to list available attributes
List box two to contain chosen attributes in order of priority.

List box one would be changed back to a single select for this senerio.

Using the above methodology you would not have to worry about form design and placement of controls, naming conventions, events, etc.

David

 

[Atomic Shrimp]

What I want to do is make an Rental Equipment Entry form that is dynamic, so that when the user picks a particular category (let's say pumps) of equipment from a combo box, the form looks at the Attributes table, finds all the attributes for Pumps, counts that there are 3entries, presents controls for Attribute1, Attribute2, Attribute3 and makes the associated labels to guide the user to input the data.

This is where it's going wrong, I think - and it's because attributes should be a list in a table, not a horizontal array of fields.

I think you need something like:

• TblEquipTypes: A table of Equipment type definitions
• TblAttrTypes: A table of attribute type definitions (with a foreign key field specifying which equipment type any given attribute definition record can apply to)
• TblEquipment: A table listing equipment inventory
• TblEquipmentAttributes: A table containing attributes and their corresponding values for the equipment listed in the equipment inventory table

So...

TblEquipTypes contains one record for each different type of equipment available - and might look like this:
EquipTypeID, EquipmentName, Category, etc
1,Big Pump, Pumps,...
2,Little Pump, Pumps,...
3,Big Tank,Tanks,...
4,Inlet Valve,Valves,...

TblAttrTypes contains one record for each possible attribute, for each different type of equipment - and might look like this:
AttrTypeID,AttrName,EquipTypeID_fk, etc
1,Capacity,3,...
2,Max Pressure,1,...
3,Max Pressure,2,...
4,Pipe diameter fit,4,...

TblEquipment lists instances of installed equipment - and can list more than one of each type of equipment, as appropriate - and might then look like this:
EquipID, EquipTypeID_fk, Details,location, etc...
1,1,Emergency drain pump, SomeTown,...
2,1,Pool drain pump, SomeOtherTown,...
3,4,Pool inlet valve, SomeOtherTown,...

and TblEquipmentAttributes: would then be a long list of the attributes of each of the instances of installed equipment - like this:
AttrID,EquipID_fk,AttrTypeID_fk,AttrValue,...
1,1,2,200psi
- meaning: for EquipID 1 (the Emergency Drain Pump at SomeTown), AttributeType 2 (Max pressure) is 200psi

 

[accessnewbness]

So, I think I solved it (sort of, the code could be cleaner) this way:

tblAttributes is

ID, Category, Attribute1, Attribute2, ... , Attribute10

So

1, Pumps, HP, Voltage, Type, Material, Inlet Size, Outlet Size
2, Holding Tank, Capacity, Diameter, Height, Type, Material
.
.
.

Then on my form, when someone chooses a selection from my CategoryChooser combo box (set list), I run this afterupdate code:

Private Sub CategoryChooser_AfterUpdate()
    
    lblTitle.Caption = Me.CategoryChooser & " "
        
    Dim Att1 As Variant
    Dim Att2 As Variant
    Dim Att3 As Variant
    Dim Att4 As Variant
    Dim Att5 As Variant
    Dim Att6 As Variant
    Dim Att7 As Variant
    Dim Att8 As Variant
    Dim Att9 As Variant
    
    Att1 = DLookup("[Attribute1]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att1) Then
        lblAtt1.Visible = False
        Attribute1.Visible = False
    Else
        lblAtt1.Caption = Att1
        lblAtt1.Visible = True
        Attribute1.Visible = True
    End If
    
    Att2 = DLookup("[Attribute2]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att2) Then
        lblAtt2.Visible = False
        Attribute2.Visible = False
    Else
        lblAtt2.Caption = Att2
        lblAtt2.Visible = True
        Attribute2.Visible = True
    End If
    
    Att3 = DLookup("[Attribute3]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att3) Then
        lblAtt3.Visible = False
        Attribute3.Visible = False
    Else
        lblAtt3.Caption = Att3
        lblAtt3.Visible = True
        Attribute3.Visible = True
    End If
    
    Att4 = DLookup("[Attribute4]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att4) Then
        lblAtt4.Visible = False
        Attribute4.Visible = False
    Else
        lblAtt4.Caption = Att4
        lblAtt4.Visible = True
        Attribute4.Visible = True
    End If
    
    Att5 = DLookup("[Attribute5]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att5) Then
        lblAtt5.Visible = False
        Attribute5.Visible = False
    Else
        lblAtt5.Caption = Att5
        lblAtt5.Visible = True
        Attribute5.Visible = True
    End If
    
    Att6 = DLookup("[Attribute6]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att6) Then
        lblAtt6.Visible = False
        Attribute6.Visible = False
    Else
        lblAtt6.Caption = Att6
        lblAtt6.Visible = True
        Attribute6.Visible = True
    End If
    
    Att7 = DLookup("[Attribute7]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att7) Then
        lblAtt7.Visible = False
        Attribute7.Visible = False
    Else
        lblAtt7.Caption = Att7
        lblAtt7.Visible = True
        Attribute7.Visible = True
    End If
    
    Att8 = DLookup("[Attribute8]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att8) Then
        lblAtt8.Visible = False
        Attribute8.Visible = False
    Else
        lblAtt8.Caption = Att8
        lblAtt8.Visible = True
        Attribute8.Visible = True
    End If
    
    Att9 = DLookup("[Attribute9]", "tblAttributes", "[Category]= categorychooser.value")
    If IsNull(Att9) Then
        lblAtt9.Visible = False
        Attribute9.Visible = False
    Else
        lblAtt9.Caption = Att9
        lblAtt9.Visible = True
        Attribute9.Visible = True
    End If
        
End Sub

Now, this is easy enough to code and relate back to the Attribute(1-9) text boxes for entry on the form.

Is there a way to make the code more elegant, so that I "loop" or something through, changing the numbers, or do I just leave it hard coded.

Does that make sense?

 

[accessnewbness]

I'm bumping this thread to see if anyone has any opinions of the code I wrote there, and if there is a way to do it any better.

Thoughts?

 

[Thinh]

I have some code that is quite complex for creating dynamic controls. I believe it may solve your issue. Let me know if this is something you are looking for.

Please check the picture for the setup.