looping VB qry?

[samonwalkabout]

unfortantly its more complicated than that, the chemical use is determined by a production schedule. So if i make a product, it uses x amount of chemical 1, x amount of 2, and x amount of 3 ect ect ect.

So a Change to the schedule will effect a change in the adjustment figure for many chemicals.

The adjustment figure will be updated into the stock table and then your qry will run, using these new adjustment figures.

But i would have to run it one time for each chemical effected.


bad news theres over 300 chemicals and 10 products.

Most products use around 50-80 chemicals.

Thats a lot of button pressing for an update.

I need it to run for chemical 1, stop run for 2, stop etc etc. Until it get to the end.

I dont mind coding the entrie thing if its the only way, but i guess its not.

what if there was a table that had a record that said 1, the VB looked at this and ran for chemical one, the last part of the vb updated this figure to 2 and ran it again, this time i would be looking for 2 ect ect. Would that be quicker or slower in the long run?

 

[Mark Wild]

I'm glad I've stuck to the easy world of banking and finance...

I assume the relationship between products and chemicals is a many to many, with an intermidiary table between them?

If so, can you not have the combo box pulling through the prodcut names. You could then amend the SQL query to include this table and update all products where the product reference is x

 

[samonwalkabout]

i dont think that will work as most raw materials are used in more than one product. I if i was to go with a large bit of code

intChemicalID = 1

strSQL = "SELECT [Chemical ID], [Week End Date],Startamt, endamt, adjustment " _
& "FROM newstock " _
& "WHERE [Chemical ID]=" & intChemicalID & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

lngAmnt = rst!Startamt

With rst
.MoveFirst

Do Until .EOF
.Edit
!Startamt = lngAmnt
!endamt = !Startamt - !Adjustment
'!Adjustment = !Adjustment
.Update
lngAmnt = !endamt
.MoveNext
Loop

end with

intChemicalID = 2

strSQL = "SELECT [Chemical ID], [Week End Date],Startamt, endamt, adjustment " _
& "FROM newstock " _
& "WHERE [Chemical ID]=" & intChemicalID & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

lngAmnt = rst!Startamt

With rst
.MoveFirst

Do Until .EOF
.Edit
!Startamt = lngAmnt
!endamt = !Startamt - !Adjustment
'!Adjustment = !Adjustment
.Update
lngAmnt = !endamt
.MoveNext
Loop
end with

ect ect all the way to 300.

That way if someone wants to make changes to 1 chemical I.e If a delivery changes then they can and if they make changes to the production schedule then they will be able to update everything at once.

Is this a bad idea? What are the odds of it taking ages or crashing alot?

 

[Mark Wild]

I'd avoid that solution like the plague!!!

At the worst change the sub to a function called Name(dim inChemicalID as integer)

and delete the dim intchem statement from in the code.

Then, call the function from another sub

Call Name(1)
Call Name(2)

This will allow you to reuse the code a lot more.

I still don't think this is the answer though. A many to many relationship will allow a product to have many chemicals and also for chemcials to be in many products.

The intermdiary table would have the PKs from the other two tables as the fields

Product Chemical
1 1
1 2
1 3
2 1
2 3
2 4

etc, so will allow for what you say

If you set the combo box up to use product, then you could see all the chemicals needed for it, and update them all as relevant (see function example)

 

[samonwalkabout]

if you look at the sample i sent you there is a table called prochem, it this in the correct format for this to work?

 

[Mark Wild]

I've inserted a combo box and modified the code for you.

Have a look to see if it makes sense. Am limited to only testing it for Chem 1 as that is the only chem that has data in for the starting amnt etc in the new stock tbl

 

[samonwalkabout]

i get an complie error

"user defined, type not defined"

on line

Dim db As Database

??

 

[Mark Wild]

try dao.database. sometimes you need to tell access whether you're using ADO or DOA.

 

[samonwalkabout]

added doa, still no good, had a look at the refs there were a couple missing so added Micosoft DAO 3.6 object libary.

code moved on but now doesnt regonise the .edit
says @method or data member not found

Am i using the wrong refs?

 

[samonwalkabout]

got around the refs its running, but says no current record.

and highlights

lngAmnt = rst!Startamt

i think this could be more to do with the lack of data in the new stock table as its been stripped.

I Will test it some and let you know Thanks again for your help Mark this has gone well beyond my experiance and normal code and hopefully it will all be working soon

 

[Mark Wild]

You're correct on the reason for the error. that's the reason I wasn't able to test it for you.

I'm certainly well out of my depth at times and am often helped by people here, so no probs.

 

[samonwalkabout]

SQL

I have his code running in the database no worries, but I want to adjust it so it takes the adjustment value from a qry not from the newstock table itself as im having to update this before i run the code and it causing trouble. The code reads...

Dim rst As Recordset
Dim dt As Date
Dim lngAmnt As Long
Dim db As DAO.Database
Dim strSQL As String, strSQL2 As String
Dim rstProChem As Recordset
Dim intChemicalID As Integer, intProcessID As Integer

Set db = CurrentDb

intProcessID = [Forms]![update]![cmbProcess]

strSQL2 = "SELECT prochem.[Chemical ID], Process.[Process ID] " _
& "FROM Process INNER JOIN prochem ON Process.[Process ID] = prochem.[Process ID] " _
& "GROUP BY prochem.[Chemical ID], Process.[Process ID] " _
& "HAVING (((Process.[Process ID])=" & intProcessID & "));"

Set rstProChem = db.OpenRecordset(strSQL2, dbOpenDynaset)

With rstProChem
.MoveFirst
Do Until .EOF

intChemicalID = ![Chemical ID]

strSQL = "SELECT [Chemical ID], [Week End Date],Startamt, endamt, adjustment " _
& "FROM newstock " _
& "WHERE [Chemical ID]=" & intChemicalID & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

lngAmnt = rst!Startamt

With rst
.MoveFirst

Do Until .EOF
.Edit
!Startamt = lngAmnt
!endamt = !Startamt + !adjustment
'!Adjustment = !Adjustment
.update
lngAmnt = !endamt
.MoveNext
Loop
End With
.MoveNext
Loop
End With

End Sub

I have a qry called "Adjustment" with [Chemical ID], [Week End Date] and [Adjustment] but cant seem to get the code right to slot it in. I think its my poor grasp of sql joins.

Thanks in Advance.

 

[samonwalkabout]

For anyone who followed down through this thread i Solved this in the end i Just needed to recalc the data before the code ran to take the adjustment amount that had just been entered and cal the new end amount and then run the code seems to work some bumps and bugs but it could be down to the user rather than the code.