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

 

[MaxPower]

Counter = 0

Do
Counter = Counter + 1
[B]X = Me.Quantity.Value[/B]
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)='Telecommunication  s')) 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

I don't know a lot about this but can I ask you why the line that I have made bold is inside the loop, is it is constant there is no need to fetch it each time? if it is variable that might be your problem.

 

[MoNkeE]

Nope, sadly that's not fixed it

-RaZ

 

[NumptyUK]

not sure why that isnt working, but try it with a for..next instead


for count = 0 to me.quantity.value
.
.
.
next


Not sure why you need to get the loop working anyway since you say you want to get away from adding multiple records and just put a quanitity value in. Cant you just add the quantity value to the insert?

 

[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

 

[NumptyUK]

the for loop adds one to the counter all by itself each time it hits the Next. You can actualy specify how much it adds by putting a "Step x" bit on the end of the For line, ie "For count = 1 to 4 step 2" would add 2 to Count each time round. Theres just a hidden "Step 1" in there that you dont see since its the default. You can even make it go backwards by putting a Step -2 on it. Anyway, enough of the lesson.

Im not sure why it works either since your using a string "1"

Why not just leave it as 1?

 

[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