Pre-populated items in sub-subform

[skyliner33v]

Are you bundling multiple items as a single line item in your quote? The civil engineers I know always had to list each item separately on any estimate and include both "Expected start" and "Expected Completion" dates.

Trenching alone could appear multiple times, especially when utilities were involved along with drainage. Even if you do not need it now, I would keep the ability to have more than one entry of a given type.

Then again the people I was dealing with had to adhere to government requirements for all proposals. Some of it became almost comic in what they had to list.

For this piece, take a look at the code on my add button. Should do exactly what you need. Just remove all of the references to the unbound fields (remove them from the form to) and you'll be set.

No bundling of any items. This estimate is mostly to be used for scoping out projects. So upper management just needs a general idea of what items, and their estimated costs at a very high level. Once the project goes to design, new cost estimates are generated that break down each item further if necessary and adds any non bridge related items as well.

Thankfully I don't have to built that database!

And yes, I will use your code for the add buttons. Thank you very much for those.

I start working on piecing all of this together and see how it goes.

 

[mike60smart]

Hi

To keep on top of which details belong to which Repair Category you need to set up a Main Form based on the Repair Categories and a Sub form that lists the Standard Items associated with the Repair Category.

Once you have this setup you can then as previously suggested have a Combobox where you select the Repair Category and then you can Insert into a table those Standard items associated with the Category selcted.

 

[skyliner33v]

Hi

To keep on top of which details belong to which Repair Category you need to set up a Main Form based on the Repair Categories and a Sub form that lists the Standard Items associated with the Repair Category.

Once you have this setup you can then as previously suggested have a Combobox where you select the Repair Category and then you can Insert into a table those Standard items associated with the Category selcted.

Hi Mike,

I apologize, but I think I know what you mean, but I'm not entirely sure how to implement that idea in practice.

I'm not sure how my tables should be linked so that i can do something like you suggest.

Do you have any ideas of how the tables can be linked together so that I can create these forms?

Thank you.

 

[mike60smart]

Hi

From the data currently stored in your tables tblRepair_Categories and tblStandard_Items, I am unable to determine which Standard Items belong to which Category??

 

[skyliner33v]

Hi

From the data currently stored in your tables tblRepair_Categories and tblStandard_Items, I am unable to determine which Standard Items belong to which Category??

Ah gotcha, yes. I am working on trying to figure out the best way to store the Standard Items that appear within a repair category.

@jdraw suggested a junction table that contained only the repair category and associated standard items, but I'm having trouble linking that up with my Estimate Details table as Access tells me that there is "No unique index found for the reference field of the primary table". So I'm at a loss on the best way to set up my tables and create the proper relationships.

Do you have any advice you could offer on how to set up and link the tables?

Thank you

 

[mike60smart]

Hi

You need a table for Categories

tblRepairCategories
-RepairCategoryID - Primary Key - Autonumber
-RepairCategory

(This table just contains the list of Repair Categories)

tblCategoryItems
-CategoryItemID - Primary Key - Autonumber
-RepairCategoryID - Foreign Key - Number - Duplicates OK
-CategoryItem

If you then create a Main Form based on tblRepairCategories
and a Subform based on the CategoryItems

You will then be able to create a list of specific Items for EACH Category

 

[jdraw]

skyliner33v,

Just returned to the forum. I had some "mystery issues" with Access last night, but did get it resolved.

I've thought a little more about your set up and have looked at some of Mark's zip files. I think the concept he is showing could be adapted. You make some basic set up materials (eg Category with StdItems).

When creating the Estimate you provide a list of possible work items, then select those that apply, then add/adjust quantities or amounts as appropriate. You can add nonStdItems as appropriate on a Repair by Repair basis. Then store the selections in the Estimate.

