Need to create records based on values(2)

[jossimon]

I am new to VBA. I have a table that holds the following information (Item#, Qty). On the click of a button, if Qty > 1, then I need to create a record in a separate table for each Qty. If Qty = 1, then only one record should be created.
Example:
Item# 1,2,3,4,5
Qty 2,1,3,2,1
Perspectively.
Should come out...
Item#
1
1
2
3
3
3
4
4
5

 

[GingerR]

Using no VBA: Create a table i.e. called tblItemQTY. One field, integer, called ItemQTY. ItemQTY will have this data:

1
2
3
4
.
.
.
up to whatever you think is max

Then make a new query. Bring in your table, and this new table as well. Do not make any join between them. Bring down the field Item# from your original table into the query grid. Then bring down the field ItemQTY from the new table into the query grid. In the CRITERIA of ItemQTY, put

Between 1 and [QTY]

This will give you the results you want.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

Let's code:

Private Sub cmdButton_Click()
  Dim i As Integer
  Dim strSQL As String

  If (Nz([Qty],0) > 0) Then
    For i = 1 to [Qty]
      strSQL = "INSERT INTO tblOrders (Item#,TextField) VALUES ("
      strSQL = strSQL & [Item#] & ",'" & [TextField] & "')"
      DoCmd.RunSQL strSQL
    Next i
  Else
    MsgBox "Qty must be 1 or greater."
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[jossimon]

VB Slammer: I did the following code and I get the error "Object Required" on the If Statement Line. What does that mean?

Private Sub Crt_ExportFile_Click()
Dim i As Integer
Dim StrSQL As String

If (Nz(TOrderImpFilter.Shippable, 0) > 0) Then
For i = 1 To TOrderImpFilter.Shippable
StrSQL = "Insert into TExportFile (SITM#,CustomerOrderNumber) VALUES TOrderImpFilter.SITM#, TOrderImpFilter.CustomerOrderNumber from TOrderImpFilter;"
DoCmd.RunSQL StrSQL
Next i
Else
MsgBox "Shippable must be greater than 1."
End If
End Sub

 

[VBslammer]

The field you are checking with the Nz() function should be an actual field on your form, such as [tt]Me![Shippable][/tt].

Also, you have to build the SQL string at runtime using concatenation as I did in my example, something like:

StrSQL = "Insert into TExportFile (SITM#,CustomerOrderNumber) VALUES (" [red]&[/red] Me![SITM#] [red]&[/red] "," [red]&[/red] Me![CustomerOrderNumber] [red]&[/red] ")"

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PHV]

Try this:
Dim i As Integer, q As Integer
Dim StrSQL As String
q = Nz(DLookUp("Shippable", "TOrderImpFilter), 0)
If q > 0 Then
StrSQL = "INSERT INTO TExportFile ([SITM#],CustomerOrderNumber) SELECT [SITM#],CustomerOrderNumber FROM TOrderImpFilter"
DoCmd.SetWarnings False
For i = 1 To q
DoCmd.RunSQL StrSQL
Next i
DoCmd.SetWarnings True
Else
MsgBox "Shippable must be greater than 1."
End If

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

 

[jossimon]

Thank you guys for your help... but this isn't working. It is only inserting one line per Item. If the Shippable (or QTY) is 2 then I need the code to insert two lines for the one item. If you have any further insight I would be very appreciative.

 

[PHV]

Try this and say us what is displayed:
Dim i As Integer, q As Integer
Dim StrSQL As String
q = Nz(DLookUp("Shippable", "TOrderImpFilter"), 0)
[highlight]MsgBox "Shippable = " & q[/highlight]
If q > 0 Then
StrSQL = "INSERT INTO TExportFile ([SITM#],CustomerOrderNumber) SELECT [SITM#],CustomerOrderNumber FROM TOrderImpFilter"
DoCmd.SetWarnings False
For i = 1 To q
DoCmd.RunSQL StrSQL
Next i
DoCmd.SetWarnings True
Else
MsgBox "Shippable must be greater than 1."
End If

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

 

[jossimon]

Shippable is = to 1

 

[jossimon]

If I change my table and make the first item have a quantity of 8, then it enters each item in 8 times. What should happen is that it should look at the Shippable field and if it says 2 for item A then two inserts should occur for that item and if the next item, item B, says 8 then there should be 8 inserts for item B, etc.

 

[PHV]

You have to add a criteria in the DLookUp call.

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

 

[GingerR]

Have you tried my solution? I know it's not coded, but it works!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

GingerR, seems that the problem is to get the correct Qty ...

 

[jossimon]

Ginger, Thank you but Max could be over a hundred. So I don't think this will work for me.

 

[jossimon]

PHV,
I need the Criteria for the DLookup to select the first record in the TOrderImpFilter Table, insert the appropriate amount of records into the TExportFile Table and then move to the next record in TOrderImpFilter. To me it seems that I might be missing a Loop statement, but I don't know how these work too well.

jossimon

 

[PHV]

I guess you need to play with 2 recordset, one reading TOrderImpFilter and the other writing TExportFile.

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

 

[GingerR]

I added an outer loop. Make sure you have a reference to DAO made (in code window, pick TOOLS+REFERENCES from the menu bar; if there's no reference to DAO picked, scroll down until you find Microsoft DAO, pick the highest version). I also first delete all the current records in the TExportFile table, so remove that line if you don't want it:

Function FillTable()
Dim i As Integer, q As Integer
Dim strSQL As String
Dim rs, rsNew As DAO.Recordset

'Delete all records in table TExportFile
CurrentDb.Execute "Delete * from TExportFile"

'Open the TOrderImpFilter table to loop thru it
Set rs = CurrentDb.OpenRecordset("Select * from TOrderImpFilter")

'Open table TEXportFile in order to add records to it
Set rsNew = CurrentDb.OpenRecordset("Select * from TExportFile")


rs.MoveFirst
    While Not rs.EOF
        q = Nz(rs!Shippable, 0)
        If q > 0 Then
          DoCmd.SetWarnings False
          For i = 1 To q
            rsNew.AddNew
            rsNew!CustomerOrderNumber = rs!CustomerOrderNumber
            rsNew![SITM#] = rs![SITM#]
            rsNew.Update
          Next i
          DoCmd.SetWarnings True
        End If
        rs.MoveNext
    Wend

rs.Close
rsNew.Close
End Function

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244