For Each Record in Table ....?

[laaacux]

Hi,

I never used this function in VBA because I am bit confused how to use it correct.
So, I have table GL_LINES with Colums LINE ID, bla bla bla, GL_LINE_FLAG

I want that after click on button GL_LINE_FLAG column lines are updated to "1" with specific LINE_ID.

I try use For Each //In but looks like I have but dummy at VBA.

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("GL_LINES")
...?
...?

For each ??GL_LINES_FLAG?? in rst (??GL_LINES - table??)
If ??LINE_ID?? = Forms!frm_LINES_ALL.LINE_ID.Value Then
??GL_LINE_FLAG??? = 1
End if
Next

how to set that I need GL_LINES_FLAG?
etc...
Can anyone explain me - I would like to understand how to create it for such case ... but after reading a lot of msdn.mic... pages I got more lost...

Thanks in advance

 

[r.harrison]

Hi,

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("GL_LINES")
...?
...?

For each ??GL_LINES_FLAG?? in rst (??GL_LINES - table??)
If ??LINE_ID?? = Forms!frm_LINES_ALL.LINE_ID.Value Then
??GL_LINE_FLAG??? = 1
End if
Next

I think an SQL statement will be what you're looking for.

Try CurrentDB.Execute ("UPDATE [GL_LINES] SET [GL_LINE_FLAG] = 1 WHERE [LINE_ID] = " & Forms!frm_LINES_ALL.LINE_ID.Value & ";")


If Forms!frm_LINES_ALL is the current form, then replace this with me

Doing it this way you do not need to loop through the recordset as it will update all the records that match the WHERE statament.

 

[laaacux]

Yes I agree, that in this case it would work.

But if ( ok this I will need in future) -
I will need change GL_LINES_AMOUNT based on the same record, but depending on GL_LINE_CUR_AMOUNT and CUR_RATE added in form.

For example:
Update GL_LINES_AMOUNT
Where LINE_ID = Me.frm_LINES_ALL.Value with GL_LINE_CUR_AMOUMT * Me.frm_LINES_ALL.CUR_RATE.Value

Form have CUR_RATE = 0.5 and LINE_ID 3

ID// LINE_ID // GL_LINE_AMOUNT // GL_LINE_CUR_AMOUNT
1 // 2// // 20
2 // 3// x //10
3 // 3// x //5
4 // 4// //3

Result should be that LINE_ID 3 have GL_LINE_AMOUNT "5" and "2,5"

I have no idea how to do with SQL script in VBA as I need update the same record with new data in column GL_LINES_AMOUNT, based on GL_LINE_CUR_AMOUNT

Hope you understand what I mean.

 

[vbaInet]

Create a separate Rates table and get your current rate from there for the calculation.

 

[laaacux]

There is no problem about getting rate

I have table - GL_LINES_ALL which have LINE_ID, AMOUNT_CUR and AMOUNT_LOC
In FORM -frm_LINES_ALL is only data about LINE_ID and CUR_RATE
after pressing button I need that based on added information in Form script is updating all AMOUNT_LOC with AMOUNT_CUR * FORM.CUR_RATE for each record where LINE_ID = FORM.LINE_ID in TABLE GL_LINES_ALL

 

[vbaInet]

You should not be saving that calculation. Just do it on the fly in a query which was why I mentioned having a separate Rates table.

Here's why:

http://allenbrowne.com/casu-14.html

 

[laaacux]

Hi,
I did it like this, but thanks for reply - it helped

Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbl_GL_LINES_ALL")
Do While Not rst.EOF
If rst!GL_JOURNAL_ID = Forms!frm_GL_journals.GL_JOURNAL_ID.Value Then
db.Execute "UPDATE tbl_GL_LINES_ALL SET LOC_CREDIT_AMOUNT = " &   Nz(Round(rst!CUR_CREDIT_AMOUNT * Forms!frm_GL_journals.CUR_RATE.Value,   2), 0) & " WHERE GL_LINES_ID = " & rst!GL_LINES_ID & ""
db.Execute "UPDATE tbl_GL_LINES_ALL SET LOC_DEBIT_AMOUNT = " &   Nz(Round(rst!CUR_DEBIT_AMOUNT * Forms!frm_GL_journals.CUR_RATE.Value,   2), 0) & " WHERE GL_LINES_ID = " & rst!GL_LINES_ID & ""
End If
rst.MoveNext
Loop
rst.close
Set rst = Nothing