I'm not able to format a variable in access vba

[clukem]

Hello

I'm in the process of migrating my database from using Java/MySQL to Access. The problem I have is being able to format the data correctly.



My authors table consists just of an author id and author name. The author id is in the format AU0001. When I need to add a new author, I want to do this automatically. The code I have used (see below) uses SQL to strip the AU off and find the max id number. The result from this is 0703. This is what I want to see. I get the next available by adding 1 to the result. Unfortunately the result is 704 without the leading 0. I want the value going in the table to be AU0704 not AU704. I'm using a format command, but not getting anywhere. Any ideas where it is going wrong?

Private Sub AuthorIDCheck_Click()


Dim NUM As String
Dim MS As String
Dim S As String
Dim strsql As String
Dim db As Database
Dim rs As DAO.Recordset
Dim rpt As Report
Dim aut As String
Dim auts As Integer






If IsNull(Me.AuthorName) Or (Me.AuthorName = "") Then MsgBox ("The Book's Author Cannot be Left Blank")
If IsNull(Me.AuthorName) Or (Me.AuthorName = "") Then Exit Sub


NUM = Nz(DLookup("ID", "Authors", "Name = """ & AuthorName.Value & """"), 0)




MsgBox ("The Author ID = " & NUM)
  
If (NUM Like "AU*") Then


    AID.Value = NUM


Else


    MsgBox ("The author is not in the database")


    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT Max(Mid(Authors.ID, 3, 4)) As MaxAID FROM Authors")




    aut = rs.Fields("MaxAID")
    MsgBox ("The Max Author ID is = " & aut)


    aut = aut + 1


    aut = Format(aut, "0000")
    MsgBox ("The Author ID = " & aut)


    AID.Value = "AU" + aut


End If




End Sub

[Welshgasman]

Use Format(YourField,"0000"). That will then result in a string regardless of just numeric characters.

[Welshgasman]

Use Format(YourField,"0000"). That will then result in a string regardless of just numeric characters. No idea as to why that is not working for you.
I would have strAut and not reuse aut all the time.

However, a quick test in the imemdiate window show

aut = "123"
aut = aut+1
? aut
 124 
? format(aut,"0000")
0124

So again no idea why it is not working for you. Try & instead of + as well.

I would also use Debug.Print instead os MsgBox to make life easier.

Edit: Even your syntax works for me?

aut = "123"
aut = aut+1
? aut
 124 
tt= format(aut,"0000")
? tt 
0124
? "au"+tt
au0124

[clukem]

Use Format(YourField,"0000"). That will then result in a string regardless of just numeric characters.

The problem seems to be with the format command. When I click on the button it runs until this command. Then I get runtime error 13

[jojowhite]

You can also try this code:

Private Sub AuthorIDCheck_Click()


Dim NUM As String
Dim MS As String
Dim S As String
Dim strsql As String
Dim rpt As Report
Dim aut As String
Dim auts As Integer
Dim var As Variant


If Len(Trim$(Me.AuthorName & "")) = 0 Then
    MsgBox ("The Book's Author Cannot be Left Blank")
    Exit Sub
End If


NUM = Nz(DLookup("ID", "Authors", "Name = """ & AuthorName.Value & """"), 0)


MsgBox ("The Author ID = " & NUM)
  
If (NUM Like "AU*") Then




    AID.Value = NUM




Else




    MsgBox ("The author is not in the database")




    NUM = DMax("ID", "Authors", "ID LIKE 'AU*'") & ""
    
    If Len(NUM) > 0 Then
        var = Split(NUM, "AU")


        MsgBox ("The Max Author ID is = " & var(1))


        var(1) = Format$(Val(var(1)) + 1, "0000")


        aut = Join(var, "AU")


    Else
        aut = "AU0001"
    End If


    MsgBox ("The Author ID = " & aut)
    AID.Value = aut




End If


End Sub

[clukem]

You can also try this code:

Private Sub AuthorIDCheck_Click()


Dim NUM As String
Dim MS As String
Dim S As String
Dim strsql As String
Dim rpt As Report
Dim aut As String
Dim auts As Integer
Dim var As Variant


If Len(Trim$(Me.AuthorName & "")) = 0 Then
    MsgBox ("The Book's Author Cannot be Left Blank")
    Exit Sub
End If


NUM = Nz(DLookup("ID", "Authors", "Name = """ & AuthorName.Value & """"), 0)


MsgBox ("The Author ID = " & NUM)
  
If (NUM Like "AU*") Then




    AID.Value = NUM




Else




    MsgBox ("The author is not in the database")




    NUM = DMax("ID", "Authors", "ID LIKE 'AU*'") & ""
    
    If Len(NUM) > 0 Then
        var = Split(NUM, "AU")


        MsgBox ("The Max Author ID is = " & var(1))


        var(1) = Format$(Val(var(1)) + 1, "0000")


        aut = Join(var, "AU")


    Else
        aut = "AU0001"
    End If


    MsgBox ("The Author ID = " & aut)
    AID.Value = aut




End If


End Sub

Fails at this point with runtime 13. Doesn't seem to like the format command
var(1) = Format(var(1) + 1, "0000")

[jojowhite]

What is the FieldType of ID, Numeric or Short Text.
If numeric, you need to to add Format Property to AID Textbox:

Format: AU0000

and change change your code to:

Private Sub AuthorIDCheck_Click()


Dim NUM As Long
Dim MS As String
Dim S As String
Dim strsql As String
Dim rpt As Report
Dim aut As String
Dim auts As Integer
Dim var As Variant


If Len(Trim$(Me.AuthorName & "")) = 0 Then
    MsgBox ("The Book's Author Cannot be Left Blank")
    Exit Sub
End If


NUM = Nz(DLookup("ID", "Authors", "Name = """ & AuthorName.Value & """"), 0)


MsgBox ("The Author ID = " & Format$(NUM, "AU0000"))
  
If (NUM > 0) Then




    AID.Value = NUM




Else




    MsgBox ("The author is not in the database")




    NUM = Val(DMax("ID", "Authors") & "")
    
    If (NUM > 0) Then


        NUM = NUM + 1


    Else
        NUM = 1
    End If


    aut = Format$(NUM, "AU0000")
    MsgBox ("The Author ID = " & aut)
    AID.Value = NUM




End If


End Sub

[clukem]

What is the FieldType of ID, Numeric or Short Text.
If numeric, you need to to add Format Property to AID Textbox:

Format: AU0000

and change change your code to:

Private Sub AuthorIDCheck_Click()


Dim NUM As Long
Dim MS As String
Dim S As String
Dim strsql As String
Dim rpt As Report
Dim aut As String
Dim auts As Integer
Dim var As Variant


If Len(Trim$(Me.AuthorName & "")) = 0 Then
    MsgBox ("The Book's Author Cannot be Left Blank")
    Exit Sub
End If


NUM = Nz(DLookup("ID", "Authors", "Name = """ & AuthorName.Value & """"), 0)


MsgBox ("The Author ID = " & Format$(NUM, "AU0000"))
  
If (NUM > 0) Then




    AID.Value = NUM




Else




    MsgBox ("The author is not in the database")




    NUM = Val(DMax("ID", "Authors") & "")
    
    If (NUM > 0) Then


        NUM = NUM + 1


    Else
        NUM = 1
    End If


    aut = Format$(NUM, "AU0000")
    MsgBox ("The Author ID = " & aut)
    AID.Value = NUM




End If


End Sub

ID is short text. I then stripped AU off it to get max. The code gets the correct max and even correctly adds 1 to it. It falls down when I need to re-format it. I tried this as a separate test

test = Format(2106, "0000")
Debug.Print "The value of test is : " & test; ""

and it still fell down at the format command

[jojowhite]

it will only Format to 4 Zeros.
if your number is greater than 999, it will not get format since it is already 4 digit.
you need to increase the number of 0's, example:

test = Format(2106, "00000") '5 digit
Debug.Print "The value of test is : " & test

result:
02106

[orange]

A modified version using one of my sample tables (AuthorsT). I commented code that is not used.

Private Sub aid_Click()

    Dim NUM As Long
    Dim MS As String
    Dim S As String
    Dim strsql As String
    Dim rpt As Report
    Dim aut As String
    Dim auts As Integer
    Dim var As Variant
    Dim jvar As String  'my variable for testing
    
    'check for authorName
    If Len(Trim$(Me.authorName & "")) = 0 Then
        MsgBox ("The Book's Author Cannot be Left Blank")
        Exit Sub
    End If
    
    'authorname exists so check aid
    If (Me.aid) & "" = "" Then
        MsgBox "AID can not be null"
        'adjusting AID
        jvar = Format(CLng(DMax("Mid(aid, 3)", "AuthorsT") + 1), "00000") 'format adjusted for 5 digits
        MsgBox "The Author ID = AU" & jvar
        Me.aid = "AU" & jvar
 
    End If

    '  NUM = Nz(DLookup(Mid(Me.aid, 3), "AuthorsT", "Name = '" & Me.authorName.Value & "'"), 0)


    '  MsgBox ("The Author ID = " & Format$(NUM, "AU0000"))
  
    ' If (NUM > 0) Then
    '
    '        aid.Value = NUM
    '    Else
    '        MsgBox ("The author is not in the database")
    '        NUM = val(DMax("ID", "Authors") & "")
    '
    '        If (NUM > 0) Then
    '            NUM = NUM + 1
    '        Else
    '            NUM = 1
    '        End If
    '
    '        aut = Format$(NUM, "AU0000")
    '        MsgBox ("The Author ID = " & aut)
    '        aid.Value = NUM
    '    End If
 
 ' print status message
    MsgBox "Record: '" & Me.aid & "'  '" & Me.authorName & "'"
    Debug.Print "Record: '" & Me.aid & "'  '" & Me.authorName & "'"

End Sub

Started with 'AU00001' Bob

Added 3 records without AID

Record: 'AU00002' 'Jim'
Record: 'AU00003' 'Chris'
Record: 'AU00004' 'Ted'

[Welshgasman]

Have you happened to create a Format function yourself?

Upload enough of the DB to see the issue otherwise, as what code you have shown should work.

[clukem]

I changed your values for ones in my table. It still falls down at the format statement

[clukem]

No. No format function created by me. I've attached some screenshots of the database. Hopefully they come through

[orange]

I changed your values for ones in my table. It still falls down at the format statement

NOTE: In my version, jvar is a string. Your version has NUM as Long. That may be the issue.



I have attached a sample database in zip format. Try it, use form tblAuthorsT, create a new record, add a name, then click on AID. The new value will be assigned---you must go to a different record for the update to the table to occur.

[June7]

You have a combobox for AuthorName. Consider using combobox NotInList event to add new author during book data entry.

Really, name parts should be in separate fields - FirstName, MiddleName, LastName.

How do you handle publications with multiple authors?

What is exact error message?

Are you missing any library references?

Why bother saving "AU" into field?