adding records into table with nested loop

[tweetyng]

I have a form linked with a subform such as:

main form: (table A with ID: primary key)
ID :1545
CustomerID (auto-number) 500
and some other fields

subform (table B, primary key: CustomerID and ViolationNO)(CustomerID in both tables A & B linked)

ViolationsNO Score CustomerID
1 5 500
2 5 500
......and so on up to the violation id 27.
26 2 500
27 3 500


In the subform each CustomerID always has 27 records (from 1 - 27 violations). So I used subform with continuous form and enter 27 times to add up to 27 records. It's working fine with that. However, I would like to do something like adding a button named "Adding Violations" . When click on this button, it's automatic adding 27 records from 1-27 with the same CustomerID. or any other easiest way. Is it possible? Please help, I'm very appreciated. Thanks

 

[lespaul]

maybe create a table (tblViolNo) with a single field and enter a record for each violation (1 - 27)

Then in the button run a query like:

INSERT INTO tableB (select CustomerNumber, tblViolNo FROM Table1, TblViolNo Where CustomerNumber = form!FormName!CustomerNumberTextField)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[randy700]

How about a FOR...NEXT loop?

For x = 1 to 27
   [i]Run the query here, using x to insert the violation number.[/i]
Next

Randy

 

[tweetyng]

Thanks for your reply lespaul. However, how I'm gona Insert your code into my sample code below, please help
I try with the code below but it's not working

Private Sub cmdAddingViolation_Click()


Dim rst As Recordset
Dim wsp As Workspace
Dim VioID As Integer

Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans

Set rst = CurrentDb.OpenRecordset("tableB")
Set VioID = 1

With rst
For VioID = 1 To 27
.AddNew
![CustomerID] = Me.txtCustomerID
![ViolationsNO] = VioID
.Update
.Close
Next VioID
End With

Set rst = Nothing
wsp.CommitTrans

Exit_cmdAddViolation_Click:
Exit Sub

End Sub

TN (USA). I'm using Windows XP, Crystal Reports 11 and SQL Server

 

[tweetyng]

I took out the

Set VioID =1 then
it's only adding record number 1 and after the loop i got an error "3420 - Object invalid or no longer set"

TN (USA). I'm using Windows XP, Crystal Reports 11 and SQL Server

 

[lespaul]

well if you're doing it in VBA you don't need the other table, I thought you were looking for an SQL solution.

 

[PHV]

Set rst = CurrentDb.OpenRecordset("tableB")
With rst
For VioID = 1 To 27
.AddNew
![CustomerID] = Me.txtCustomerID
![ViolationsNO] = VioID
.Update
Next VioID
[!].Close[/!]
End With
Set rst = Nothing
wsp.CommitTrans

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tweetyng]

Thanks lespaul and PHV for your repond. I haven't noticed the ".close" before the Next statement. Once, PHV - thanks, I'm very appreciated. That's helping alots.

TN (USA). I'm using Windows XP, Crystal Reports 11 and SQL Server