Auto Generate new records. (1 Viewer)

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
I need to generate new records in bulk. I searched this forum and net and at last the below code worked partially to my requirement.

Public Sub AddRows()

Dim strSQL As String

Dim i As Integer

strSQL = "INSERT INTO Tblinkcode (bottleID) SELECT MAX (bottleID) + 1 FROM Tblinkcode "


For i = 1 To Me.rowcount
CurrentDb.Execute strSQL

Next i

End Sub

I call this function from a button on the form in which I need to fill various field first and then fill desired row count then I need to generate new records with all data filled same as filled currently on form.

But, it does populate only one field (which is autonumber), because I could not set other fields syntax etc.

Could somebody help me in this.

best regards.
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
What are the other fields you wish to Update? My basic Idea, is you have an Update Query next to the Inset??
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
The form is bound to a query. If I fill single records and add, it works fine. My need, I have a similar lot of many products and I wish to generate bar code for all products.

At the moment, I have not thought for any update query because I thought the code posted in my last post after modification shall update that many records.

What you suggest. Please advise.
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
Sorry,

Other fields are:

BottleNo
MakeID
GradeID
BatchNo
ExpiryDate
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
Hello mahenkj2, I have a small question, why do you have to duplicate date? within the same table? Well based on your info, you wanted to add the same data in multiple records.. I am not sure as I have not tested this.. but the following should do the trick I believe..
Code:
Public Sub AddRows()
    Dim i As Integer, botID As Integer
    
    For i = 1 To Me.rowcount
        botID = Nz(DMax("bottleID","Tblinkcode"),0)+1
        CurrentDb.Execute "INSERT INTO Tblinkcode (bottleID, BottleNo, MakeID, GradeID, BatchNo, ExpiryDate) VALUES ("& botID &", "& Me.[COLOR=Blue]BotNo[/COLOR] &", ", "& Me.[COLOR=Blue]MakeID[/COLOR] &", "& Me.[COLOR=Blue]GradeID[/COLOR] &", "& Me[COLOR=Blue].BatchNo[/COLOR] &", "& Me.[COLOR=Blue]ExpiryDate[/COLOR] &")"
    Next
End Sub
Blue bits need to change..
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
Thanks for helping.

This is the code now:

Public Sub AddRows1()
Dim i As Integer, bottleID As Integer

For i = 1 To Me.rowcount
botID = DMax("bottleID", "Tblinkcode") + 1
CurrentDb.Execute "INSERT INTO Tblinkcode (bottleID, BottleNo, MakeID, GradeID, BatchNo, ExpiryDate) VALUES (" & botID & ", " & Me.BottleNo & ", " & Me.MakeId & ", " & Me.GradeID & ", " & Me.BatchNo & ", " & Me.ExpiryDate & ")"
Next
End Sub

There were some minor syntax alerts after correcting which Now I find method or data member not found and error goes on Me.BatchNo first. I removed this and then then error goes on other Me.GradeID

You said to replace blue bits, The above are now the exact field names.
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
They should be the names of the controls on the form.. as you said,
I call this function from a button on the form in which I need to fill various field first and then fill desired row count then I need to generate new records with all data filled same as filled currently on form.
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
I removed all fields and add one by one. I used intellisense, so field names are correct.
Names were correct but I feel is there something to do with data type because now all number field are working. Date field is entering wrong date (05-07-1894) for (18-10-2012), may be it has to be formatted.

Then there is a text field which if I add in the code then it says too few parameters, expected 1.

