Pre-populated items in sub-subform (1 Viewer)

skyliner33v said:

I would also need an "Add Item" button that will open up a new form so that the user can add any Standard Item from the Standard Items table. Then a button would append the appropriate Standard Item ID to the Estimate Details table.

Click to expand...

Mark_ said:

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.

Click to expand...

Mark_ said:

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.

Click to expand...

mike60smart said:

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.

Click to expand...

mike60smart said:

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??

Click to expand...

jdraw said:

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.

Click to expand...

• 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.

jdraw said:

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

Click to expand...

jdraw said:

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.

Click to expand...

jdraw said:

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.

Click to expand...

• 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

skyliner33v said:

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.

Click to expand...

Mark_ said:

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.

Click to expand...

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

-If the list of common items is always the same then
1. Add a column to the Item table that is Y/N. Y indicates default. N indicates other.
2. After you add a new Project, (in the AfterInsert event for the project form) run an append query that selects items marked Y and append them to the project items table

- If the list of common items depends on something else such as the type of bridge,
1, you need a field that supports more than two states. call it DefaultCategory
2. After you add a new Project (in the AfterInsert event for the project form) run an append queyr that selects Items that match the Bridge category.

- If the sets are not mutually exclusive, such that there is a 1-m relationship with category, you need a child table to hold the categories so that more than one can apply to the same item.

Click to expand...

Pat Hartman said:

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:

Click to expand...

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