PLEASE HELP WITH CODES

[jhazziejhazz]

I have this form "Inventory Data Entry Form" linked to a sheet named INVENTORYIN, However in this form, the Part NUmber dropdownlist is from other worksheet named "Product_Masterlist"

Please help me with the codes. I manage to get the Description and Cost auto populated when I click an item in the Part Number(source is from another worksheet named "product_masterlist) dropdownlist, however, I am having an error when I I try to click the add button (see codes in bold letters)


I am using these codes:

Private Sub cmbPartNo_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Masterlist")

Dim i As Integer

If Me.cmbPartNo.Value = i Then
Else


Me.txt_Description.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 2, 0)
Me.txt_Cost.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 3, 0)

End If


End Sub

and this for the add button

Private Sub cmdAdd_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("INVENTORYIN")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

'===================================Validation===========================================

If Me.txtRRNo.Value = "" Then
MsgBox "Please Input Receiveing Report Reference Number"
Exit Sub
End If

If Me.txtSupplierInvoice.Value = "" Then
MsgBox "Please Indicate Suppliers Invoice Reference Number"
Exit Sub
End If


'========================================================================================

sh.Range("A" & lr + 1).Value = "=ROW()-1"
sh.Range("B" & lr + 1).Value = Me.txtRRNo.Value
sh.Range("C" & lr + 1).Value = Me.txtSupplierInvoice.Value
sh.Range("D" & lr + 1).Value = Me.txtTransdate.Value
sh.Range("E" & lr + 1).Value = Me.cmbOrderType.Value
sh.Range("F" & lr + 1).Value = Me.cmbPartNo.Value
sh.Range("G" & lr + 1).Value = Me.txt_Description
sh.Range("H" & lr + 1).Value = Me.txt_Cost.Value
sh.Range("I" & lr + 1).Value = Me.txt_Qty.Value
sh.Range("J" & lr + 1).Value = Me.txt_Cost.Value * Me.txt_Qty.Value


''''''' Clear Boxes ''''''

Me.txtRRNo.Value = ""
Me.txtSupplierInvoice.Value = ""
Me.txtTransdate.Value = ""
Me.cmbOrderType.Value = ""
Me.cmbPartNo.Value = ""
Me.txt_Description = ""
Me.txt_Cost.Value = ""
Me.txt_Qty.Value = ""
Me.txt_Total.Value = ""


Call Show_Data

MsgBox "Data has been added to the database", vbInformation


End Sub



Than you so much for those who will take time to help me. You guys are all amazing.

 

[mart37]

I am having an error when I I try to click the add button (see codes in bold letters)

The error is in cmbPartNo_Change() not cmdAdd_Click().
What is the error?
I have no problems with both lines.

 

[jhazziejhazz]

The error actually comes up when I try to add new data using the form, and when I click ADD button, an error will come up, and when I click the debug, it will lead me to these codes...

Me.txt_Description.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 2, 0)
Me.txt_Cost.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 3, 0)

 

[mart37]

When you clear the boxes the change-event is triggered but because the combobox is empty the vlookup gives an error.
If Me.cmbPartNo.Value = i Then ==> If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then

 

[jhazziejhazz]

When you clear the boxes the change-event is triggered but because the combobox is empty the vlookup gives an error.
If Me.cmbPartNo.Value = i Then ==> If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then

Sorry for the ignorance, but how should I fix it? Do i need to delete or add something?
Thanks

 

[mart37]

Yes
Replace If Me.cmbPartNo.Value = i Then with If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then

 

[jhazziejhazz]

Yes
Replace If Me.cmbPartNo.Value = i Then with If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then

Oh god its perfect.
Thank you so much.

One more thing, when I added the data, my column heads was replaced by the new data I just added... (please see attached image)

 

[mart37]

You are using two different names for last row: lr and Last_Row

lr= WorksheetFunction.Max(Application.WorksheetFunction.CountA(sh.Range("A:A")), 1)