Is there any to run (but not display) a query when you make a change
to a form?

I've got a system now that is tracking backup jobs in our enterprise.
What I've done is first setup a query that will look at the date in a
table and return a numeric value for day of the week to another field
on the table (this field doesn't show on the form). Then I've got
another query that reads what that numeric day of the week code is
and
compares it to a table that lists all the days of the week, and
returns that name of the day to a third field on the table (this
field
shows up on the form).


Basically what I want to have happen is when I select a date on the
form, I want the first query (figure out which numeric day of the
week
corresponds to that date) to run, then the second query to run
(figure
out which named day of the week corresponds to that numeric value).
Here's a snippet of code I've been struggling with that I found
online
- the error is "Method or data member not found" on .RunQuery. I'm
using Access 2007, so I can only assume the website I found this on
was referencing a previous version of Access.


Is there anything analogous to this command in 2007?


Private Sub CurDate_AfterUpdate()
'Turns off the Access warning messages
DoCmd.SetWarnings False
'Converts the dd/dd/dddd date to a numeric day of the
week
DoCmd.RunQuery "qryGetNumericDayofWeek"
'Does a LOOKUP to convert the numeric day to named
day
DoCmd.RunQuery "qryGetVerbalDayofWeek"
'Turns the Access warning messages back on
DoCmd.SetWarnings True
End Sub


Here's what I have in my queries:


qryGetNumericDayofWeek
UPDATE tblLaptopsBackups SET tblLaptopsBackups.NumofWeek = Weekday
([CurDate],1);


qryGetVerbalDayofWeek
UPDATE tblLOOKUPDaysoftheWeek, tblLaptopsBackups SET
tblLaptopsBackups.DayofWeek = [tblLOOKUPDaysoftheWeek].[DayoftheWeek]
WHERE (((tblLaptopsBackups.NumofWeek)=[tblLOOKUPDaysoftheWeek].
[DayNum]));


The form uses the field tblLaptopsBackups.DayofWeek as the field with
the spelled out day of the week on the form; tblLOOKUPDaysoftheWeek
is
just a listing of all the numeric values for the Weekday() function
and what they equal in spelled out days.

On Apr 9, 12:22*pm, chickenfriedsteak <ow... (AT) chickenfriedsteak (DOT) us>
wrote:
Also, I've tried this with the DoCmd.RunSQL() command, but nothing
happens when I use that code (no errors, but it also doesn't update
the table / form). Below is a copy of the other code I've tried that
does nothing:

Private Sub CurDate_AfterUpdate()
'Turns off the Access warning messages
DoCmd.SetWarnings False
'Converts the dd/dd/dddd date to a numeric day of the
week
DoCmd.RunSQL ("UPDATE tblLaptopsBackups SET
tblLaptopsBackups.NumofWeek = Weekday([CurDate],1)")
'Does a LOOKUP to convert the numeric day to named day
DoCmd.RunSQL ("UPDATE tblLOOKUPDaysoftheWeek,
tblLaptopsBackups SET tblLaptopsBackups.DayofWeek =
[tblLOOKUPDaysoftheWeek].[DayoftheWeek] WHERE
(((tblLaptopsBackups.NumofWeek)=[tblLOOKUPDaysoftheWeek].[DayNum]))")
'Turns the Access warning messages back on
DoCmd.SetWarnings True
End Sub

chickenfriedsteak wrote:

Could you use Format()? Ex:
d = date()
? format(d,"dddd")
Thursday

On Apr 9, 1:13*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
I just did some more testing, and I don't think I was clear on what I
was hoping to have happen with the snippet of code I was working on.
When I select a date in my tabular data entry form in Access, I wanted
it to plug the named day of the week into my table (and update it on
the form).

I've been playing around with some other code since then, here's what
I've come up with so far:

Private Sub CurDate_AfterUpdate()

Dim strDayofWeek As String

strDayofWeek = Format(CurDate, "dddd")

DoCmd.SetWarnings False
DoCmd.RunSQL = "UPDATE tblLaptopsBackups SET DayofWeek=" &
strDayofWeek & " WHERE " & Me.CurDate & "= CurDate"
DoCmd.SetWarnings True

End Sub

I'm getting "Argument not optional" at Me.CurDate in the SQL
statement. Any idea?

On Apr 20, 11:16*am, chickenfriedsteak <ow... (AT) chickenfriedsteak (DOT) us>
wrote:
Sorry, I posted the wrong code - that was from a little test I was
running / playing with in Notepad. The real code I'm trying to fix
is:

Private Sub CurDate_AfterUpdate()

Dim strDayofWeek As String
Dim dteCurDate As Date

strDayofWeek = Format(CurDate, "dddd")
dteCurDate = Me.CurDate.Text

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblLaptopsBackups SET DayofWeek=" & strDayofWeek
& " WHERE " & dteCurDate & "= CurDate")
DoCmd.SetWarnings True

End Sub

chickenfriedsteak wrote:

I usually surround date fields with #. What you may condider doing is
strS = "UPDATE tblLaptopsBackups SET DayofWeek=" &
strDayofWeek & " WHERE " & Me.CurDate & "= CurDate"
Debug.print strS
then going to the debug window, copy to clipboard, and paste result into
a new query SQL window. Try to run on some test data.