Search for last, then add 1 depending on value in another field (1 Viewer)

[veraloopy]

Hi guys

I have a quotes table that will allow the user to create more than one quote for a follow up call to any customer.

What i'm trying to do is have a 'Create New' button on the form that will create a new quote, defaulting the custref and custflwUpID to the same as the quote that is being viewed, but the quotenumber needs to be the next one up in sequence.

I've managed to get this far, but if I press 'Create New' on QuoteNumber 1, when the customer already has more than one quote, it tries to create quote number 2, not realising it already exists.... The problem i'm having is if the customer has 6 quotes and i'm viewing quote number 1, I then want to create quote number 7....

What i need it to do is search the table (tblQuotes) and find the last quote number for this custref and custflwupid then add 1 to it to create a new quote.

Here is my code so far....

Private Sub cmdAddNewRec_Click()
'Takes the custref, the follow up id and quote number and either adds 1 to create next quote or starts from 1
Dim strLinkID As String
Dim strCustID As String
Dim strQuoteNo As String
If Me.QuoteNumber = 0 Or IsNull(QuoteNumber) Then
strCustID = Forms!frmCustomerDetailView!CustRef
strLinkID = Me.CustFlwUpID
strQuoteNo = 1
DoCmd.GoToRecord , , acNewRec
Me.CustFlwUpID = strLinkID
Me.CustRef = strCustID
Me.QuoteNumber = strQuoteNo
Else
strCustID = Forms!frmCustomerDetailView.CustRef
strLinkID = Me.CustFlwUpID
strQuoteNo = Me.QuoteNumber + 1
DoCmd.GoToRecord , , acNewRec
Me.CustFlwUpID = strLinkID
Me.CustRef = strCustID
Me.QuoteNumber = strQuoteNo
End If

End Sub

 

[Brianwarnock]

I think that you are going to need to look at using DMAX

Brian

 

[HiTechCoach]

Hi guys

I have a quotes table that will allow the user to create more than one quote for a follow up call to any customer.

What i'm trying to do is have a 'Create New' button on the form that will create a new quote, defaulting the custref and custflwUpID to the same as the quote that is being viewed, but the quotenumber needs to be the next one up in sequence.

I've managed to get this far, but if I press 'Create New' on QuoteNumber 1, when the customer already has more than one quote, it tries to create quote number 2, not realising it already exists.... The problem i'm having is if the customer has 6 quotes and i'm viewing quote number 1, I then want to create quote number 7....

What i need it to do is search the table (tblQuotes) and find the last quote number for this custref and custflwupid then add 1 to it to create a new quote.

Here is my code so far....

Private Sub cmdAddNewRec_Click()
'Takes the custref, the follow up id and quote number and either adds 1 to create next quote or starts from 1
Dim strLinkID As String
Dim strCustID As String
Dim strQuoteNo As String
If Me.QuoteNumber = 0 Or IsNull(QuoteNumber) Then
    strCustID = Forms!frmCustomerDetailView!CustRef
    strLinkID = Me.CustFlwUpID
    strQuoteNo = 1
    DoCmd.GoToRecord , , acNewRec
    Me.CustFlwUpID = strLinkID
    Me.CustRef = strCustID
    Me.QuoteNumber = strQuoteNo
Else
    strCustID = Forms!frmCustomerDetailView.CustRef
    strLinkID = Me.CustFlwUpID
    strQuoteNo = Me.QuoteNumber + 1
    DoCmd.GoToRecord , , acNewRec
    Me.CustFlwUpID = strLinkID
    Me.CustRef = strCustID
    Me.QuoteNumber = strQuoteNo
End If
    
End Sub

See if this help:

Precious thread