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

[gaker10]

Drawing numbers are in the DRAWINGS table. All others are in the PartsDatabase table. How do I put this condition into code?

The combo boxes row source: SELECT [PDatabase].[PartNumber], [PDatabase].[Description], [PDatabase].[PurchaseUOM] FROM [PDatabase] ORDER BY [PartNumber] UNION SELECT [Drawing], Null, Null FROM [DRAWINGS] UNION SELECT [AssemblyKitNumber], Null, Null FROM [ASSEMBLIESKITS];

[June7]

That still doesn't tell me how a drawing number can be distinguished from a part number. Are they different number characters? Does one start with a letter? Exactly how are they different?

But maybe don't need.

There are 3 entities in that combobox RowSource. Do you want the AssemblyKitNumber to be concatenated as well as Drawing number?

Since only part number has values in 2nd and 3rd columns, the IIf() can use that to determine if selected item is a part number. Reference the combobox column by index. Index begins with 0.

" VALUES ('" & Me.txtAlternateC & "','" & Me.txtAltCDescription & IIf(IsNull(Me.combobox.Column(1)), ", REF: " & Me.comboxname, "") & " \ALT" & "','" & Me.txtAltCUOM & "','" & Me.txtCageCodeC & "')"

[gaker10]

As far as the drawing and part numbers themselves, they can be anything.

The AssemblyKitNumber is not in use, so I can literally take that out of the rowsource. It was an experiment that ended up not being suitable with Access.

I tried your code and I (THINK) I adjusted it correctly for all the alternates. It didn't work. See below.

Here's what I got:



Here's what I would like:




Here's the code I modified (I probably did it wrong, hehe):

    '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 & IIf(IsNull(Me.cboComponent.Column(1)), ", REF: " & Me.cboComponent, "") & " \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 & IIf(IsNull(Me.cboComponent.Column(1)), ", REF: " & Me.cboComponent, "") & " \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 & IIf(IsNull(Me.cboComponent.Column(1)), ", REF: " & Me.cboComponent, "") & " \ALT" & "','" & Me.txtAltCUOM & "','" & Me.txtCageCodeC & "')"
    End If

[June7]

Okay, maybe a combobox column can't hold a Null. Maybe the Null fields will be presented as empty string.

IIf(Me.cboComponent.Column(1)="",

[gaker10]

That got the REF: working. Now I just need to add the drawing number as the first record. No description or UOM or anything needed for it.

[June7]

Thought you already have code for that.

[gaker10]

I do not. But I imagine it would be similar to the Iif statement. I can't get the syntax right.

[June7]

Oh, I was looking at the code for 'main part'. The record for Drawing number should be similar. Show attempted code.

Probably want it inside an If Then conditional.

[gaker10]

    'Add drawing number if applicable
          CurrentDb.Execute "INSERT INTO BOM(Assembly, Component) " & _
    " VALUES ('" & Me.txtAssembly & "','" IIf(Me.cboComponent.Column(1) = "", & Me.cboComponent & "'),"")

[June7]

Missing & for concatenation.

However, don't think that's where you need the conditional. What you have will add the record even if the selection is not a drawing number.

If Me.cboComponent.Column(1) = "" Then
'Add drawing number if applicable
CurrentDb.Execute "INSERT INTO BOM(Assembly, Component) " & _
" VALUES ('" & Me.txtAssembly & "','" & Me.cboComponent & "')"
End If

[gaker10]

Works like a charm! Thank you!