Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010

    Unanswered: Populate Form with concatenate value based on criteria.

    Populate Form with concatenate value based on criteria.

    I am new to access and the Board . I have a button on a form that I need to
    1 Create a new record
    2 Look at the auto number Field (in the DB it ID1)
    3 If the auto number is less than 2 the 00 + ID1 (Example ID1= 1 so it should equal 001 when all is said and done.)
    Else if the = 2 than 0+ ID1 (Example ID1= 10 so it should equal 010 when all is said and done.)
    4 Store the concatenated value in the new active record Field would be ID

    This is the code
    Private Sub New_issue_Click()
    Dim rs As DAO.Recordset
    Dim NewID As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Action_Items WHERE False")
    If LenB(rs.Fields![ID1]) > 2 Then
        NewID = "0" & "rs.Fields![ID]"
    ElseIf LenB(rs.Fields![ID1]) > 1 Then
        NewID = "00" & "rs.Fields![ID]"
    rs.Fields![ID] = NewID
    End If
    End Sub
    I sure I am not even close.
    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    The Format function can just do that:
    NewID = Format(rs!ID, "000")
    The SQL statement you use to open the recordset seems to be incorrect, though (... WHERE False).
    Moreover, ID1 won't have a value when you create a new record until you save it. You should try something like:
    NewID = Format(Nz(DMax("ID1", "Action_Items"), 0) + 1, "000")
    Also be careful when using the "." and "!" operators: they are not similar. See:
    Cleaner Coding: Bang vs. Dot -- It’s time to reconsider an old Access rule. ++ Tags: Code, Microsoft, Microsoft Access, Microsoft Office, Microsoft Windows, Programming, VBA
    Have a nice day!

Posting Permissions

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