What I'm thinking about in simple "business" terms is:
-you create an Estimate for a Bridge Repair
-there are a number of work items associated with Bridge Repair
-Bridge Repair Types can be Categorized
-a Bridge Repair Category is related to 1 or more workitems
-a WorkItem related to a specific Bridge Repair Category is considered a StdItem
-a Workitem that is not associated with a Bridge Repair Category is considered a nonStdItem
-A Bridge Repair can involve StdItems (based on Category)
-a Bridge Repair can also include 0,1 or more nonStdItems
-a StdItem may have defined attributes and default values (x units and related Costs etc)
-an Estimate can be for 1 or more Category (I'm assuming this)

If these assumptions are valid: (and using Mark's sample generally)
There is a need to deal with processes such as:
(helper set up things)
-identify all workitems
-identify Categories
-relate Categories and workitems to identify StdItems
-identify the Category(s) associated with a BridgeRepair

When creating an Estimate:
-identify the Bridge
-identify the Repair(s) needed
-ForEach Category
---identify StdItems that could apply
---select StdItems that do apply
---assign attributes and values to each selected StdItem
---assign nonStdItems that apply

As has been said previously, you know your business and processes better than readers,
so this is a best guess that may be helpful. The draft models I posted were for consideration, but certainly not vetted with your requirements. I have not modeled the above, but on reviewing Mark's examples and thread posts, I just put some thoughts to paper. Some of these thoughts are assumptions, so adjust/replace or delete as you see fit.

Good luck.

 

[skyliner33v]

skyliner33v,

Just returned to the forum. I had some "mystery issues" with Access last night, but did get it resolved.

I've thought a little more about your set up and have looked at some of Mark's zip files. I think the concept he is showing could be adapted. You make some basic set up materials (eg Category with StdItems).

When creating the Estimate you provide a list of possible work items, then select those that apply, then add/adjust quantities or amounts as appropriate. You can add nonStdItems as appropriate on a Repair by Repair basis. Then store the selections in the Estimate.

What I'm thinking about in simple "business" terms is:
-you create an Estimate for a Bridge Repair
-there are a number of work items associated with Bridge Repair
-Bridge Repair Types can be Categorized
-a Bridge Repair Category is related to 1 or more workitems
-a WorkItem related to a specific Bridge Repair Category is considered a StdItem
-a Workitem that is not associated with a Bridge Repair Category is considered a nonStdItem
-A Bridge Repair can involve StdItems (based on Category)
-a Bridge Repair can also include 0,1 or more nonStdItems
-a StdItem may have defined attributes and default values (x units and related Costs etc)
-an Estimate can be for 1 or more Category (I'm assuming this)

If these assumptions are valid: (and using Mark's sample generally)
There is a need to deal with processes such as:
(helper set up things)
-identify all workitems
-identify Categories
-relate Categories and workitems to identify StdItems
-identify the Category(s) associated with a BridgeRepair

When creating an Estimate:
-identify the Bridge
-identify the Repair(s) needed
-ForEach Category
---identify StdItems that could apply
---select StdItems that do apply
---assign attributes and values to each selected StdItem
---assign nonStdItems that apply

As has been said previously, you know your business and processes better than readers,
so this is a best guess that may be helpful. The draft models I posted were for consideration, but certainly not vetted with your requirements. I have not modeled the above, but on reviewing Mark's examples and thread posts, I just put some thoughts to paper. Some of these thoughts are assumptions, so adjust/replace or delete as you see fit.

Good luck.

Hi jdraw. So the business terms are really close to what you describe, with the following exception.


-an Estimate can be for 1 or more Category (I'm assuming this)

For now (until I'm told otherwise) there will only ever be one category per estimate. So there is no need to assign multiple categories per estimate.

I was able to somewhat apply your previous relationship diagram and came up with the following attached diagram. My thinking is as follows in how I linked them together.

• The Estimate Details table can have 1 category_item_GID. This is joined to a CategoryItems junction table that houses each repair category, and the associated Standard Items for that Repair Category.
• The Estimate Details table also holds standard_item_GID's so that any Item from the Standard Items table can be added to the Estimate Details table.

I believe what I need to have, is a combobox in the Estimates table named "repair category". This isn't linked to any table, but I think I can use it as a form of control to assign the matching repair category and items to the Estimate Details table. The combobox would hold the list of repair categories that a user could select. And then a button that would read what repair category was chosen, then append the matching category_item_GID's to the Estimate Details table.

Then I would need a "Add new Item" subform that could pop up. The user would choose an Item for the Standard Items table and it would be appended to the Estimate Details table as well.

My problem is that I don't know how to structure the forms sot that everything goes into the right places. I'm pretty sure I understand how it should be done in theory, but fail at applying it in practice.

I hope all of that made sense. It's hard to describe these sorts of things in text alone.

 

[jdraw]

Is there a reason why you are using GUID rather than plain autonumber?

 

[skyliner33v]

Is there a reason why you are using GUID rather than plain autonumber?

Not sure. We have another very large database that houses a lot of other bridge information, and they use guids. So I think they wanted to keep the same format with the guids.

That part is out of my control.

 

[jdraw]

OK.
I am mocking up a sample based on Marks approach.
I mocked up some sample Items and Categories.
Then a form to start an Estimate.

I use the BridgeID and create a new Estimate record that generates a GUID for the EstimateID. I put that Estimate GUID into a textbox at top. These are working.

Based on the Category selected I open a subform with stdItems, after filling in some info I click the Add Record button, and attempt to use the GUID in the txtBox, but continue get a 3201 error. For whatever reason, the Estimate.EstimateID (GUID) is not matching (referential integrity) when I try to write the EstimateDetail record.

Note: In the attached diagram, I did not select a Category so all are listed. The category select is working, but my focus at the moment is using the same GUID as EstimateID
when trying to create a related EstimateDetail record.

 

[skyliner33v]

OK.
I am mocking up a sample based on Marks approach.
I mocked up some sample Items and Categories.
Then a form to start an Estimate.

I use the BridgeID and create a new Estimate record that generates a GUID for the EstimateID. I put that Estimate GUID into a textbox at top. These are working.

Based on the Category selected I open a subform with stdItems, after filling in some info I click the Add Record button, and attempt to use the GUID in the txtBox, but continue get a 3201 error. For whatever reason, the Estimate.EstimateID (GUID) is not matching (referential integrity) when I try to write the EstimateDetail record.

Note: In the attached diagram, I did not select a Category so all are listed. The category select is working, but my focus at the moment is using the same GUID as EstimateID
when trying to create a related EstimateDetail record.

Oh my gosh! You sir are my personal hero. Thank you so much!

 

[jdraw]

Not sure what latest status of this thread is, but I had computer failure Feb 15 and am
working on a 10 yr old Dell until the repairs are completed on my regular laptop.

The sample I gave was just a sample as to how it might be approached. If anyone has ideas
on getting a new GUID for an Estimate and then using that GUId to relate EstimateDetails, I'm listening.

 

[skyliner33v]

Not sure what latest status of this thread is, but I had computer failure Feb 15 and am
working on a 10 yr old Dell until the repairs are completed on my regular laptop.

The sample I gave was just a sample as to how it might be approached. If anyone has ideas
on getting a new GUID for an Estimate and then using that GUId to relate EstimateDetails, I'm listening.

That stinks about your computer. I'm sorry that happened and I hope you didn't lose any important information.

I'm still working on the database. I've since decided to abandon the table based form/subform/sub-subform approach and nuked all the forms I was working on. I really didn't have much anyway. And I slightly restructured the tables and relationships and added a new junction table that houses which Standard Items are associated with which Repair Category.

I've since created new forms based on queries instead of the tables that, so far, seem to be working better. Attached is a copy of what I have so far.

There is an Edit Estimate Form that contains all the standard items related to that estimate.

And there is a View Estimates form with buttons that will take you directly to that estimates record.

Some of the things I am now working on:

• Getting the Edit Estimate form to be blank for new data entry
• Comboboxes for the subform to be able to add any items from the Standard Items table
• Get some of the other buttons hooked up and working

I think I will have some trouble though getting the Edit Estimates form to be blank so I can add in new data. I'll still have the same issue of populating the GUID's across the appropriate tables. I took a look at the Northwind DB and tried to replicate it, but I'm getting an all white form instead. Nothing on it except for form header.

If you have time and could take a look at it for me, it would be greatly appreciated.

 

[skyliner33v]

That stinks about your computer. I'm sorry that happened and I hope you didn't lose any important information.

I'm still working on the database. I've since decided to abandon the table based form/subform/sub-subform approach and nuked all the forms I was working on. I really didn't have much anyway. And I slightly restructured the tables and relationships and added a new junction table that houses which Standard Items are associated with which Repair Category.

I've since created new forms based on queries instead of the tables that, so far, seem to be working better. Attached is a copy of what I have so far.

There is an Edit Estimate Form that contains all the standard items related to that estimate.

And there is a View Estimates form with buttons that will take you directly to that estimates record.

Some of the things I am now working on:

• Getting the Edit Estimate form to be blank for new data entry
• Comboboxes for the subform to be able to add any items from the Standard Items table
• Get some of the other buttons hooked up and working

I think I will have some trouble though getting the Edit Estimates form to be blank so I can add in new data. I'll still have the same issue of populating the GUID's across the appropriate tables. I took a look at the Northwind DB and tried to replicate it, but I'm getting an all white form instead. Nothing on it except for form header.

If you have time and could take a look at it for me, it would be greatly appreciated.

Well crap, I've run into another wall. In my Edit Estimates Form, I can edit the existing data there, but I can't seem to add new data to it. It gives me an error of "Field cannot be updated".

Any ideas?

 

[Mark_]

I didn't have a chance to look in on this earlier. I've got an updated version of the "Estimate" that allows you to add each Standard Item once, selected from a drop down. That was a neat little piece to code and taught me a bit about how ACCESS doesn't like to keep SQL straight if you put it in a query def. Let me know if this looks more like what you are wanting (I even used a smiley face to add an item) and I'll see if I can look into GUIDs tomorrow.

 

[skyliner33v]

I didn't have a chance to look in on this earlier. I've got an updated version of the "Estimate" that allows you to add each Standard Item once, selected from a drop down. That was a neat little piece to code and taught me a bit about how ACCESS doesn't like to keep SQL straight if you put it in a query def. Let me know if this looks more like what you are wanting (I even used a smiley face to add an item) and I'll see if I can look into GUIDs tomorrow.

Thank you Mark!

Sorry i didn't respond to this sooner. I had to put this project on the back burner for a while so I could work on another project. But I'm getting back to it now.

I've been able to make some pretty good progress on it so far. And I've definitely used the ideas from everyone here, which is greatly appreciated. I'm still stuck on a pretty important piece though, which has somewhat changed since when I started this project.

Basically, I have 2 tables, one that contains a list of the Repair Categories, and another table that contains all of the Standard Items. Then I have a 3rd table that is a join table that contains the Standard Items that are associated with a particular Repair Category.

On my "Edit Estimate" form, there is a subform that contains the list of Standard Items in an estimate. There is also a dropdown (on the parent form) that allows a user to assign a Repair Category to an estimate. What I need is when the user selects a Repair Category from the dropdown, they can click the "Add Repair Items" button and it will insert the Standard Items that are associated with the Repair Category they selected into the subform.

Example would be, they chose "Concrete Deck Replacement" as the repair category. And then they press the "Add Repair Items" button it would insert the following Standard Items into the subform:

• 0001 Mobilization
• 0061 Removing Portion of Existing Bridge,
• 4148 Epoxy Coated st Reinf Bar for Bridge
• .... etc.

Mark, I've tried modifying what you last provided me to work like I describe above, but I just can't seem to get it working correctly.

Attached is a copy of the database that I have so far.

Thank you

 

[skyliner33v]

All of you please forgive me for just jumping in. I do not have time to figure out which advice is currently "current". This thread has gone on way too long for such a simple, common requirement.

Here is what I would do:

My apologizes Pat. I know this was a common requirement, but it was a bit more difficult because I am using GUIDs (replication ID's) in my db. And there are some different things to get some parts to works.

But I was able to figure it out. Attached is the code I ended up using in case anyone else in the future runs into this same issue.

Private Sub btn_add_repair_items_Click()

If IsNull(Me.[cbo_repair_category_name].Value) Then
    MsgBox "Please select a Repair Category."
    Me.[cbo_repair_category_name].SetFocus
    Cancel = True
Else
    Dim ctl As Control
    Dim ctl2 As Control
    Dim strGUID As String
    Dim strGUID2 As String
    ' Get the GUIDs.
        Set repcatGID = Forms![Edit Estimate]!repair_category_GID
        Set estGID = Forms![Edit Estimate]!estimate_GID
    ' Convert the GUIDs to a string.
        repcatSTRING = Mid(StringFromGUID(repcatGID.Value), 8, 36)
        estSTRING = Mid(StringFromGUID(estGID.Value), 8, 36)
    
    ' Insert into EsitmateDetail Table
        Dim strSQL As String
        strSQL = "INSERT INTO TblEstimateDetails(estimate_GID, standard_item_GID) " & _
            "SELECT '{" & estSTRING & "}' AS estimate_GID, standard_item_GID " & _
            "FROM TblCategoryItems " & _
            "WHERE repair_category_GID = '" & repcatSTRING & "'"
    
    If Me.Dirty = True Then Me.Dirty = False
            
    CurrentDb.Execute strSQL, dbFailOnError
    
End If

    Me.frm_Estimate_Details_subform.Requery
    
End Sub