Query to Insert Multiples Rows (1 Viewer)

MattBaldry

Registered User
Joined
Feb 5, 2019
Messages
55
Hello again forumers.

I am after some advice on a query that will add multiple rows into a table from an unbound form. I will try to explain below what I need.

We buy cable from a supplier and it comes in by the reel. We have to assign a batch number to each reel IE 123456-001, 002, 003, 004 and so on. At the moment he has to add each one by hand. On our last shipment we received 50 reels of one cable type, so he had to add 50 lines.

I am after a code that would allow him to input the batch number and total number of reels and when he clicks add, it will add all 50 lines, generating the reel number for each one IE 123456-001 - 050.

My table is called tblCableReel and the fields are as below.

CableID - FK (uses the PK from tblCable)
CableReedID - PK (Autonumber reel ID)
CableReelBatch - Batch number
CableReelNumber - Reel number from this batch IE 1-50
CableReelDate - Date it was booked in
CableReelLength - The length of cable on the reel
CableReelEmployee - FK (The ID of the employee booking the cable)
CableReelFinished - Yes/No, used to mark the reel complete once all cable has been used.

I have written plenty of insert queries to do a single line, but I need a code that will add as many lines as the employee selects in the form.

Thank you for your input and advice as always.

~Matt
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,013
Because you need to increment the reel no, I would probably do it via a recordset?

I'll be interested in how other methods if possible might work.?

Just thought of a single record query also in a loop?
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,310
I'd also suggest using a loop for this.
Something like this for your example

Code:
Dim I As integer
For I = 1 to 50
'do insert query based on a value like this
CableReelBatch = "IE 123456-" & Format(I, "000")
Next
As you're going to do this repeatedly, I would create a function to generalise the process
 

MattBaldry

Registered User
Joined
Feb 5, 2019
Messages
55
Just as an additional bit of information, the input form would have unbound fields as below.

CableID - They will select the cable type received from the drop down menu
BatchNumber - The cable batch number when booked in
CableReelStart - The number the cable reel will start from, this may not always start at 1
CableReelQuantity - The number of reels received
CableReelLength - The length of cable on each reel
CableReelDate - Auto fills in with todays date.

I would like the user to be able to input the details and when they click add, it inserts the data into the table for the number of reels received, starting from the number they selected, IE 12 reels starting at 12.

~Matt
 

MattBaldry

Registered User
Joined
Feb 5, 2019
Messages
55
Code:
Private Sub BatchAdd_Click()

    Dim strSQL As String
    Dim intCableReelQuantity As Integer = txtCableReelStart

    Dim strCableReelBatch As String = txtCableReelBatch

    Do While intCableReelQuantity <= txtCableReelQuantity

        strSQL = "INSERT INTO table (CableReelBatch, CableReelNumber) VALUES ('" & strCableReelBatch & "', '" & intCableReelQuantity & "')"
        CurrentDb.Execute(strSQL)
        intCableReelQuantity = intCableReelQuantity + 1
    Loop

End Sub
Something like the above maybe? I found this on another site....

~Matt
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,013
Are you sure the length of cable is the same for each reel?
The date in the table could have a default of Date, so that would be one less value to play around with?

I think just for easier coding for you, a recordset would be the best option for you.

Just set up a loop for the correct number of reels with correct starting number and other values, most of which would be set outside the loop.?

https://docs.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/add-a-record-to-a-dao-recordset

I would probably put all this code in the relevant form and not use a procedure/function outside the form, unless you know you would want to use it elsewhere?

HTH
 

MattBaldry

Registered User
Joined
Feb 5, 2019
Messages
55
Are you sure the length of cable is the same for each reel?
The date in the table could have a default of Date, so that would be one less value to play around with?

I think just for easier coding for you, a recordset would be the best option for you.

Just set up a loop for the correct number of reels with correct starting number and other values, most of which would be set outside the loop.?

https://docs.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/add-a-record-to-a-dao-recordset

I would probably put all this code in the relevant form and not use a procedure/function outside the form, unless you know you would want to use it elsewhere?

HTH
The date field you are correct, that is now done. We tend to always buy reels in the same length so this would be fine. It is very rare we receive anything not on a standard size.

I have never tried doing any sort of loops so any coding advice would be much appreciated. The code would be form based as this would be the only form using it.

~Matt
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,013
You appear to have deleted a post.?, but that was using an insert statement.
Whilst this is one way, I believe it would be harder for you to code?

Here is a simple recordset option. It shows a simple loop and adding records within that loop.

https://www.tek-tips.com/viewthread.cfm?qid=946476
 

MattBaldry

Registered User
Joined
Feb 5, 2019
Messages
55
You appear to have deleted a post.?, but that was using an insert statement.
Whilst this is one way, I believe it would be harder for you to code?

Here is a simple recordset option. It shows a simple loop and adding records within that loop.

https://www.tek-tips.com/viewthread.cfm?qid=946476
Okay, so I have changed it to suit my database, as below, but it only inserts 1 record.

Code:
Dim i, CableReelsReceived As Integer
Dim Cable As Integer
Dim Employee As Integer
Dim CableReelBatch As String
Dim CableReelLength As Integer
Dim CableReelStart As Integer

