New Record Problem

[Bob Vance]

When I open a new record I am not gettting the next number if I select " If
fraSelectInvoiceNoType = 1 "
I am getting a zero , Thanks for any help.............Bob

If Me.NewRecord = True Then
If fraSelectInvoiceNoType = 1 Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
ElseIf fraSelectInvoiceNoType = 2 Then
recInvoice.Fields("InvoiceNo") = 0
End If

 

[Tom Wickerath]

Hi Bob,

What do you get if you issue this command directly from the Immediate Window?

?Nz(DMax("InvoiceNo", "tblInvoice"), 0) + 1

Open the Immediate Window by pressing the Control and G keys at the same
time, ie. <Ctrl><G>.

Also, are you sure that you haven't already saved the record at this point
in time? If you have, then Me.NewRecord will evaluate to False. You might try
inserting a break point, and single stepping through the code using the F8
key.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Bob Vance]

Yes Tom, I think you are right The Invoice is already saved so it just keeps
changing the [fraSelectInvoiceNoType ] Because this is part of
SubSetInvoiceValues

Sub subSetValues()
Dim recOwnersInfo As New ADODB.Recordset
Dim dblTotal As Double
Dim dblOwnerPercentAmount As Double
Dim recTmpOwner As New ADODB.Recordset
recTmpOwner.Open "SELECT CompanyID FROM tblCompanyInfo WHERE CompanyName
LIKE '" _
& tbCompanyName.value & "'", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

recOwnersInfo.Open "Select * from tblOwnerInfo where OwnerID=" &
cbOwnerName.Column(0), _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With recInvoice
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"), 0)
.Fields("InvoiceID") = tbInvoiceID.value
.Fields("ClientInvoice") = True
.Fields("HorseID") = 0
.Fields("HorseName") = ""
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value
.Fields("ClientDetail") = tbClientDetail.value
If tbDateOfBirth.value = "" Or IsNull(tbDateOfBirth.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDateOfBirth.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDateOfBirth.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & "-" &
tbSex.value
End If

.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = cbGSTOptions.value


.Fields("GSTOptionsValue") = IIf(tbGSTOptionsValue = "" Or
IsNull(tbGSTOptionsValue), 0, tbGSTOptionsValue)
.Fields("SubTotal") = IIf(tbSubTotal = "" Or IsNull(tbSubTotal), 0,
tbSubTotal)

.Fields("TotalAmount") = IIf(tbTotalAmount = "" Or
IsNull(tbTotalAmount), 0, tbTotalAmount)

'************This was original code that keeped adding Invoice
number each time you opened the form*******
If fraSelectInvoiceNoType = 1 Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
ElseIf fraSelectInvoiceNoType = 2 Then
recInvoice.Fields("InvoiceNo") = 0

End If
'************************************************************************************

If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value) Then
Else
.Fields("InvoiceDate") = Format(CDate(tbInvoiceDate.value),
"dd/mm/yyyy")
End If

If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True Then
Else
.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") =
IIf(IsNull(recOwnersInfo.Fields("OwnerTitle")), "",
recOwnersInfo.Fields("OwnerTitle") & " ") _
& IIf(IsNull(recOwnersInfo.Fields("OwnerFirstName")), "",
recOwnersInfo.Fields("OwnerFirstName") & " ") _
& IIf(IsNull(recOwnersInfo.Fields("OwnerLastName")), "",
recOwnersInfo.Fields("OwnerLastName") & " ")
.Fields("OwnerAddress") = recOwnersInfo.Fields("OwnerAddress")

End If

dblTotal = IIf(tbTotalAmount.value = "" Or
IsNull(tbTotalAmount.value), 0, val(tbTotalAmount.value))

dblOwnerPercentAmount = dblTotal
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

.Fields("OwnerPercent") = 1
dblGSTContentsValue = (dblOwnerPercentAmount / 9)

.Fields("GSTContentsValue") = dblGSTContentsValue
If dblGSTContentsValue > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf dblGSTContentsValue < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End With

End Sub

 

[Bob Vance]

This is the [Close] Button Code..........Thanks Bob

Private Sub cmdClose_Click()
On Error GoTo Err_Command80_Click
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = DateSerial(Year(Date), Month(Date) + 1, 1)


End If

If Len([cbOwnerName]) = 0 Then
MsgBox "Please Select The Horse Name From The List.",
vbApplicationModal + vbInformation + vbOKOnly, "Intellisoft"
Exit Sub
End If

If Len([cbGSTOptions]) = 0 Then
MsgBox "Please Select The GST Options From The List.",
vbApplicationModal + vbInformation + vbOKOnly, "Intellisoft"
Exit Sub
End If


If bModify = True Then
CurrentProject.Connection.Execute "DELETE * FROM
tblDailyCharge WHERE InvoiceID=" & tbInvoiceID.value
CurrentProject.Connection.Execute "DELETE * FROM
tblAdditionCharge WHERE InvoiceID=" & tbInvoiceID.value
bModify = False
End If
subSetValues
subSetInvoiceDetailsValues
recInvoice.Update

Set recInvoice = Nothing
Forms!frmModifyInvoiceClient!lstModify.Requery
DoCmd.Close acForm, Me.Name

Exit Sub
Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

End Sub

 

[Bob Vance]

Oh Got it GREAT, Changed the close button to another subSetValues() with
another name and deleted out the Invoice Number Code........Brilliant Im
getting better Thanks.......Bob

 

[Tom Wickerath]

Glad to read that you found a solution.
Happy New Year.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________