Update a Field Via form

[Guest]

I'm trying to update a field TRAN_Flag when the ADM_DT and SERV_DT are equal.
I'm chose not to use a query as I am sorting by PAT_ACCT_NBR and looping
through my record set because I need to read each record to look for other
information. Only problem I'm having at the moment is actually setting the
field to 1 in my table.

If rs.Fields("ADM_DT") = rs.Fields("SERV_DT") Then
Dim strSQL1 As String
strSQL1 = "UPDATE rab_fullday SET rab_fullday.TRAN_Flag = 1 " & _
"WHERE PAT_ACCT_NBR =" & rs.Fields("PAT_ACCT_NBR") & _
" AND SERV_DT =" & rs.Fields("SERV_DT")
DoCmd.RunSQL strSQL1

 

[Stefan Hoffmann]

hi Sash,

I'm trying to update a field TRAN_Flag when the ADM_DT and SERV_DT are equal.
I'm chose not to use a query as I am sorting by PAT_ACCT_NBR and looping
through my record set because I need to read each record to look for other
information. Only problem I'm having at the moment is actually setting the
field to 1 in my table.

Is your field TRAN_Flag in the record set your are looping?

You can use this in your loop:

If rs![ADM_DT] = rs![SERV_DT] Then
rs.Edit
rs![TRAN_Flag] = 1
rs.Update
End If


mfG
--> stefan <--

 

[Marshall Barton]

I'm trying to update a field TRAN_Flag when the ADM_DT and SERV_DT are equal.
I'm chose not to use a query as I am sorting by PAT_ACCT_NBR and looping
through my record set because I need to read each record to look for other
information. Only problem I'm having at the moment is actually setting the
field to 1 in my table.

If rs.Fields("ADM_DT") = rs.Fields("SERV_DT") Then
Dim strSQL1 As String
strSQL1 = "UPDATE rab_fullday SET rab_fullday.TRAN_Flag = 1 " & _
"WHERE PAT_ACCT_NBR =" & rs.Fields("PAT_ACCT_NBR") & _
" AND SERV_DT =" & rs.Fields("SERV_DT")
DoCmd.RunSQL strSQL1

That looks like it's logically consistent.

One possible thing to check is if either of the PAT_ACCT_NBR
or SERV_DT fields are Text type. If so you need to quote
the values. For example, If both are Text fields:
"WHERE PAT_ACCT_NBR =""" & rs.Fields("PAT_ACCT_NBR") & _
""" AND SERV_DT =""" & rs.Fields("SERV_DT") & """"

The only other thing that I can think of is that the Update
is running, but you are expecting the recordset to reflect
the changes in the remainder of the loop. This will not
happen unless you do things differently. First you need to
use a synchronous method to run the query. Use Execute
instead of RunSQL (see VBA Help for details). Second, you
would need to Requery the recordset, but this will lose your
place in the loop.