Creating A Form
 

I have created a simple inventory form. I am not very familiar with VBE, but
found a simple code I can use to run it.

How do I add to the code to sum the total of parts available, i.e.
=sumif(a2:A10="New entry")?

Here is the form layout:

Part No:
Location:
Date Received:
Number Recieved:
Amount On Hand: (Need formula)

Here is the code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub

Thanks for all your help!
Johnnie

Creating A Form
 

create a label on your form, and everytime you add an item, update the
label;

example code:
result = Application.WorksheetFunction.Sum( <= add your arguments here
for what you want to sum,
YourForm.lblCalculated.Caption = "Value is : " & result


"Johnnie" wrote in message
...
I have created a simple inventory form. I am not very familiar with VBE,
but
found a simple code I can use to run it.

How do I add to the code to sum the total of parts available, i.e.
=sumif(a2:A10="New entry")?

Here is the form layout:

Part No:
Location:
Date Received:
Number Recieved:
Amount On Hand: (Need formula)

Here is the code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub

Thanks for all your help!
Johnnie

Creating A Form
 

Tieski,
Thanks for the response. However, I realized I did not phrase the request
for assistance correctly.

I need help in creating a new line on the that, when a new order is entered,
the code will search to see if the part number already exists and the do
calculation for only those line items which contain that part number and give
a total on hand.

If you think this is to complicated for a novice like me, please let me know
and I will just work with the spreadsheet view.

Once again, thanks for all your help.

"Tieske" wrote:

create a label on your form, and everytime you add an item, update the
label;

example code:
result = Application.WorksheetFunction.Sum( <= add your arguments here
for what you want to sum,
YourForm.lblCalculated.Caption = "Value is : " & result


"Johnnie" wrote in message
...
I have created a simple inventory form. I am not very familiar with VBE,
but
found a simple code I can use to run it.

How do I add to the code to sum the total of parts available, i.e.
=sumif(a2:A10="New entry")?

Here is the form layout:

Part No:
Location:
Date Received:
Number Recieved:
Amount On Hand: (Need formula)

Here is the code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub

Thanks for all your help!
Johnnie