Microsoft Access - Do While Loop problem

[MoNkeE]

Hey all,

I'm making a Microsoft Access database for maintaining stock and purchasing etc. What I have at the moment is when you fill in the purchasing form, you click on the Submit button and that runs an append query appending certain information into another table - all that works fine.

However, I want to introduce a Quantity value. This is so when ordering - for example - 4 PCs, I can just fill in the Quantity field as 4, rather than having to enter all the information 4 times. In order to append it to the table, i've come up with the below code:

Counter = 0

Do
Counter = Counter + 1
X = Me.Quantity.Value
SQL = "INSERT INTO tblAsset ( ID_Har_Pur, Description, Asset_Type ) " & _
      "SELECT tblPurchasing.ID_Asset, tblPurchasing.Description, tblPurchasing.Purchase_Type " & _
      "FROM tblPurchasing " & _
      "WHERE (((tblPurchasing.Purchase_Type)='Hardware') AND ((tblPurchasing.ID_Pur)=[Forms]![frmPurchasingNew].[ID_Pur].[Value])) OR (((tblPurchasing.Purchase_Type)='Telecommunications')) OR (((tblPurchasing.Purchase_Type)='Technical Library')) OR (((tblPurchasing.Purchase_Type)='Equipment')) OR (((tblPurchasing.Purchase_Type)='Mobile'));"

DoCmd.RunSQL SQL

Loop While Counter <= X

    MsgBox "Requisiton Submitted.", vbOKOnly

The problem I'm getting with it at the moment, is that it seems to be running an endless loop - it just sits there calculating for ages until I interrupt it.

Me.Quantity is the Quantity field on my form (which is obviously open when this code is run).

If anyone can help me with this, I'd greatly appreciate it.

Ta,

-Wills

 

[MoNkeE]

Nope, sadly that's not fixed it

-RaZ

 

[MoNkeE]

Basically, if I order 4 PCs, I only want to input it once. But when it comes to maintaining the stock of those computers, I need individual fields for the computer name, serial number, what software's installed etc..

That's why I need the single record from purchasing appending four times (or whatever the total quantity is) to a different table - to manage the asset

I'll give your suggestion a try now.

Ta,

-Raz

 

[MoNkeE]

Numpty, you defy your name - you're a genius! That's worked a treat

If you're interested, the code is now:

For Counter = "1" To Me.Quantity

SQL = "INSERT INTO tblAsset ( ID_Har_Pur, Description, Asset_Type ) " & _
      "SELECT tblPurchasing.ID_Asset, tblPurchasing.Description, tblPurchasing.Purchase_Type " & _
      "FROM tblPurchasing " & _
      "WHERE (((tblPurchasing.Purchase_Type)='Hardware') AND ((tblPurchasing.ID_Pur)=[Forms]![frmPurchasingNew].[ID_Pur].[Value])) OR (((tblPurchasing.Purchase_Type)='Telecommunications')) OR (((tblPurchasing.Purchase_Type)='Technical Library')) OR (((tblPurchasing.Purchase_Type)='Equipment')) OR (((tblPurchasing.Purchase_Type)='Mobile'));"

DoCmd.RunSQL SQL

Next

I don't understand how it works, considering there's nothing specifying to keep adding 1 to the counter until it equals the quantity, but WAHEY! It works!

-RaZ

 

[MoNkeE]

The reason it's set to 1 is that when it's at 0 and the quantity is 4, it'll actually append 5 (it seems to count 0). So using 1 offsets the number of records it appends

Seriously, thanks for your help - you've made me a very happy chappy

Ta,

-RaZ