Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2011
    Posts
    7

    Unanswered: Display Bill last number

    Hi,

    I has a table with bill number.
    Then I has a form. I want to design the form in a way that when the user enter the bill number, theu know that what is the last bill number being key in.
    Pls advise.
    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "Last bill number" according to what? The most recent, the one with the greater number (provided it's numeric, which you don't specify), the last in an ordered or unordered rowset, another criteria that determines that a specific row is the last one?

    You could probably use a domain function, such as DMAX(), or a SQL SELECT with a specific clause, such as TOP (1), MAX(...), etc. and open a RecordSet with it, or you could open a RecordSet and use the MoveLast method and fetch the Id of the current row. However you need first to specify how you determine which is the last record.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dmax or a select top would be the way to go
    a move last is unlikely to be the fastest, and it also depends ont he characteristics of the underlying recordset.

    if you need to guarantee the last value then youneed to take into consideration other things if you are in a multi user db
    you may need to write the record without the bill number
    then get a read lock on the table
    find the current highest bill number
    update your new record
    then release the lock
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2011
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    "Last bill number" according to what? The most recent, the one with the greater number (provided it's numeric, which you don't specify), the last in an ordered or unordered rowset, another criteria that determines that a specific row is the last one?

    You could probably use a domain function, such as DMAX(), or a SQL SELECT with a specific clause, such as TOP (1), MAX(...), etc. and open a RecordSet with it, or you could open a RecordSet and use the MoveLast method and fetch the Id of the current row. However you need first to specify how you determine which is the last record.
    Thank you for your reply. But I need the output in form.
    I has a a table with Bil number column. Example, the last bill number is 2499.
    Then the user need to create a new bill with bill number 2500.
    in order to know the new bill number is 2500, I need to has a output of the last bill number (2499) somewhere in the form of ms accesswhere the staff generate new bill.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so use dmax or select top
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2011
    Posts
    7
    Quote Originally Posted by healdem View Post
    so use dmax or select top
    Thank you for the reply.
    I am now at the form of ms access. I has add a text box in the form, to display the last bill number. I has decided to use dmax as advise. I am at the build event of the text month which allow me to add in the code.
    Can you advise which part of the code should I add in and would you mind teaching me how to write the code? The table in ms access of the bill number is name: Table_Billing 2010.
    I am sorry, I am new to MS access.
    Thanks in advance.


    Code:
    Option Compare Database
    
    Private Sub Command17_Click()
    On Error GoTo Err_Command17_Click
    
        Dim stDocName As String
    
        stDocName = "Invoice 2010"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command17_Click:
        Exit Sub
    
    Err_Command17_Click:
        MsgBox Err.Description
        Resume Exit_Command17_Click
        
    End Sub
    Private Sub Command18_Click()
    On Error GoTo Err_Command18_Click
    
    
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Exit_Command18_Click:
        Exit Sub
    
    Err_Command18_Click:
        MsgBox Err.Description
        Resume Exit_Command18_Click
        
    End Sub
    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click
    
    
        DoCmd.Close
    
    Exit_Command19_Click:
        Exit Sub
    
    Err_Command19_Click:
        MsgBox Err.Description
        Resume Exit_Command19_Click
        
    End Sub
    Private Sub Command20_Click()
    On Error GoTo Err_Command20_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Bill Number Allocation"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Exit_Command20_Click:
        Exit Sub
    
    Err_Command20_Click:
        MsgBox Err.Description
        Resume Exit_Command20_Click
        
    End Sub
    Private Sub Command21_Click()
    On Error GoTo Err_Command21_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Out of Pocket Expense"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command21_Click:
        Exit Sub
    
    Err_Command21_Click:
        MsgBox Err.Description
        Resume Exit_Command21_Click
        
    End Sub
    
    Private Sub Text28_BeforeUpdate(Cancel As Integer)
    
    End Sub
    
    Private Sub Update_OPE_Click()
    On Error GoTo Err_Update_OPE_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Table_Out of Pocket expense - Search"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Update_OPE_Click:
        Exit Sub
    
    Err_Update_OPE_Click:
        MsgBox Err.Description
        Resume Exit_Update_OPE_Click
        
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try:-
    Google
    or the access help system
    press F1
    or use the context sensitive abilities of the IDE
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2011
    Posts
    7
    Quote Originally Posted by healdem View Post
    try:-
    Google
    or the access help system
    press F1
    or use the context sensitive abilities of the IDE
    Hi,

    Thank for the suggestion. I has try using the code below, but it cannot work.

    DMax("Bill Number", "Table_billing 2010", "Billing Year = 2010")

    Bill Number = DMAX("Bill Number","Table_billing 2010")+1

    I ha add a text box on my form to output the max bill number, but nothing happen.
    Code:
    Option Compare Database
    
    Private Sub Command17_Click()
    On Error GoTo Err_Command17_Click
    
        Dim stDocName As String
    
        stDocName = "Invoice 2010"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command17_Click:
        Exit Sub
    
    Err_Command17_Click:
        MsgBox Err.Description
        Resume Exit_Command17_Click
        
    End Sub
    Private Sub Command18_Click()
    On Error GoTo Err_Command18_Click
    
    
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Exit_Command18_Click:
        Exit Sub
    
    Err_Command18_Click:
        MsgBox Err.Description
        Resume Exit_Command18_Click
        
    End Sub
    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click
    
    
        DoCmd.Close
    
    Exit_Command19_Click:
        Exit Sub
    
    Err_Command19_Click:
        MsgBox Err.Description
        Resume Exit_Command19_Click
        
    End Sub
    Private Sub Command20_Click()
    On Error GoTo Err_Command20_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Bill Number Allocation"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Exit_Command20_Click:
        Exit Sub
    
    Err_Command20_Click:
        MsgBox Err.Description
        Resume Exit_Command20_Click
        
    End Sub
    Private Sub Command21_Click()
    On Error GoTo Err_Command21_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Out of Pocket Expense"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command21_Click:
        Exit Sub
    
    Err_Command21_Click:
        MsgBox Err.Description
        Resume Exit_Command21_Click
        
    End Sub
    
    Private Sub Text28_BeforeUpdate(Cancel As String)
    DMax("Bill Number", "Table_billing 2010", "Billing Year = 2010")
    End Sub
    
    Private Sub Update_OPE_Click()
    On Error GoTo Err_Update_OPE_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Table_Out of Pocket expense - Search"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Update_OPE_Click:
        Exit Sub
    
    Err_Update_OPE_Click:
        MsgBox Err.Description
        Resume Exit_Update_OPE_Click
        
    End Sub

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you have made life harder for yourself by using spaces in table and or column names. the problem is the SQL engine cannot understand where a table or column names starts and a stops.

    try
    Code:
    DMax("[Bill Number]", "[Table_billing 2010]", "[Billing Year] = 2010")
    that assumes that [Billing Year] is numeric, if it was string then it would need quotes around the literal value eg
    Code:
    DMax("[Bill Number]", "[Table_billing 2010]", "[Billing Year] = '2010'")
    do yourself a favour and drop this approach as soon as. whether you choose to use CamelCase or underscores doesn't matter. people argue to toss either way but effectively its about legibility. if you work in a mixed environment eg server on *nix and microsoft clients then there can be problems of capitalisation favouring using all lower case names and that would make the underscore favoured
    eg
    CamelCase
    Code:
    DMax("BillNumber", "TableBilling 2010", "BillingYear = 2010")
    Underscore
    Code:
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")
    just seeing a table or query name of [Table_billing 2010] sends shivers down my normalised spine.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2011
    Posts
    7
    Quote Originally Posted by healdem View Post
    you have made life harder for yourself by using spaces in table and or column names. the problem is the SQL engine cannot understand where a table or column names starts and a stops.

    try
    Code:
    DMax("[Bill Number]", "[Table_billing 2010]", "[Billing Year] = 2010")
    that assumes that [Billing Year] is numeric, if it was string then it would need quotes around the literal value eg
    Code:
    DMax("[Bill Number]", "[Table_billing 2010]", "[Billing Year] = '2010'")
    do yourself a favour and drop this approach as soon as. whether you choose to use CamelCase or underscores doesn't matter. people argue to toss either way but effectively its about legibility. if you work in a mixed environment eg server on *nix and microsoft clients then there can be problems of capitalisation favouring using all lower case names and that would make the underscore favoured
    eg
    CamelCase
    Code:
    DMax("BillNumber", "TableBilling 2010", "BillingYear = 2010")
    Underscore
    Code:
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")
    just seeing a table or query name of [Table_billing 2010] sends shivers down my normalised spine.
    Hi,

    Thank you. I has try insert code below. but still cannot work.
    Code:
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")



    Code:
    Option Compare Database
    
    Private Sub Command17_Click()
    On Error GoTo Err_Command17_Click
    
        Dim stDocName As String
    
        stDocName = "Invoice 2010"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command17_Click:
        Exit Sub
    
    Err_Command17_Click:
        MsgBox Err.Description
        Resume Exit_Command17_Click
        
    End Sub
    Private Sub Command18_Click()
    On Error GoTo Err_Command18_Click
    
    
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Exit_Command18_Click:
        Exit Sub
    
    Err_Command18_Click:
        MsgBox Err.Description
        Resume Exit_Command18_Click
        
    End Sub
    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click
    
    
        DoCmd.Close
    
    Exit_Command19_Click:
        Exit Sub
    
    Err_Command19_Click:
        MsgBox Err.Description
        Resume Exit_Command19_Click
        
    End Sub
    Private Sub Command20_Click()
    On Error GoTo Err_Command20_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Bill Number Allocation"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Exit_Command20_Click:
        Exit Sub
    
    Err_Command20_Click:
        MsgBox Err.Description
        Resume Exit_Command20_Click
        
    End Sub
    Private Sub Command21_Click()
    On Error GoTo Err_Command21_Click
    
        Dim stDocName As String
    
        stDocName = "Query_Out of Pocket Expense"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command21_Click:
        Exit Sub
    
    Err_Command21_Click:
        MsgBox Err.Description
        Resume Exit_Command21_Click
        
    End Sub
    
    Private Sub Text28_BeforeUpdate(Cancel As String)
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")End Sub
    
    Private Sub Text32_BeforeUpdate(Cancel As Integer)
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")End Sub
    
    Private Sub Update_OPE_Click()
    On Error GoTo Err_Update_OPE_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Table_Out of Pocket expense - Search"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Update_OPE_Click:
        Exit Sub
    
    Err_Update_OPE_Click:
        MsgBox Err.Description
        Resume Exit_Update_OPE_Click
        
    End Sub

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Nayan View Post
    Hi,

    Thank you. I has try insert code below. but still cannot work.
    Code:
    DMax("bill_number", "table_billing_2010", "billing_year] = 2010")
    so do we assume you have renamed your columns and tables to remove any spaces and replaced with either CamelCase or underscores?

    if you use a terminating ] then you must have a [ at the start of that column or table name.

    you need to assign the value of DMAX to a varaible / control
    http://www.techonthenet.com/access/f...omain/dmax.php
    eg
    myvariable = DMax("bill_number", "table_billing_2010", "billing_year] = 2010")
    Last edited by healdem; 12-15-11 at 06:49.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •