update query in subform

[clayton ledford]

It might be easier to provide an example of what i'm trying to do. Is this
possible?

I have a table with three fields:

Record_ID (pk)
Date (pk)
Status
Other_Field1
Other_Two

The status field is a yes/no (-1,0) check-box field.

In my form, i set up a sub-form so that i can view all records for a given
record_ID. I want to set up the "after update" event on the Status field to
say something like:

If the status of this record is YES, then all records with dates earlier
than this one should also have a Status equal to YES.

 

[KARL DEWEY]

BACKUP DATABASE ----------- BACKUP DATABASE

Have your "after update" event on the Status field to run this query --
UPDATE YourTable SET YourTable.Status = -1 WHERE YourTable.Record_ID =
[Forms]![YourForm]![YourSubForm]![Record_ID_TextBox] AND
YourTable.YourDateField <=
[Forms]![YourForm]![YourSubForm]![YourDateFieldTextBox] AND
[Forms]![YourForm]![YourSubForm]![YourCheckBox] = -1;

You can test the query by having the form/subform open and cursor in field
of a record to be tested and then run the query alone.

 

[clayton ledford]

Thanks for the help guys. I have tried Ken's method, and i'm receiving a
data type mismatch in criteria error when running this. The debugger is
highlighting the line:

cmd.Execute

i suspect that this isn't the line causing the error though.

Any thoughts? Troubleshooting this will be difficult by myself because i'm
still learning when it comes to code. This one is outside my skill set so
far.

Clay

Or do it in code in the Status control's AfterUpdate event procedure:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE YourTable" & _
" SET Status = TRUE" & _
" WHERE YourTable.Record_ID = " & Me.Record_ID & _
" AND YourTable.Date < #" & _
FORMAT(Me.[Date],"yyyy-mm-dd") & "#"

cmd.CommandText = strSQL

If ctrl = True Then
cmd.Execute
End If

But what if the user makes a mistake and sets the Status control to True and
then reverses this by unchecking the check box? The prior records' Status
column will have already have been updated to True. Would it be better to
update the prior records to whatever the current record's Status control is
updated to, i.e. if the user checks it then the prior ones are updated to
True, if they uncheck it then they are all updated to False? If so amend the
SQL statement and call the Execute method unconditionally:

strSQL = "UPDATE YourTable" & _
" SET Status = " & Me.Status & _
" WHERE YourTable.Record_ID = " & Me.Record_ID & _
" AND YourTable.Date < #" & _
FORMAT(Me.[Date],"yyyy-mm-dd") & "#"

cmd.CommandText = strSQL
cmd.Execute

I've assumed that by "all records with dates earlier than this one" you mean
"all records with the same record_id and dates earlier than this one", not
all prior records of any record_id value.

BTW 'Date' should be avoided as a column name. It’s the name of a built in
function, so using it as an object name could cause confusion. Something
more specific such as 'TransactionDate', 'OrderDate' etc should be used.

Ken Sheridan
Stafford, England

It might be easier to provide an example of what i'm trying to do. Is this
possible?

I have a table with three fields:

Record_ID (pk)
Date (pk)
Status
Other_Field1
Other_Two

The status field is a yes/no (-1,0) check-box field.

In my form, i set up a sub-form so that i can view all records for a given
record_ID. I want to set up the "after update" event on the Status field to
say something like:

If the status of this record is YES, then all records with dates earlier
than this one should also have a Status equal to YES.

 

[clayton ledford]

I was testing this before putting it in my actual application. Perhaps
something about the date? I have three fields:

Table is titled: test_update_by_click
Account = Text field
Date = Date field
Status = Yes/No field


Private Sub Status_AfterUpdate()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = " & Me.Account & _
" AND test_update_by_click.Date < #" & _
Format(Me.Date, "mm-dd-yyyy") & "#"


cmd.CommandText = strSQL

If ctrl = True Then
cmd.Execute
End If


End Sub

Thanks again for the help.

Clay:

The error will almost certainly be in the SQL statement. It may refer to a
column of text data type which needs wrapping in quotes characters. Post
back with the full code and details of the columns involved and their data
types.

Ken Sheridan
Stafford, England

Thanks for the help guys. I have tried Ken's method, and i'm receiving a
data type mismatch in criteria error when running this. The debugger is
highlighting the line:

cmd.Execute

i suspect that this isn't the line causing the error though.

Any thoughts? Troubleshooting this will be difficult by myself because i'm
still learning when it comes to code. This one is outside my skill set so
far.

Clay

Or do it in code in the Status control's AfterUpdate event procedure:

[quoted text clipped - 67 lines]

If the status of this record is YES, then all records with dates earlier
than this one should also have a Status equal to YES.

 

[Hans Up]

clayton ledford wrote:
Table is titled: test_update_by_click

Account = Text field
Date = Date field
Status = Yes/No field


Private Sub Status_AfterUpdate()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = " & Me.Account & _
" AND test_update_by_click.Date < #" & _
Format(Me.Date, "mm-dd-yyyy") & "#"

Since Account is text type, surround the Me.Account value with quotes.

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = '" & Me.Account & _
"' AND test_update_by_click.Date < #" & _
Format(Me.Date, "mm-dd-yyyy") & "#"
Debug.Print strSQL

I added the Print so you can see the finished SQL statement in the
Immediate Window (Ctrl+g keyboard shortcut will take you there.) You
can copy the SQL statement and paste it in the SQL View of a new query
for further trouble shooting.

Good luck,
Hans

 

[Douglas J. Steele]

Try renaming your Date field. Date is a reserved word, and using reserved
words for your own purposes can lead to problems. For a comprehensive list
of names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it:

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = " & Me.Account & _
" AND test_update_by_click.[Date] < #" & _
Format(Me.[Date], "mm-dd-yyyy") & "#"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was testing this before putting it in my actual application. Perhaps
something about the date? I have three fields:

Table is titled: test_update_by_click
Account = Text field
Date = Date field
Status = Yes/No field


Private Sub Status_AfterUpdate()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = " & Me.Account & _
" AND test_update_by_click.Date < #" & _
Format(Me.Date, "mm-dd-yyyy") & "#"


cmd.CommandText = strSQL

If ctrl = True Then
cmd.Execute
End If


End Sub

Thanks again for the help.

Clay:

The error will almost certainly be in the SQL statement. It may refer to
a
column of text data type which needs wrapping in quotes characters. Post
back with the full code and details of the columns involved and their
data
types.

Ken Sheridan
Stafford, England

Thanks for the help guys. I have tried Ken's method, and i'm receiving
a
data type mismatch in criteria error when running this. The debugger is
highlighting the line:

cmd.Execute

i suspect that this isn't the line causing the error though.

Any thoughts? Troubleshooting this will be difficult by myself because
i'm
still learning when it comes to code. This one is outside my skill set
so
far.

Clay

Or do it in code in the Status control's AfterUpdate event procedure:

[quoted text clipped - 67 lines]
If the status of this record is YES, then all records with dates
earlier
than this one should also have a Status equal to YES.

 

[clayton ledford]

I'm not sure what the plural of genius is... but you all qualify. It was the
account field issue. Thanks for the help.

Clearly Date should always be avoided as a column name, but as in this case
its qualified by the column name on the one hand and Me on the other there
should be no problem. The error arises from the Account column being a text
data type as Hans spotted, so the value needs delimiting with quotes:

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = """ & Me.Account & _
""" AND test_update_by_click.Date < #" & _
Format(Me.Date, "mm-dd-yyyy") & "#"

The corollary of course is that if Clay ever did want to return the current
date in the form's module with the date function he'd have to qualify that
also with VBA.Date.

Renaming the column something like TestDate would be the best bet, though.

Ken Sheridan
Stafford, England

Try renaming your Date field. Date is a reserved word, and using reserved
words for your own purposes can lead to problems. For a comprehensive list
of names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it:

strSQL = "UPDATE test_update_by_click" & _
" SET Status = TRUE" & _
" WHERE test_update_by_click.account = " & Me.Account & _
" AND test_update_by_click.[Date] < #" & _
Format(Me.[Date], "mm-dd-yyyy") & "#"

I was testing this before putting it in my actual application. Perhaps
something about the date? I have three fields:

[quoted text clipped - 67 lines]

earlier
than this one should also have a Status equal to YES.