Leading zeros are truncated when passing a value from a data entry form to a vba function

[Iain]

I am using a data entry form to pass a number of function parameters to a form's visual basic code.



Data entry of one of the parameters is by an unbound text box [StartNumber] which accepts a 4 digit number i.e. 0045, however the the equivalent function parameter [StartNumber] is truncated to 45.

Thus, the remaining code only functions if StartNumber is greater than 1000.

How can I prevent the number being truncated in the function before it is used by the remaining code?

Thanks for any assistance rendered.

[June7]

What do you mean by 'function parameters' - is this a VBA function? Or is this a query? Parameter is applied to a text type field? Is code setting a variable? Post code or SQL statement for analysis.

[Iain]

Thank you June7, you can close out my other post on this topic. I am an Access beginner and don't fully understand your questions but I will try my best.


The code is from a research project that stores images from many digital cameras. The images are allocated various identifiers according to the camera used, the series of number generated by the camera used to capture the image, different libraries of images.
This information is assembled in an image name that resembles the Dewey Library system e.g. 5001.C52.101.IMG_1234.


The form is not generated by a query. The record source for the form is a table [tblCameraLibrary], although, this has no bearing [StartNumber] as this is blank when the form is opened.


Data is manually entered into text boxes on the form and the VBA for the form is activated by a button [cmdCalc] on the form. The ‘On Click’ property of the button contains the following code:
=InsertFrames([txtCameraLibrary],[txtCameraLibraryID],[Status],[Prefix],[NextRecNo],[StartNumber],[NumberOfFrames])


The data is captured by the first line of the VBA code:

Function InsertFrames(txtCameraLibrary As String, txtCameraLibraryID As Long, Status As String, Prefix As String, NextRecNo As Long, StartNumber As Long, NumberOfFrames As Integer)


At this stage, unless [StartNumber] is 1000 or greater, it is truncated.

Does this help?

[CJ_London]

numbers are numbers, they do not have preceding zeros - for that to be the case, the 'number' is actually text.

if you want preceding zeros as a number then you can use

format(startnumber,"0000")