Thanks.
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
I am sorry did not realise that the Expiry date was actual date, so try enclosing them in ##.. so something like..
Code:
CurrentDb.Execute "INSERT INTO Tblinkcode (bottleID, BottleNo, MakeID, GradeID, BatchNo, ExpiryDate) VALUES (" & botID & ", " & Me.BottleNo & ", " & Me.MakeId & ", " & Me.GradeID & ", " & Me.BatchNo & ", #" & Me.ExpiryDate & "#)"
However the above, code will change the format to mm/dd/yyyy, if you want to preserve the format, try this..
Code:
CurrentDb.Execute "INSERT INTO Tblinkcode (bottleID, BottleNo, MakeID, GradeID, BatchNo, ExpiryDate) VALUES (" & botID & ", " & Me.BottleNo & ", " & Me.MakeId & ", " & Me.GradeID & ", " & Me.BatchNo & ", " & CDbl(Me.ExpiryDate) & ")"
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
Thanks again.

what about another text field? do you think this also has some adjusting required. Please suggest.
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
Normally 'Too Few parameter exception' occurs when there is a syntax error in the statement, try a Debug.print before executing the query, i.e. assign it to a string (as you did earlier) print the string and see if you have any syntax error, that should clear a few things.. If unable to see whats wrong copy and paste one Query, lets see what is going on there..

ALSO make sure that the data type in the table is Number and not string.
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
ALSO make sure that the data type in the table is Number and not string.

I already informed in my earlier post that the erratic field is text field but anyway I changed this to number field and then code is working fine. So, it seems that it has soem problem with text field.

I am not aware how to properly write for text field.

The final code is:
Public Sub AddRows2()
Dim i As Integer, BottleID As Integer

For i = 1 To Me.rowcount
botID = DMax("bottleID", "Tblinkcode") + 1
CurrentDb.Execute "INSERT INTO Tblinkcode (bottleID, MakeID, gradeID, expiry, batchno) VALUES (" & botID & ", " & Me.MakeId & ", " & Me.GradeID & ", #" & Me.Expiry & "#, " & Me.BatchNo & ")"

Next i
End Sub

BatchNo is the field which I must need to keep text type because batch number can contain texts as well.

I used debug.print in immediate window and no errors, it returns correct values for all the fields as typed in form.
 

pr2-eugin

Super Moderator
Local time
Today, 21:46
Joined
Nov 30, 2011
Messages
8,494
To include text just use a single quote before and after the " something like..
Code:
[B][COLOR=Red]'[/COLOR][/B]" & Me.BatchNo & "[B][COLOR=Red]'
[/COLOR][/B]
 

mahenkj2

Registered User.
Local time
Tomorrow, 03:16
Joined
Apr 20, 2012
Messages
459
Thanks again.

Very small thing but I was not aware of. Could not correlate correctly with access help.

I hope I am done for this form with your great help.

best regards.
 

Larz

Registered User.
Local time
Today, 16:46
Joined
Jan 30, 2012
Messages
10
I'm looking for something similar... i think

I would like to create a form, to populate a main table, of which would have drop down and text boxes lets say...
Customer, Product, Location, txt entry, and #of records needed.

Info like Customer, Product, Location, would be drop down boxes which pull from respective tables. The txt entry is variable but would be constant for this purpose. And the last field I want is a field to input a # to say how many new records I need.

Now the information provided here would populate a master table. Its sort of like a order, a customer calls orders ‘x’ amount of shipments, so ‘x’ amount of records need to be generated… we’re talking anywhere from 5 – 60 or more.

The key thing here is that as the new records are being generated, I need an autonumber field to populate itself too in that table. The autonumber is used in the final shipment#... so if a customer orders 50 shipments… then the shipment# would be a string like FieldA & FieldB & “Autonumber Field”

So the ends…
Customer = Mom
Product = Cake
Location = Home
Txt entry = Delivery in 30
# of records = 15

It would then populate Main_Table with 15 records.
# = Autonumber = Records 0115 - 0129
Customer = Customer = All 15 would be Mom
Product = Product = All 15 would be Cake
Location = Location = All 15 would be Home
Txt Entry = DeliveryTerms = All 15 would be Delivery in 30
(plus other fields not relevant here)
Sales # = “FieldA”&”FieldB”&”Autonumber = XXYYRecord# respectively 15x

Any guidance / help would be appreciated.
 

Users who are viewing this thread

Top Bottom