Really Need Help with this Function

B

Bob Vance

I have a code so as I can select whether a Invoice has a number or just a
zero and that works fine but when I re-open the invoice if it has a number
it incriminates again on closing, this is the part of my code that is
causing me trouble, if it would only work on a new record only!
Please any help....Bob
--------------------------------------------
If fraSelectInvoiceNoType = 1 Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
ElseIf fraSelectInvoiceNoType = 2 Then
recInvoice.Fields("InvoiceNo") = 0
---------------------------------------
Function NextInvoiceNo() As Long
NextInvoiceNo = Nz(DMax("InvoiceNo", "tblInvoice"), 0) + 1
End Function
-- -----------------------------------------
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3
 
A

Albert D. Kallal

Bob Vance said:
I have a code so as I can select whether a Invoice has a number or just a
zero and that works fine but when I re-open the invoice if it has a number
it incriminates again on closing, this is the part of my code that is
causing me trouble, if it would only work on a new record only!
Click to expand...

Perhaps you try the code in the "before insert" event"

"Inserting" ONLY occurs and fires when a NEW record is being added. So, you
can put your code in the on-insert event. If the user closes the form, then
the record is not added. If the user starts to type in the form, then you
before insert" event fires.

If the user goes back to that record, the before insert event will NEVER
fire again...it only fires for a new record.....

I think the above is the best way to have code distinguish between an
existing record, and a new one.

however, you can also in code go:

if me.NewRecord = True then
' code goes here for new record

else

' code goes here for existing record

end if
 
B

Bob Vance

Thanks Albert but it would not work in Before Event as the record is made up
on closing the form, What would I put in existing record....Thanks Bob

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

' code goes here for existing record

End If
 
B

Bob Vance

Thanks Albert I tried entering my whole code in the top space for new record
then entered the code again minus the set Invoice numbers but it is not
giving me an Invoice number for new record, my code below:

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
If Me.NewRecord = True Then

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)


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
Else

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)



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 If
End Sub
 
B

Bob Vance

Albert I created a text box on my form tbInvoiceNumber and added it to my
code in subBlankForm, It enters the next Invoice number that fine I can add
a control to change the Invoice number to zero if needed, but each time I
open an old Invoice it is adding to Invoice number, does not seem to know
what a NewRecord is
Thanks for any help.....Bob

Private Sub subBlankForm()
tbInvoiceID.value = ""

cbOwnerName.value = ""
tbFatherName.value = ""
tbMotherName.value = ""
tbDateOfBirth.value = ""
tbSex.value = ""

cbGSTOptions.value = "Plus Tax"
Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Dim dblSubTotal As Double, dblTotalAmount As Double
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = dblGSTOptionsValue + dblSubTotal
dblTotalAmount = Round(dblTotalAmount, 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = Nz(recGSTOptions.Fields("GSTPercentage"), 0)


If Me.NewRecord = True Then

tbInvoiceNumber = ""
Else
tbInvoiceNumber = NextInvoiceNo
tbRate.value = sngGstPercentage
tbRate2.value = sngGstPercentage
tbGSTOptionsValue.value = ""
tbSubTotal.value = ""
tbTotalAmount.value = ""
chkByCheque.value = True
End If
End Sub
 
B

Bob Vance

Actually Albert the problem is when I double click on the List box is when
the recoed opens and changes the Invoice Number, can I stop NextInvoiceNo
from here, Thanks Bob

Private Sub lstModify_DblClick(Cancel As Integer)
Select Case Me.OpenArgs
Case "ModifyOldInvoice"
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select Owner.", vbApplicationModal + vbOKOnly
+ vbInformation
Else
If Form_frmActiveHorses.cboClient.value = True Then
DoCmd.OpenForm "frmInvoiceClient", , , , , ,
"ModifyOldInvoice"
Else
DoCmd.OpenForm "frmInvoiceClient", , , , , ,
"ModifyOldInvoice"
End If
End If
Case "ModifyHoldingInvoice"
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select Horse.", vbApplicationModal + vbOKOnly
+ vbInformation
Else
DoCmd.OpenForm "frmInvoiceClient", , , , , ,
"ModifyOldInvoice"
End If

Case Else

DoCmd.Close acForm, "frmActiveHorses"
If lstModify.value = "" Or IsNull(lstModify.value) Then
MsgBox "Please Select a Client Name!", vbApplicationModal +
vbOKOnly + vbInformation
Else
DoCmd.OpenForm "frmInvoiceClient", , , , , ,
"ModifyOldInvoice"
End If

End Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

New Record Problem 5
Incrementing Invoice Number 20
Adding a Condition from Check Box 5
Need Invoice sequential number series 19
Field Size Dilemma 12
Adding True/False condition to my code 2
Create new unique invoice number 3
Ok This one is Tuff 3

Top