VBA Problem, Padding left with "0"

[TomProff]

Hi all,

I'm relatively new to vba in Access and would approctae any help anyone may be able to offer.

I have the following vba code in my database, in order to add new records based on the last number in the table.

Private Sub PopulateDataGen_Click()

Dim barcodeLim As Long
Dim currentBarcode As String
Dim dbArtifacts As DAO.Database
Dim rstGeneral As DAO.Recordset

Set dbArtifacts = CurrentDb
Set rstGeneral = dbArtifacts.OpenRecordset("Data_General", dbOpenDynaset)

currentBarcode = [Forms]![Create Barcodes]![MaxOfBarcode] + 1
barcodeLim = [Forms]![Create Barcodes]![NumNewCodes] + [Forms]![Create Barcodes]![MaxOfBarcode] + 1


Do Until currentBarcode = barcodeLim

rstGeneral.AddNew
rstGeneral("Barcode").Value = currentBarcode
rstGeneral("Artifact Type").Value = [Forms]![Create Barcodes]![Artifact Type]
rstGeneral("Site").Value = [Forms]![Create Barcodes]![Site]
rstGeneral("Bed").Value = [Forms]![Create Barcodes]![Bed]
rstGeneral("Level").Value = [Forms]![Create Barcodes]![Level]
rstGeneral("Trench").Value = [Forms]![Create Barcodes]![Trench]
rstGeneral.Update


currentBarcode = currentBarcode + 1

Loop

rstGeneral.Close

DoCmd.Requery


[Forms]![Create Barcodes]![NumNewCodes] = Null
MsgBox "Records Added To Database"


End Sub


I was wondering if anyone would know how to have the new entries created in this code have the format of "0000000000". these new entries come under the "barcode" field.

any help would be hugely appreciated

all the best

Tom

 

[pr2-eugin]

Have you looked into the Format function? Not sure how you plan to work with numbers though..

 

[TomProff]

Hey, thanks, I have looked at the format option.

I'm not entirely sure how to and where to implement it into the code. Also i only want the new "barcode" numbers formatted in this way.

 

[pr2-eugin]

So what does these controls have?

[Forms]![Create Barcodes]![MaxOfBarcode]
[Forms]![Create Barcodes]![NumNewCodes]
[Forms]![Create Barcodes]![MaxOfBarcode]

Where do they get their values from? The button is on the Form which also has the above controls right?? What is the type of the Barcode field in the table?

 

[gemma-the-husky]

what format is the field. is it a number?

if you need leading zeroes, it HAS to be text, not numeric, I would think.

to be honest, I am not sure about the standard way of handling bar codes.

 

[Pat Hartman]

With numeric fields, leading zeros are not stored. They are only stored for text fields. Since this is a generated number and needs to be numeric, you will need to add the leading zeros when you format the field. The simplest way is in your query.

Select Format(MyBarCode, "00000000000") as FormattedBarCode,...
From YourTable;

I used 11 zeros. This will fix the length of the field to 11 digits and zero fill if the actual value is fewer digits. Just use the correct number of zeros to make the field the fixed width you need.

Alternatively, you can store the field as a fixed width text field including the leading zeros and convert it to integer to increment.

CInt(MyBarCode) + 1