check the code in your insertframes function - you will probably find it is treating startnumber as a number so it can increment it (you can't increment text)

If the function is just using the value, change the parameter from

startnumber as long

to

startnumber as string

[June7]

As Ajax points out, it is the declaration of StartNumber as Long that causes the truncation.

Post the rest of the code for InsertFrames() if still having issue.


Not finding another thread on this topic.

[Iain]

if you want preceding zeros as a number then you can use

format(startnumber,"0000")

Thanks Ajax, where should I make use of this format statment? In my text box properties? ... in my VBA code?

[June7]

Format() function not needed if you change the variable type in the function declaration statement that you provided in your post.

[Iain]

Format() function not needed if you change the variable type in the function declaration statement that you provided in your post.

Ajax is correct, the remainder of the VBA code treats StartNumber as a number for incrementation purposes and yes ... I would prefer leading zeros.

Is format(StartNumber,"0000") treating the data as a number or is it converting it to text?

Before I look at the remainder of the code I would like to understand the use of Ajax's suggestion, hence my last question on where I should make use of it. Could we please clarify that before we go further?

[CJ_London]

Is format(StartNumber,"0000") treating the data as a number or is it converting it to text?

formatting any number converts it to text - so yes, it is converted to text

where I should make use of it.

I was just demonstrating how to get preceding zeros as a value. without knowing how your form is intended to work and what you code is, I don't know - are users entering 0045 in the form or just 45? Is your code incrementing? what does the function insertframes do? If it is to create a file name with ....0045 rather than ...45 then I would suggest the bit of code that does that and perhaps format the startnumber control (but be aware this is the control property, not the value) and would simply be 0000

[June7]

Post your code and maybe we can suggest how to fix.

[Iain]

Post your code and maybe we can suggest how to fix.

The following code works perfectly well for 4 digit numbers e.g. 1000 or greater. The user is required to enter a 4 digit number ... 0045 but it truncates to 45. It still increments as necessary but the SQL statment fails as I previously explained.

Function InsertFrames(txtCameraLibrary As String, txtCameraLibraryID As Long, Status As String, Prefix As String, NextRecNo As Long, StartNumber As Long, NumberOfFrames As Long)
'Runs when 'cmdCalc' is clicked
'calculates camera frame numbers and inserts a new record for each frame into tblCameraImage and tblDigitalImage

Stop
On Error GoTo MyError

Dim frm As Form
Dim cmd As ADODB.Command
Dim strSQL As String
Dim okFlag1 As Boolean
Dim okFlag2 As Boolean
Dim n As Integer
Dim x As Integer
Dim ExtSource As Long
Dim FileName As String

Set frm = Forms!frmEditCameraImageDetails
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
DoCmd.SetWarnings False
okFlag1 = False
okFlag2 = False
        
'data validation for Status
If Status = "Unprocessed" Then
    Status = StrConv("Unprocessed", vbProperCase)
    okFlag1 = True
End If
If okFlag1 = True Then
    
    'data validation for Prefix
    If Prefix = "IMG" Then
        Prefix = StrConv("IMG", vbUpperCase)
        okFlag2 = True
    End If
    If Prefix = "DI" Then
        Prefix = StrConv("DI", vbUpperCase)
        okFlag2 = True
    End If
    If okFlag2 = True Then
        For n = 0 To NumberOfFrames - 1
            'add new records to tblCameraImage
            strSQL = "INSERT INTO [tblCameraImage]([CameraLibraryID],[CameraImageStatus],[CameraFrame],[FrameChronoOrder])"
            strSQL = strSQL + " VALUES(" & txtCameraLibraryID & ",""" & Status & """,""" & Prefix & "_" & StartNumber + n & """,""" & Prefix & "_" & StartNumber + n & """)"
            cmd.CommandText = strSQL
            DoCmd.RunSQL strSQL
            'add new records to tblDigitalImage
            DigStatus = "Unprocessed"
            Rating = 1
            FileName = "5001." & txtCameraLibrary & ".001." & Prefix & "_" & StartNumber + n & ".jpg"
            Path = "C:\Users\Public\Pictures\Photo Library Database\New Images\Camera Images\Unprocessed Images\External Source\" & [FileName]
            strSQL = "INSERT INTO tblDigitalImage(CameraImageID,ImageStatus,ImageRating,ImageFileName,FilePath)"
            strSQL = strSQL + " VALUES(""" & NextRecNo + n & """, """ & DigStatus & """, """ & Rating & """, """ & FileName & """, """ & Path & """)"
            cmd.CommandText = strSQL
            DoCmd.RunSQL strSQL
        Stop
        Next n
        DoCmd.SetWarnings True
        x = MsgBox("You have successfully added " & [NumberOfFrames] & " new records to tables tblCameraImage & tblDigitalImage", , "Complete")
    Else
        x = MsgBox("Invalid Prefix", , "Data Validation Error")
    End If
Else
    x = MsgBox("Invalid Status", , "Data Validation Error")
End If
    
'refresh frmEditCameraDetails
frm.Requery
frm.Refresh
    
'close dialog box and return to frmEditCameraDetails
DoCmd.Close acForm, "frmFrames", acSaveNo
    
MyErrorExit:
    Exit Function
    
MyError:
    Dim Msg As String
    Msg = "Error in Function InsertFrames" & Err.Number & ": " & Err.Description
    MsgBox Msg
    Resume MyErrorExit

End Function

[June7]

Note that I edited your post to include CODE tags so indentation is retained and code easier to read.

The same value goes in CameraFrame and FrameChronoOrder?

Try:

strSQL = "INSERT INTO [tblCameraImage]([CameraLibraryID],[CameraImageStatus],[CameraFrame],[FrameChronoOrder])"
strSQL = strSQL & " VALUES(" & txtCameraLibraryID & ",'" & Status & "','" & Prefix & "_" & Format(StartNumber + n), "0000") & "','" & Prefix & "_" & Format(StartNumber + n), "0000") & "')"

[ssanfu]

The changes I made are in RED.
Note that the concatenation character you should use is the ampersand (&), not the plus (+).

My preference would be to use DAO instead of ADODB.
And I don't like turning the warnings off because you don't get error messages.

Function InsertFrames(txtCameraLibrary As String, txtCameraLibraryID As Long, Status As String, Prefix As String, NextRecNo As Long, StartNumber As Text, NumberOfFrames As Long)
    'Runs when 'cmdCalc' is clicked
    'calculates camera frame numbers and inserts a new record for each frame into tblCameraImage and tblDigitalImage

    Stop
    On Error GoTo MyError

    Dim frm As Form
    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim okFlag1 As Boolean
    Dim okFlag2 As Boolean
    Dim n As Integer
    Dim x As Integer
    Dim ExtSource As Long
    Dim FileName As String
    Dim lngStartNumber As Long

    Set frm = Forms!frmEditCameraImageDetails
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    DoCmd.SetWarnings False
    okFlag1 = False
    okFlag2 = False
    
    lngStartNumber = Val(StartNumber)


    'data validation for Status
    If Status = "Unprocessed" Then
        Status = StrConv("Unprocessed", vbProperCase)
        okFlag1 = True
    End If
    If okFlag1 = True Then

        'data validation for Prefix
        If Prefix = "IMG" Then
            Prefix = StrConv("IMG", vbUpperCase)
            okFlag2 = True
        End If
        If Prefix = "DI" Then
            Prefix = StrConv("DI", vbUpperCase)
            okFlag2 = True
        End If
        If okFlag2 = True Then
            For n = 0 To NumberOfFrames - 1
                'add new records to tblCameraImage
                strSQL = "INSERT INTO [tblCameraImage]([CameraLibraryID],[CameraImageStatus],[CameraFrame],[FrameChronoOrder])"
                strSQL = strSQL & " VALUES(" & txtCameraLibraryID & ",""" & Status & """,""" & Prefix & "_" & Format(lngStartNumber + n, "0000") & """,""" & Prefix & "_" & Format(lngStartNumber + n, "0000") & """)"
                cmd.CommandText = strSQL
                DoCmd.RunSQL strSQL
                'add new records to tblDigitalImage
                DigStatus = "Unprocessed"
                Rating = 1
                FileName = "5001." & txtCameraLibrary & ".001." & Prefix & "_" & Format(lngStartNumber + n, "0000") & ".jpg"
                Path = "C:\Users\Public\Pictures\Photo Library Database\New Images\Camera Images\Unprocessed Images\External Source\" & [FileName]
                strSQL = "INSERT INTO tblDigitalImage(CameraImageID,ImageStatus,ImageRat ing,ImageFileName,FilePath)"
                strSQL = strSQL & " VALUES(""" & NextRecNo + n & """, """ & DigStatus & """, """ & Rating & """, """ & FileName & """, """ & Path & """)"
                cmd.CommandText = strSQL
                DoCmd.RunSQL strSQL
                Stop
            Next n
            DoCmd.SetWarnings True
            x = MsgBox("You have successfully added " & [NumberOfFrames] & " new records to tables tblCameraImage & tblDigitalImage", , "Complete")
        Else
            x = MsgBox("Invalid Prefix", , "Data Validation Error")
        End If
    Else
        x = MsgBox("Invalid Status", , "Data Validation Error")
    End If

    'refresh frmEditCameraDetails
    frm.Requery
    frm.Refresh

    'close dialog box and return to frmEditCameraDetails
    DoCmd.Close acForm, "frmFrames", acSaveNo

MyErrorExit:
    Exit Function

MyError:
    Dim Msg As String
    Msg = "Error in Function InsertFrames" & Err.Number & ": " & Err.Description
    MsgBox Msg
    Resume MyErrorExit

End Function

[Iain]

Ok, I think I follow what is being suggested and replaced the code with the amended version.
I cannot test it however as it generates another error when trying to enter data in one of the other boxes - a combo box



The code that goes with the combo box is:

Private Sub txtLibrary_AfterUpdate()
' Find the record that matches the control.

Stop
Dim rs As Object
Stop
'create a copy of the form's recordset
Stop
Set rs = Me.Recordset.Clone
    'Finds if there is a record in the recordset that matches the record selected in the combo box
    Stop
    rs.FindFirst "[CameraLibraryID] = " & Str(Nz(Me![txtLibrary], 0))
    'If there is a match, moves the selection on the form to the record found in the recordset
    Stop
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    'Me.txtLibrary = Me.txtLibrary.ItemData(0)
'Me.CategoryID.Requery
End Sub

I suspect you will require a copy of the form and the table used a the record source. How do I attach these to my posts?

[June7]

Please use CODE tags for longer indented code so it is easier to read.

To attach files, follow instructions at bottom of my post.

If CameraLibraryID is a text field then the parameter needs apostrophe delimiters.

rs.FindFirst "[CameraLibraryID] = '" & Me![txtLibrary] & "'"

I use recordset NoMatch property.

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

I don't set recordset object variable.

Private Sub txtLibrary_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[CameraLibraryID] = '" & Me![txtLibrary] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub