Apply formatting to a record after adding to a table under certain conditions

**gaker10** · 06-04-2015, 05:34 PM

I have the code below:

Code:

Private Sub cboComponent_AfterUpdate()
Dim strPN As String


'Replace drawing number with Primary part if the entry is a drawing
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")


'If the string is not empty
If strPN <> "" Then
'this is a DRAWING, set component value and corresponding information
Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")
Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.Revision = DLookup("Revision", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")


'Load AlternateA into the drop down
On Error Resume Next
   txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateA; "
                
'Load AlternateB into the drop down
On Error Resume Next
   txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateB;"
            
'Load AlternateC into the drop down
On Error Resume Next
   txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateC;"


'If this is not a drawing, its an assembly/kit or regular part with possible alternates
ElseIf strPN = "" Then


'Compare entry to assembly and kit numbers in the table
strPN = Nz(DLookup("AssemblyKitNumber", "ASSEMBLIESKITS", "[AssemblyKitNumber]='" & Me.cboComponent & "'"), "")


    'If a match is found
    If strPN <> "" Then
            'this is an assembly or kit
            Me.txtComponent = strPN
            Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
            Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
        
        'Load all parts for possible alternates in drop down
           txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                    
            txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                    
            txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
    
    Else
    'This is a part number
    Me.txtComponent = Me.cboComponent
        Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
        Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
              
                            
    
    strPN = Nz(DLookup("AlternateA", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
    
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateA " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateA; "
    Else
       txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
    End If
    
    strPN = Nz(DLookup("AlternateB", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateB " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateB; "
    Else
        txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
        End If
        
        strPN = Nz(DLookup("AlternateC", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateC.RowSource = "SELECT ALTERNATES.AlternateC " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateC; "
    Else
        txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
        End If
    
    End If
End If


End Sub

The part of the code I am focusing on is the first few lines, where the [DRAWINGS] table is concerned. Basically, it looks at whatever number I type in. If this number is from the DRAWINGS table, it adds the Primary Part and the subsequent alternates instead of the drawing number.

Now we have changed our process, and want to include the drawing number to the added records of the table. So, I just need to tweak this code to add the drawing number IN ADDITION to the Primary part and all alternates. So, it will look like this in my table:

Drawing Number
Primary
AlternateA

AlternateB
AlternateC

Instead of (this is what the code does now)

Primary
AlternateA
AlternateB
AlternateC

But thats not all. Also, I need the drawing number to be bolded and the Primary and Alternates to be italicized when I add them to the BOM table. If possible, I'd also like to concatenate the Drawing number into every part's (Primary & Alternates) description field.

So three problems here.

Ideas?

**CJ_London** · 06-04-2015, 06:12 PM

you need to simplify what you are asking, no one will understand your code. 'first few lines' helps - but why not highlight them in some way so it is clear which lines you are talking about. Then provide some sample data of what you have at the moment and what you want as a result - much easier for people to visualise.

with regards bold and italicizing in the table, this is not an option - use conditional formatting on your form or report. The format option in a table is for upper/lower case, numerical/date formats etc.

**June7** · 06-04-2015, 06:34 PM

Adds this data where? Exactly how are records added? I don't see any code that saves data to tables, just pulls data.

Why save the Drawing Number concatenated with description? Calculate this concatenation on report.

**gaker10** · 06-04-2015, 08:01 PM

My apologies. I am a little rusty to the forums.

The highlighted portion of the after update combobox function:

Code:

Private Sub cboComponent_AfterUpdate()
Dim strPN As String


'Replace drawing number with Primary part if the entry is a drawing
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")


'If the string is not empty
If strPN <> "" Then
'this is a DRAWING, set component value and corresponding information
Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")
Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.Revision = DLookup("Revision", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")


'Load AlternateA into the drop down
On Error Resume Next
   txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateA; "

'Load AlternateB into the drop down
On Error Resume Next
   txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateB;"

'Load AlternateC into the drop down
On Error Resume Next
   txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateC;"


'If this is not a drawing, its an assembly/kit or regular part with possible alternates
ElseIf strPN = "" Then


'Compare entry to assembly and kit numbers in the table
strPN = Nz(DLookup("AssemblyKitNumber", "ASSEMBLIESKITS", "[AssemblyKitNumber]='" & Me.cboComponent & "'"), "")


    'If a match is found
    If strPN <> "" Then
            'this is an assembly or kit
            Me.txtComponent = strPN
            Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
            Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
        
        'Load all parts for possible alternates in drop down
           txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                    
            txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                    
            txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
    
    Else
    'This is a part number
    Me.txtComponent = Me.cboComponent
        Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
        Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
              
                            
    
    strPN = Nz(DLookup("AlternateA", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
    
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateA " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateA; "
    Else
       txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
    End If
    
    strPN = Nz(DLookup("AlternateB", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateB " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateB; "
    Else
        txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
        End If
        
        strPN = Nz(DLookup("AlternateC", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateC.RowSource = "SELECT ALTERNATES.AlternateC " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateC; "
    Else
        txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
        End If
    
    End If
End If


End Sub

My add function:

Code:

Private Sub butAdd_Click()
'Add entry to list


'Insert vs Update options


'If the part is not in the list
    If Me.ID.Tag & "" = "" Then


'add the part to the list
    
    'Add main part
    CurrentDb.Execute "INSERT INTO BOM(Assembly, Component, Description, AssemblyQty, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & _
     Me.txtCageCode & "')"
    
    
    
    'If there is an AlternateA, Add AlternateA
    If Not (Me.txtAlternateA & "" = "" And Me.txtCageCodeA & "" = "") Then
    CurrentDb.Execute "INSERT INTO BOM(Component, Description, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & " \ALT" & "','" & Me.txtAltAUOM & "','" & Me.txtCageCodeA & "')"
    End If

    'If there is an AlternateB, Add AlternateB
    If Not (Me.txtAlternateB & "" = "" And Me.txtCageCodeB & "" = "") Then
    CurrentDb.Execute "INSERT INTO BOM(Component, Description, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & " \ALT" & "','" & Me.txtAltBUOM & "','" & Me.txtCageCodeB & "')"
    End If

    'If there is an AlternateC, Add AlternateC
    If Not (Me.txtAlternateC & "" = "" And Me.txtCageCodeC & "" = "") Then
    CurrentDb.Execute "INSERT INTO BOM(Component, Description, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAlternateC & "','" & Me.txtAltCDescription & " \ALT" & "','" & Me.txtAltCUOM & "','" & Me.txtCageCodeC & "')"
    End If
    
    Me.numAssemblyQty.SetFocus
    
'update an already existing part


   ElseIf InStr(Me.txtDescription, " \ALT") > 0 Then
   
        CurrentDb.Execute "UPDATE BOM " & _
        " SET Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", CageCode='" & Me.txtCageCode & "'" & _
        " WHERE ID=" & Me.frmEntrySub.Form.ID
        
        Else
        
    CurrentDb.Execute "UPDATE BOM " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", CageCode='" & Me.txtCageCode & "'" & _
        " WHERE ID=" & Me.frmEntrySub.Form.ID
            
            End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

Screenshots:

What happens when I look up a drawing number now:

Click image for larger version.

Name: beforedrawing1.jpg
Views: 16
Size: 97.8 KB
ID: 20928

What happens when I add the drawing's record information A.K.A parts associated with that drawing:

Click image for larger version.

Name: adddrawingtobom.png
Views: 16
Size: 52.6 KB
ID: 20929

What I would like my lookup to do now (I did this manually, so don't worry about the beginning of the descriptions, just the REF part):

Click image for larger version.

Name: desiredresult.png
Views: 16
Size: 57.4 KB
ID: 20932

I need the drawing number concatenated in the description (very similar to the concatenation of " \ALT" in my alternates description in my add function) is because after the records have been added to my BOM table, I export this table to an Excel. I am willing to do this in a report, as long as I can export it to an excel, where it will be copied and pasted into our standard Bill of Materials template. One of my Export functions is this:

Click image for larger version.

Name: exportpopup.png
Views: 16
Size: 13.8 KB
ID: 20931

I am looking at the first check box. I see "MOST" formatting, but this is not possible for what I need? I notice that when I mess with the manual formatting in the ribbon menu, my the formatting is applied to my entire table, headers and all. Is this why conditional formatting doesn't work on tables?

**June7** · 06-05-2015, 12:49 AM

Seems to me, in a normalized data structure, DrawingExample1 would be in one table and then the Parts would be in a related table, something like:

tblDrawings
DrawingID (PK)
DrawingName
DrawingDate

tblDrawingParts
ID
DrawingID (FK)
PartDesc
Assembly
CageCode
Quantity
UOM

**gaker10** · 06-05-2015, 06:28 AM

They are. Drawing numbers come from the DRAWINGS table. This is my current structure:

DRAWINGS table:
DrawingNumber
PrimaryPart
AlternateA
AlternateB
AlternateC

PrimaryPart, AlternateA, AlternateB, and AlternateC are all part of the PartsDatabase table, which is structured like this:

PartNumber
Description
StockUOM
PurchaseUOM

PrimaryPart and the alternates are all "PartNumbers".

Is this what you mean, June?

**June7** · 06-05-2015, 08:17 AM

So those tables have a many-to-many relationship? That requires a third (junction) table that associates drawings with parts:

tblDrawingParts
DrawingNumber (FK)
PartNumber (FK)

**gaker10** · 06-05-2015, 11:28 AM

Sort of. The Drawing number is unique in it's table, and the part numbers are unique in their table. However, many drawings can refer to the same part, and many parts can be on the same drawing. So in a sense they do have a many to many relationship.

The junction table you are referring to here is the DRAWINGS table. It contains the drawing numbers and associated parts.

**June7** · 06-05-2015, 11:47 AM

Then I don't understand your form image where the drawing number and part numbers are in separate records of the same dataset in the subform.

Conventional form/subform arrangement would have main form bound to Drawings table and subform bound to junction table with a combobox to select part. The form/subform linking would automatically save Drawings PK as FK in subform record.

**gaker10** · 06-05-2015, 12:24 PM

The form is for an empty table where a BOM (Bill of Materials) is created. The information entered into this bom is drawn from the drawings and parts tables.

Not all entries on a BOM are drawing numbers. Some of them are just part numbers. So, this form looks at both tables to determine if an entry is a drawing number or part number.

**June7** · 06-05-2015, 12:38 PM

Okay, that helps somewhat.

If you want to concatenate part and drawing descriptions for saving (as opposed to saving just IDs), then hold the drawing info in a variable and use that in code - reference to the main form combobox can be the variable.

**gaker10** · 06-05-2015, 02:29 PM

Thank you.

I think I have an idea of what the code should be. My main problem is where to put it. I think it should go in the add function, yes?

**June7** · 06-05-2015, 02:40 PM

Sounds reasonable from what I see happening in the code.

**gaker10** · 06-05-2015, 05:11 PM

My second issue is how do I code it so that it only concatenates the REF in the description IF the number in the combobox is from the DRAWINGS table. Also, I would have to add the drawing number as shown in the second to last screenshot of post #4.

**June7** · 06-05-2015, 05:54 PM

Can use an IIf() expression. How do you distinguish between a drawing number and other numbers? What is the combobox RowSource?

Something like:

" VALUES ('" & Me.txtAlternateC & "','" & Me.txtAltCDescription & IIf({condition here}, ", REF: " & Me.comboxname, "") & " \ALT" & "','" & Me.txtAltCUOM & "','" & Me.txtCageCodeC & "')"

Apply formatting to a record after adding to a table under certain conditions

Thread Tools

Apply formatting to a record after adding to a table under certain conditions

Similar Threads

Apply formatting to query results

Apply Conditional Formatting to blank controls on Form

how to apply Conditional Formatting in run time

Conditional Formatting with more than 3 conditions

code to apply control formatting

Posting Permissions