looping VB qry?

[samonwalkabout]

Say i have a stock table. With

/Weekend Date //RawMaterial//Start Amount//End Amount.

End amount is calculated.

How can i get the End amount for date 1 to be the start amount for date 2. and the end amount for date 2 the start amount for date 3 ect ect. So i can predict stock level for rest of year.

Do i need to use some kind of looping VB or Qry?

Any help would be great as every think else works i just cant get the my head around this one????

 

[Mark Wild]

Do you want to update all the records for the year as apart form the one you are editing, they will all have the same start and end qty.

I'd go more along the lines of picking up the last end amount (do you store this or calc it on the fly, in which case this won't work) when the input form is opened and setting this as the value for the start amount for the new recrod.

This way every time someone goes to update the current week, the opening amount will correctly be the previous weeks end amnt.

 

[samonwalkabout]

Thanks for you suggestion, unfortunately the function needed is a little different. The Start Amount for a week has minus (as stock is used) and plus (new delivery's) giving the end amount. However the use of stock is planned for the year in advance, and so are the delivery's! The system will show a user what will happen if they increase usage in say 4 months time for a week. The system will tell them if they will have enough stock, if they do no worries, if not it flags the delivery that they need to increase or move to account for the increase in stock. Basically I need to use the end stock value for a week as the start value for the next week for the whole year at once. I.e. if I change 1 input or output value to the system all the stock values will change simultaneously till the end of the year. Complex hey!

I think I need a make table qry to create a second copy of updated stock data with the new values, then copy this over the original. But im not sure and cant seem to get it to work.

 

[Mark Wild]

Samon,

Whilst the below would need a lot of modification, is the logic the sort of thing your after?

Dim rst As Recordset
Dim dt As Date
Dim intAmnt As Integer
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset(Qry Or Table, dbOpenDynaset)

dt = Date 'choose a start date
intAmnt = Me.TextboxOnForm - Me.AmntUsed + Me.StockDelivered

With rst
    .MoveFirst
        
        Do Until .EOF
            .Edit
            'are the dates going to pre-populated?  if not, change the do until to something more relevant
                !Date = DateAdd("d", 7, dt)
                !StartAmnt = intAmnt
                !EndAmnt = 1 'however you calculate yor end amount
            .Update
            intAmnt = !EndAmnt
            .MoveNext
        Loop
End With

 

[samonwalkabout]

Thanks Mark!

That kinda does make sense, but 'Me.TextboxOnForm' value confuses me should i try and use this function from a form?

I am looking at around 6000-7000 records but they will be there from day one so the EOF function should work. I will play with it over the weekend and try to get something working. Thanks again i can see a light at the end of the tunnel.

 

[Mark Wild]

Glad it helped a bit.

The 'Me.TextboxOnForm' was just a guess at a field you might have had on the form.

intAmnt = Me.TextboxOnForm - Me.AmntUsed + Me.StockDelivered would probably refer to existing stock levels-amount used+delivery.

Have a look over the weekend and revert if you want anything clarifying.

 

[samonwalkabout]

Diffrent stock types

Have been struggling away with this some more! The qry wont run propely but im not really sure why

Dim rst As Recordset
Dim dt As Date
Dim intAmnt As Integer
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset(STOCK)

dt = Date() 'choose a start date
intAmnt = Me.startamnt + me.adjustement 'a text box on a form

With rst
.MoveFirst

Do Until .EOF
.Edit
!Date = DateAdd("d", 7, dt) 'what does this do change the date?
!StartAmnt = intAmnt
!EndAmnt = startamnt + adjustment
.Update
intAmnt = !EndAmnt
.MoveNext
Loop
End With

Im not sure what the .edit part does and what the update part does differently. does !date = dateadd change the date of the record? Or just effect where the new info is recorded.

I also have a problem that im keeping the stock levels of several different things, any idea how can i account for this rather that all the records being treated as one stock item.

I have managed to do this with a couple of qrys and make table qrys, but it only does one record at a time I.e each time thay are all ran.

Any help would be great

 

[Mark Wild]

Oh the joys of that a Monday bring...

!EndAmnt = startamnt + adjustment

you need to have me. then the field. At the moment, Access won't know what these fields refer to.

With rst - anthing with . is an action on the recordset. Anything with ! will refer to a field in the recordset.

.MoveFirst ' go to the first record in the recordset

Do Until .EOF ' keep looing through the below until all the records are updated. If you're adding "new" dates, this .eof should be until the date field in the table equals a date that you specify.

.Edit - what are you doing to the recordset? Do you want to add a record (.add) or edit an exsiting record (.edit)

!Date = DateAdd("d", 7, d) - if all the dates in in the system, then you don't need this line. If they're not, then you need to use .add. This date line will add 7 days to the date from the line before in the table
!StartAmnt = intAmnt
!EndAmnt = startamnt + adjustment - change to refer to the fields on the form
.Update - the changes that you have made to the recordset above are not written to the recrodset (table) until you .update them

intAmnt = !EndAmnt - keep these two lines in memory for when they are used in the next line
dt=!date
.MoveNext
Loop
End With

I also have a problem that im keeping the stock levels of several different things, any idea how can i account for this rather that all the records being treated as one stock item.

Not quite sure what you mean. can you be a bit more specific

Rgds,

 

[samonwalkabout]

okay so far giving me some trouble, just to check this code will add the startamt adn adjustment to make the endamnt. Then update this figure to the next line as the startamnt?


Option Compare Database

Dim rst As Recordset
Dim dt As Date
Dim intAmnt As Integer
Dim db As Database

Set db = "mrp2000"
Set rst = db.OpenRecordset


endAmnt = Me.StartAmnt + Me.adjustment 'a text box on a form

With rst
.MoveFirst

Do Until .EOF
.Edit
!StartAmnt = !endAmnt
!endAmnt = !StartAmnt + !adjustment
.Update
!endAmnt = !StartAmnt
.MoveNext
Loop
End With


The other problem is that i have diffrent things in stock, so if the code moves through all the data taking the endamount of one bit of stock and using it as the startamount of a diffrent stock iteam it wrong. I need it to move through the dates of each item seperately. thanks

 

[Mark Wild]

Yep, sounds about right.

On the recordset front, your statement is still a bit short

set rst=db.openrecordset(strSQL,dbopendynaset)

where strSQL is a query code that selects which product you want to update. THink about how you could call the module for each type of product whilst re-using as much code as possible.

Have a go and then post a sample db if your still stuck as I'm not very good at explaining some of this.

 

[samonwalkabout]

okay still not 100% my raw materials are stored in a diffrent table they all have a unquie chemical iD so i guess this could be used? Here is a sample of the database stock tqable was the way i was trying to do it, and newstock is the table way that i think your method will fit into. If you could get it to work i would be very greatful as its starting to drive me mad! thanks


----

had to strip a lot of bits out fot it to fit let me know if it makes sense.

 

[Mark Wild]

Hiya,

I'm afraid I've only got A97 on the site I'm on at the moment. Could you save it down for me please.

Cheers

 

[samonwalkabout]

wow came out at half the size!

 

[Mark Wild]

Do you have a form that you've tried this on so that I can look at how your fields etc are set out.

 

[samonwalkabout]

Heres one of the forms... the layout isnt that important at this stage if you can just get it to work somehow on or off a form that would be great, as long as i can do the update once a change is made to the adjustment figures then it should be fine.

Thanks

 

[Mark Wild]

Hi Samon,

I've put some code behind the button. I hope it makes things a bit clearer as to what I was trying to explain.

It still needs some work as I have hard coded in the chemical id at the moment.

HTH

 

[samonwalkabout]

Fantastic!!!! It works!! Thanks so much for your help i was starting to go mad there!!!! I knew there must be some way of doing it!

this line

& "WHERE [Chemical ID]=" & intChemicalID & ";"

does the intchemicalID mean first chem id in the record set?

If i had a combo on the form where the user selected the Chemical ID to update would it update only those with that ID?

with something like

& "WHERE [Chemical ID]=" & [combo1]& ";"

 

[Mark Wild]

Glad it helped!

I'd probably use

intChemicalID=me.cmbName...

& "WHERE [Chemical ID]=" & intChemicalID & ";"


As it makes it easier for people to debug later, but in you could certainly use something similar to you example.

 

[samonwalkabout]

Thats working great!!! and i can run specific updates when say a delivery for a certain raw material is increase ect. Now all i need is a way for getting it to run for each raw material seperately but all at once. For instances where several are changed at once.

I could code for each chemicalID and then get a macro to run them all in turn? I think there is a smarter way though... Any idea's?

 

[Mark Wild]

Now you're using code, try and stay away from macros - they're the devils work (lol).

Am I correct in thinking you want to input the chemical id, agreement etc for each of the chemicals, and then run the update?

If so, is this any quicker than doing one and running it, then doing the next?

If you wanted to do each one sequentially you could have the cmb +1, so the next chem number is in the cmb, and then refresh the form. THis would at least speed up some of the process?