problem inserting multiple records into Table (1 Viewer)

[pds8475]

Hi
I have a Form called FrmAddDueIn which is for adding multiple records into the Table DueInTable. Im using a textbox on the form called AmountTxt to specify the number of records to add when the Button SaveItemsBtn is clicked. I am passing all values to variables before the Loop which contains the insert statement. But this is only saving one record. If I insert a breakpoint on my code(on the loop) and step through the code it asks for the values of the variables. So the values seem to getting cleared after the first iteration of the loop.

Any help would be appreciated.

 Private Sub SaveItemsBtn_Click()
 
    Dim CU As String
    Dim Man As String
    Dim Ty As String
    Dim Model As String
    Dim PN As String
    Dim DI As String
    Dim NT As String
    Dim Count As Integer
    
    Count = Me.AmountTxt
    CU = Me.CustomerCombo
    Man = Me.ManCombo
    Ty = Me.TypeCombo
    Model = Me.ModCombo
    PN = Me.PNCombo
    NT = Me.NumberTxt
    DI = Me.DateDueInTxt

    
    Do
  
    Dim StrSQL As String
   
   StrSQL = "INSERT INTO DueInTable(Customer, Manufacturer, Type, Model, PartNumber, ATRNumber, DueInDate) VALUES(CU,Man,Ty,Model,PN,NT,DI)"
   DoCmd.RunSQL StrSQL
   
    Count = Count - 1
    Loop Until Count = 0
    
   
    
     
 
End Sub

 

[arnelgp]

StrSQL = "INSERT INTO DueInTable(Customer, Manufacturer, Type, Model, PartNumber, ATRNumber, DueInDate) VALUES('" & CU & "','" & Man & "','" & Ty & "','" & Model & "','" & PN & "','" & NT & "',#" & Format(DI,"mm\/yy\/yyyy") & #)"

 

[pds8475]

That partially fixes the loop. when you first click the Save button only one record gets saved. But if you fill the form out again all records get saved.

I have attached a cut down version of my database so you can see exactly what is going on.

 

[arnelgp]

try this one

 

[pds8475]

Now it's adding one to many. What seems to be happening is that because of the On lost Focus event of the textbox NumberTxt. When NumberTxt loses focus when you click the save Items button. it automatically saves the record and then carries on doing the save. If I remove the On Lost focus event it works perfectly. But the subform doesn't update.
I however need the Subform to update while not messing up the save event.

 

[Minty]

Try until count =1 ?
Also I'm pretty sure Count is a reserved word.

 

[arnelgp]

i remove the filter from your query, is this what you need?

 

[pds8475]

No I need to be able to filter records based on the ATRNumber. Plus if you add a second lot of items without closing the form first an extra Item is saved

It may help if I say what the process is.

My company will receive a notification from a customer that they are sending in x amount of equipment on an ATRNumber(order number) for their Bonded stock. This can be 10 of one sort of equipment, 5 of another and so on.

But also at any point up to the DueInDate the customer may wish to add equipment to the ATRNumber.

The Staff in the office need to be able to see the quantity of different equipment on the ATRNumber and be able to add Items to the ATRNumber.

 

[pds8475]

I seem to have got very close to what I want by making NumberTxt an unbound text box. Now the right amount of records are being saved. But to view the results after clicking the save Items button in the subform I have to copy the text in the textbox NumberTxt and then click(not paste) in the ATRNumber field on the subform.

Im thinking that doing this is causing the query to be rerun? therefore it should now just be a matter of rerunning the query on the Onclick event Of the save item button after it has done the save.

 

[Minty]

Just try refreshing or re-querying the subform as the last action of the update code.

 

[pds8475]

Yes that did the trick. Time for a celebratory beer now