Dim rst As Recordset
  CableReelsReceived = Me.txtCableReelsReceived
  Employee = Me.cboEmployee
  Cable = Me.txtCableID
  CableReelBatch = Me.txtCableReelBatch
  CableReelLength = Me.txtCableReelLength
  CableReelStart = Me.txtCableReelStart
  Set rst = CurrentDb.OpenRecordset("tblCableReel")
  For i = 0 To i = CableReelsReceived - 1
    rst.AddNew
    rst!CableID = Cable
    rst!CableReelOrder = CableReelBatch
    rst!CableReelLength = CableReelLength
    rst!CableReelEmployee = Employee
    rst!CableReelDate = Date
    rst.Update
  Next i
rst.Close
Set rst = Nothing
What have I done wrong so far?

~Matt
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,543
We tend to always buy reels in the same length so this would be fine. It is very rare we receive anything not on a standard size.
If you set up a loop for ease of use and adding reel records, then any batches/reels that don't have same length, reels received, date, employee etc would represent an exception and would have to be entered separately (or a different procedure).

If on your form you had BatchNumber, number of reels received,reel length, date, employee --and any other values that are consistent in the Batch to be processed, then the Loop approach would be most suitable.

Perhaps you could show us your form.

Do your reels start with 000 or 001?

Also, this doesn't work the way you think
Code:
Dim i, CableReelsReceived As Integer
You must do explicit assignment
Code:
Dim i As Integer
Dim  CableReelsReceived As Integer
   ---OR ---
Dim i As Integer, CableReelsReceived As Integer
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,013
Walk through the code line by line and only say there are 2 reels to start with.

I would have thought that this would start with ReelStartNumber for as as many reels specicied on the form?

Code:
For i = 0 To i = CableReelsReceived - 1
More like
Code:
For i = Me.txtCableReelStart to Me.txtCableReelStart + Me.Me.txtCableReelsReceived
You have not included the reel number which would be something along the lines of

Code:
rst!ReelNumber = "IE 123456-" & Format(i,"000")
Though keeping them seperate is always recomended, and concatenate when needed.?

HTH

If you want to pust them into new variables as tou did for Me.txtCableReelsReceived, no problem with that, but I would say not really needed here, but a matter of personal preference.?

Okay, so I have changed it to suit my database, as below, but it only inserts 1 record.

Code:
Dim i, CableReelsReceived As Integer
Dim Cable As Integer
Dim Employee As Integer
Dim CableReelBatch As String
Dim CableReelLength As Integer
Dim CableReelStart As Integer

Dim rst As Recordset
  CableReelsReceived = Me.txtCableReelsReceived
  Employee = Me.cboEmployee
  Cable = Me.txtCableID
  CableReelBatch = Me.txtCableReelBatch
  CableReelLength = Me.txtCableReelLength
  CableReelStart = Me.txtCableReelStart
  Set rst = CurrentDb.OpenRecordset("tblCableReel")
  For i = 0 To i = CableReelsReceived - 1
    rst.AddNew
    rst!CableID = Cable
    rst!CableReelOrder = CableReelBatch
    rst!CableReelLength = CableReelLength
    rst!CableReelEmployee = Employee
    rst!CableReelDate = Date
    rst.Update
  Next i
rst.Close
Set rst = Nothing
What have I done wrong so far?

~Matt
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,479
There is a sort of back-door way to do this that requires a one-time setup and this absolutely will not work correctly if there are non-uniform elements.

First build a permanent table of integers from 1 to some seriously large number that is higher than any number of orders you have ever had to manage. Or if your business rules state a limit, then as many as that limit. No missing integers allowed. Call it tblNumbers or something innocuous like that. It will have one and only one field: IDNumber. You COULD build this with code or you could open an Excel sheet, put numbers 1 and 2 as the first two elements of the column, and then select the two numbers and drag the bottom down to build the list of numbers. Then import that spreadsheet. There are many ways to do it - that is just one.

Second, when you are about to enter a batch of reels, use this sequence to build and run an SQL statement. Note that because your field CableReelID is an autonumber, you do not include it into the INSERT INTO statement. Add a text box for the number of reels you wish to enter. In my code I called it txtNumberOfReels, but that was just to give it a name at all.

Code:
Dim sSQLCable as String

...

sSQLCable = "INSERT INTO tblCableReel " & _
    "(CableID, CableReelBatch, CableReelNumber, CableReelDate, " & _
    "CableReelLength, CableReelEmployee, CableReelFinished) " & _ 
  SELECT " & Me.txtCableID & ", '" & Me.txtCableReelBatch & "', " _
    & IDNumber, #" & Date() & "#, " & _
    Me.txtCableReelLength & ", " & Me.cboEmployee & ", FALSE " & _
  FROM tblNumbers " & _
  "WHERE IDNumber <= " & Me.txtNumberOfReels & " ;"
CurrentDB.Execute sSQLCable, dbFailOnError
Everything you are entering is a constant EXCEPT the reel number, so you grab that from the tblNumbers table that lists numbers for you. The setup is a bit complex, true, but once you have the setup, you just insert a bunch of records this way VERY quickly. NOTE: Your data types show that the only text element is CableReelBatch. Everything else is integers. So taking them from a text box, the integers remain conveniently ambiguous and you don't have to diddle with them. But watch for the embedded apostrophes for CableReelBatch.

You might have to play with it a bit more.

You also asked what was wrong with what you were doing. Your loop didn't include a place to store the reel number in the recordset operation. You used that number as a counter but didn't also use it as data.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom