Also, before I had the component text box autofilling the primary when a drawing number is typed in the combo. When I changed it to autofill the combo and the text box, I get a "run-time error '-2147352567 (80020009)': The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field."
Code:
Private Sub cboComponent_BeforeUpdate(Cancel As Integer)
Dim strPN As String
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")
If strPN <> "" Then
'this is a DRAWING, set combobox value
Me.cboComponent = strPN
'Me.txtComponent = strPN
End If
End Sub
UPDATE: I used cascading comboboxes to get my alternates to work. But now it wont autofill description and UOM for my alternates:
Code:
Private Sub cboComponent_AfterUpdate()
On Error Resume Next
txtAlternateA.RowSource = "Select DRAWINGS.AlternateA " & _
"FROM DRAWINGS " & _
"WHERE DRAWINGS.Primary = '" & txtComponent & "' " & _
"ORDER BY DRAWINGS.AlternateA;"
On Error Resume Next
txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
"FROM DRAWINGS " & _
"WHERE DRAWINGS.Primary = '" & txtComponent & "' " & _
"ORDER BY DRAWINGS.AlternateB;"
End Sub
I understand why it wont autofill, because the row source changed when I changed which table to look at, but I'm not sure how to change it back for the autofill without making the drop down disappear completely (which happened when I tried to change the row source back to CADatabase). My regular component description and UOM wont autofill because it is dependent on my combobox entry, which I had the above run time error due to the red highlighted code. I tried Me.cboComponent.Value = strPN, Me.cboComponent = Me.txtComponent (after text box change), and every possible variation I could think of.
UPDATE2: I used DLookup to solve my autofill problem. A detour around changing row sources.
Code:
Private Sub txtAlternateA_Change()
Me.txtAltADescription = DLookup("Description", "CADatabase", "[PartNumber] = '" & Me.txtAlternateA & "'")
Me.txtAltAUOM = DLookup("PurchaseUOM", "CADatabase", "[PartNumber] = '" & Me.txtAlternateA & "'")
End Sub
Private Sub txtAlternateB_Change()
Me.txtAltBDescription = DLookup("Description", "CADatabase", "[PartNumber] = '" & Me.txtAlternateB & "'")
Me.txtAltBUOM = DLookup("PurchaseUOM", "CADatabase", "[PartNumber] = '" & Me.txtAlternateB & "'")
End Sub
So my next step is to add this to my table in a certain order. So if I have everything filled out, I want Assembly, Component, Description, AssemblyQty, and UOM to be added on the first record, then AlternateA and its Description and UOM to be added to the next record, then AlternateB to the third record. So if I have this:
I want my table to look something like this:
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. So like this:
This is my add code (attached to a button) right now:
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, PriceA, PriceB, PriceC) " & _
" VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
'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
Would I use separate INSERT INTO statements for AlternateA and AlternateB?
UPDATE3: Separate INSERT INTO's worked
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 & "')"
CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
" VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & "','" & Me.txtAltAUOM & "')"
CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
" VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & "','" & Me.txtAltBUOM & "')"
'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