Automatically entering a range of numbers

[bkitka]

I want to define the range by putting numbers into 2 different fields? I have bar codes that get entered into a database. There are times when I want to enter the first bar code and the last bar code and have the bar codes in between automatically populate.

 

[dcx693]

If you want to enter the first and last barcode numbers and have the ranges autogenerate, you can enter the numbers into a form and use some code do the number inserts for you. The code can be a SQL append query or DAO/ADO code.

 

[bkitka]

If you want to enter the first and last barcode numbers and have the ranges autogenerate, you can enter the numbers into a form and use some code do the number inserts for you. The code can be a SQL append query or DAO/ADO code.

I am new to programming, how would this be done

 

[dcx693]

What version of Access are you using? What is the table in your database called?

 

[bkitka]

What version of Access are you using? What is the table in your database called?

I am using Access 2003. he table is name BarCode. It has the following fields:
DateIn, DateOut, BarCode.

 

[dcx693]

OK, so I assume that you'll have a form with two text controls, called txtStartBarCode and txtEndBarCode and a command button called cmdAddBarcodes. This is the code you need in the Click event of the commadn button. Please note that there is no error checking in the code - you will really want to include some!

Private Sub cmdAddBarcodes_Click()
Dim lngBarcode As Long
Dim strSQL As String

    strSQL = "INSERT INTO Barcode (Barcode) VALUES("

    For lngBarcode = Me.txtStartBarcode To Me.txtEndBarcode
        CurrentProject.Connection.Execute _
            strSQL & lngBarcode & ");"
    Next lngBarcode

End Sub

 

[Pat Hartman]

Please don't start multiple threads on the same topic.

 

[bkitka]

OK, so I assume that you'll have a form with two text controls, called txtStartBarCode and txtEndBarCode and a command button called cmdAddBarcodes. This is the code you need in the Click event of the commadn button. Please note that there is no error checking in the code - you will really want to include some!

Private Sub cmdAddBarcodes_Click()
Dim lngBarcode As Long
Dim strSQL As String

    strSQL = "INSERT INTO Barcode (Barcode) VALUES("

    For lngBarcode = Me.txtStartBarcode To Me.txtEndBarcode
        CurrentProject.Connection.Execute _
            strSQL & lngBarcode & ");"
    Next lngBarcode

End Sub

Thank you very much!!! This worked great! I am sory again for the multiple postings

 

[bkitka]

Thank you very much!!! This worked great! I am sory again for the multiple postings

How would I go about getting 2 date field to be populated along with the bar code numbers?

 

[Mile-O]

I am sorry again for the multiple postings

I have closed your other two posts on this subject.

 

[dcx693]

How would I go about getting 2 date field to be populated along with the bar code numbers?

Do some searching on the syntax of append queries. If you want to use the code I posted, change the SQL string to something of the form:

CurrentProject.Connection.Execute "INSERT INTO Barcode (field1,field2,field3) VALUES(value1,value2,value3);"

 

[bkitka]

Do some searching on the syntax of append queries. If you want to use the code I posted, change the SQL string to something of the form:

CurrentProject.Connection.Execute "INSERT INTO Barcode (field1,field2,field3) VALUES(value1,value2,value3);"

Thank you very much

 

[bkitka]

Do some searching on the syntax of append queries. If you want to use the code I posted, change the SQL string to something of the form:

CurrentProject.Connection.Execute "INSERT INTO Barcode (field1,field2,field3) VALUES(value1,value2,value3);"

I have been searching on the append queries, to no avail. I can't get the syntax right to add these fields to the code that you gave me.

 

[dcx693]

Change the code to something like:

Private Sub cmdAddBarcodes_Click()
Dim lngBarcode As Long

    For lngBarcode = Me.txtStartBarcode To Me.txtEndBarcode
        CurrentProject.Connection.Execute _
            "INSERT INTO Barcode (Barcode,DateIn,DateOut) " & _
            "VALUES(" & lngBarcode & "," & Me.txtDateIn & "," & Me.txtDateOut & ");"
    Next lngBarcode

End Sub

That code assumes that you've got two date controls on your form called txtDateIn and txtDateOut.

 

[bkitka]

Change the code to something like:

Private Sub cmdAddBarcodes_Click()
Dim lngBarcode As Long

    For lngBarcode = Me.txtStartBarcode To Me.txtEndBarcode
        CurrentProject.Connection.Execute _
            "INSERT INTO Barcode (Barcode,DateIn,DateOut) " & _
            "VALUES(" & lngBarcode & "," & Me.txtDateIn & "," & Me.txtDateOut & ");"
    Next lngBarcode

End Sub

That code assumes that you've got two date controls on your form called txtDateIn and txtDateOut.

When I run this code I get an error in the CurrentProject.Connection.Execute area.

 

[dcx693]

Is there a specific error message you get? You might have to delimit the date fields if you're using dates, like:
"VALUES(" & lngBarcode & ",#" & Me.txtDateIn & "#,#" & Me.txtDateOut & "#);"

 

[bkitka]

Is there a specific error message you get? You might have to delimit the date fields if you're using dates, like:
"VALUES(" & lngBarcode & ",#" & Me.txtDateIn & "#,#" & Me.txtDateOut & "#);"

That is it. Thank you very much for all of you assistance.

 

[PlasticMonster]

Hi,

Im doing somnething similar but getting an error trying to replicate this code:

Table = "Card Usage"

Form= "Stock Add"

Text box for card number form = "TBcnf"
Text box for card number to = "TBcnt"
(I realise now that this acronym is a little inappropriate!!)

Button with VBA in it "Baddrange"


I modified the code to be:

Private Sub Baddrange_Click()
Dim lngCard_usage As Long
Dim strSQL As String
strSQL = "INSERT INTO card_Update (Barcode) VALUES("
For lngBarcode = Me.TBcnf To Me.TBcnt
CurrentProject.Connection.Execute _
strSQL & lngCard_usage & ");"

Next lngCard_usage

End Sub


But im getting an issue with the next command...
"Compile Error Invalid Next Control variable reference"

I dont quite understand that lng part of the code so lost on a solution, thought id add it to this thread rather than start a new one