Ken Snell said:
Not sure what you mean by "set the sub form to addnew". AddNew is a
method
used by recordsets, and typically is not a property set for a subform.
Please give us more details about how you get to the subform and the code
that you're running before and after data entry.
--
Ken Snell
<MS ACCESS MVP>
I came across a problem that I have set the sub form to addnew and
continuous
form, however when entering data in the subform and reaching the last
field
the next tab takes me to the beginning of the line I just typed ( I
checked
the sub record and it gets written). I really want to get a new line
and
be
able to see all the records entered in continuous form mode. Any
insights
would be greatly appreciated.
Sorry - Let me more specific.
The main form is bound to a table called OrderMaster, it looks up a
customer and assigns an invoice number. The sub form is Bound to a table
called OrderSub, it allows the user to lookup a product number and finds
the
correct price to use from another table - user enters quantity and it
calculates the total price for that item.
The mainform properties are allow additions, allow edits, allow deletions,
data entry = yes, Cycle = all records. Sub Form properties are allow
additions, allow edits, allow deletions, data entry = yes, cycle = all
records.
The form and subform were created via Wizard and worked fine. As luck
would
have it - I had to make some changes and it does not want to work.
Since the last post I created a new Form and had the same problem - so
data
related in some way?
More on symptoms - When I load the main form all controls work perfectly
and
the record gets created in OrderMast tab at last field takes me to
subForm,
all controls work fine on sub form at last field sub form should start a
new
line - it skips back to the first field on the same line - giving me blank
fields. When I check the OrderSub the record was written. So basically it
looks as though it would if additions aren't allowed but the record gets
written.
Hope you can help - I am totally mysified.
I will attach code for the sub form:
Option Compare Database
Option Explicit
Private Sub cmbProductCode_LostFocus()
Me.txtProductName.Value = Me.cmbProductCode.Column(1)
Me.DiscountForItem.Enabled = True
End Sub
Private Sub ItemQuantity_Exit(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim rsSpecial As ADODB.Recordset
Dim rsCust As ADODB.Recordset
Dim rsProd As ADODB.Recordset
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim frm1 As Form
Dim lngDiscount As Currency
Dim intDiscount As Long
Dim cmd1 As ADODB.Command
Dim intPrice As Currency
Dim intCalc As Currency
Dim intProfit As Currency
Dim intPriceCode As Integer
Set frm1 = Forms("frmOrder")
Set cn = CurrentProject.Connection
Set rsTemp = New ADODB.Recordset
Set rsCust = New ADODB.Recordset
Set rsSpecial = New ADODB.Recordset
Set rsProd = New ADODB.Recordset
Set cmd1 = New ADODB.Command
rsTemp.CursorType = adOpenKeyset
rsTemp.LockType = adLockOptimistic
rsProd.CursorType = adOpenKeyset
rsProd.LockType = adLockOptimistic
rsSpecial.CursorType = adOpenKeyset
rsSpecial.LockType = adLockOptimistic
rsCust.CursorType = adOpenKeyset
rsCust.LockType = adLockOptimistic
' Write a temp record containing cust and prod
rsTemp.Open "tblTempSP", cn, , , adCmdTableDirect
rsTemp.AddNew
rsTemp("CustomerName") = frm1.cmbCustomerName
rsTemp("ProductCode") = Me.cmbProductCode
rsTemp.Update
Debug.Print "Created a temp record "
'Set up sql string to access special price table
strSQL = "SELECT SpecialPrice.CustomerName, SpecialPrice.ProductCode,
SpecialPrice.SpecialPrice" & _
" FROM SpecialPrice, tblTempSP" & _
" WHERE (((SpecialPrice.CustomerName)=[tblTempSP].[CustomerName]) AND
((SpecialPrice.ProductCode)=[tblTempSP].[ProductCode]))"
'look for special price - if one exists use it to get price
rsSpecial.Open strSQL, cn, , , adCmdTableDirect
If rsSpecial.BOF = True And rsSpecial.EOF = True Then GoTo
CalculateDiscount
'Update ordermast totals with special price and disable discount field
lngDiscount = rsSpecial("SpecialPrice")
Me.ItemTotal = (lngDiscount * Me.ItemQuantity)
Me.ItemPrice = lngDiscount
Me.DiscountForItem.Enabled = False
Debug.Print "updated totals for Special Price"
GoTo CleanUp
' No Special Price found so Read cust and retreive pricecode and customer
discount
CalculateDiscount:
strSQL1 = "SELECT CustomerMaster.CustomerName,
CustomerMaster.DiscountPercent, CustomerMaster.PriceCode" & _
" FROM tblTempSP INNER JOIN CustomerMaster ON tblTempSP.CustomerName =
CustomerMaster.CustomerName" & _
" WHERE (((CustomerMaster.CustomerName)=[tblTempSP]![CustomerName]))"
rsCust.Open strSQL1, cn, , , adCmdTableDirect
intDiscount = rsCust("DiscountPercent")
Me.DiscountForItem = rsCust("DiscountPercent")
intPriceCode = rsCust("PriceCode")
rsCust.Close
Debug.Print "read the cust because there was no Special price"
' After cust read product and get price and check to see if discount is
allowed.
strSQL2 = "SELECT ProductMaster.ProductCode, tblTempSP.ProductCode,
ProductMaster.UnitCost, ProductMaster.DiscountAllowed,
ProductMaster.NJPrice,
ProductMaster.EastCoastPrice, ProductMaster.StorePrice,
ProductMaster.VendingPrice, ProductMaster.MasterVendingPrice" & _
" FROM ProductMaster INNER JOIN tblTempSP ON ProductMaster.ProductCode =
tblTempSP.ProductCode" & _
" WHERE (((tblTempSP.ProductCode)=[ProductMaster].[Productcode]))"
rsProd.Open strSQL2, cn, , , adCmdTableDirect
Debug.Print "right after cust read, read prod"
'Choose correct Price
Select Case intPriceCode
Case 1
intPrice = rsProd("NJPrice")
Case 2
intPrice = rsProd("EastCoastPrice")
Case 3
intPrice = rsProd("StorePrice")
Case 4
intPrice = rsProd("VendingPrice")
Case 5
intPrice = rsProd("MasterVendingPrice")
Case 6
intPrice = rsProd("SparePrice1")
Case 7
intPrice = rsProd("SparePrice2")
End Select
'test if discount allowed
If rsProd("DiscountAllowed") = False Then GoTo NoDiscount
intCalc = ((intPrice * Me.ItemQuantity) * (intDiscount / 100))
Me.ItemTotal = (intPrice * Me.ItemQuantity) - intCalc
Me.ItemPrice = intPrice
GoTo UpdateTotals
NoDiscount:
Me.ItemPrice = intPrice
UpdateTotals:
frm1.OrderAmountTotal = frm1.OrderAmountTotal * (Me.ItemQuantity +
intPrice)
frm1.UnitCostTotal = frm1.UnitCostTotal + (rsProd("UnitCost") *
Me.ItemQuantity)
intProfit = (frm1.OrderAmountTotal - frm1.UnitCostTotal)
frm1.OrderMargin = (frm1.UnitCostTotal / intProfit)
frm1.CurrentBalance = frm1.CurrentBalance + frm1.OrderAmountTotal
rsProd.Close
GoTo CleanUp
CleanUp:
rsTemp.Close
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "DELETE * FROM tblTempSP"
.CommandType = adCmdText
.Execute
End With
rsSpecial.Close
Set cn = Nothing
Set rsTemp = Nothing
Set rsSpecial = Nothing
Set rsProd = Nothing
Set rsCust = Nothing
Set cmd1 = Nothing
End Sub
Private Sub txtProductName_GotFocus()
Me.ItemQuantity.SetFocus
End Sub
