Two tables, one combobox

[gaker10]

Just two*



One thing I'd like to add to distinguish main parts from alternates is I want to add " \ALT" to the end of the description text of alternates automatically when I add them to my table.

Also, If I add a part with an alternate A and NO alternateB, I get a blank line. Is there any way to eliminate records that are blank? DELETE IF statement?

->

[June7]

Concatenation is simple:

Me.txtAlternateA & "\Alt"

Don't save the record if no value:

If Not IsNull(Me.txtAlternateB) Then
'save record
End If

[gaker10]

Where do I place these statements?

[June7]

In your VBA code.

If Not IsNull(Me.txtAlternateB) Then
CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
" VALUES ('" & Me.txtAlternateB & "\AltB" & "','" & Me.txtAltBDescription & "','" & Me.txtAltBUOM & "')"
End If

[gaker10]

I get this:



Add Code:

Private Sub butAdd_Click()


'Insert vs Update options




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


'add the part to the list
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, UnitPriceA, UnitPriceB, UnitPriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
    
    If Not IsNull(Me.txtAlternateA) Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & " \AltB" & "','" & Me.txtAltAUOM & "')"
    End If
    
    If Not IsNull(Me.txtAlternateB) Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & " \AltB" & "','" & Me.txtAltBUOM & "')"
    End If
    
'update an already existing part
    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE Component='" & Me.frmEntrySub!Component & "'"
    End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

Changed \AltB to \Alt, A and B dont really matter here. Didn't make a difference Adding wise of course.

[June7]

Have you step debugged?

[gaker10]

No. I've only done step debugging once years ago in C++.

[gaker10]

Does it matter that txtAlternateA is actually mislabeled, and is a combobox?

[June7]

Review link at bottom of my post for guidelines.

And that answers question I was going to ask. No, the naming convention doesn't matter. Are they actually Null if no value shows?

Don't you want "\AltA" for the AlternateA concatenation?

[gaker10]

AlternateA and AlternateB are only useful for telling access how many records to add in that are different when I make an entry. In the final product, \ALT's are interchangeable.

[June7]

Then why bother with the "A" or "B" designation, just "\Alt"?

[gaker10]

Also, my debugging is not working. When I put my cursor over the line and press F8 or click it on the menu, nothing happens.

[gaker10]

The A and B designation is to help match form fields to table fields.

[June7]

Did you place a breakpoint in the code? Then when code execution pauses on the breakpoint, hover over any variable to see its content. Reference to a control is a variable.

[gaker10]

Like this?