Running a query when you change a form

[chickenfriedsteak]

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.

 

[chickenfriedsteak]

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 dayto 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.

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

 

[Marshall Barton]

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.

Whoa! That's very confusing and best I can figure out you
are trying to **change** (UPDATE) values in both tables.
What is that all about?

If you just want the full name of the day of week, you don't
need those tables or either query. Instead, you can just
use either:
Format(datefield, "dddd")
or
WeekDayName(datefield)

 

[chickenfriedsteak]

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.

Whoa!  That's very confusing and best I can figure out you
are trying to **change** (UPDATE) values in both tables.
What is that all about?

If you just want the full name of the day of week, you don't
need those tables or either query.  Instead, you can just
use either:
        Format(datefield, "dddd")
or
        WeekDayName(datefield)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Yargh! I haven't played much with Access since 2000, and back then I
was doing all my front-end writing in VB, so I'm used to having to
juggle things around, create lookup tables, blah blah blah. I wish I
had done more research to find out Access has such things built in
now!

I put the code in, but now get the following error:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Office Access from
saving the data in the field.

There is no BeforeUpdate or ValidationRule for my text box, so I'm a
little confused on this. Here's the code I threw in:

txtDayofWeek.SetFocus
txtDayofWeek = Format(CurDate, "dddd")

 

[chickenfriedsteak]

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.

Whoa!  That's very confusing and best I can figure out you
are trying to **change** (UPDATE) values in both tables.
What is that all about?

If you just want the full name of the day of week, you don't
need those tables or either query.  Instead, you can just
use either:
        Format(datefield, "dddd")
or
        WeekDayName(datefield)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Solved it:

Me.txtDayofWeek = Format(CurDate, "dddd")

I've been out of the game too long now. Thanks!

 

[chickenfriedsteak]

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.

Whoa!  That's very confusing and best I can figure out you
are trying to **change** (UPDATE) values in both tables.
What is that all about?

If you just want the full name of the day of week, you don't
need those tables or either query.  Instead, you can just
use either:
        Format(datefield, "dddd")
or
        WeekDayName(datefield)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

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?

 

[chickenfriedsteak]

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.

Whoa!  That's very confusing and best I can figure out you
are trying to **change** (UPDATE) values in both tables.
What is that all about?

If you just want the full name of the day of week, you don't
need those tables or either query.  Instead, you can just
use either:
        Format(datefield, "dddd")
or
        WeekDayName(datefield)

- Show quoted text -

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?- Hide quoted text -

- Show quoted text -

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

 

[Marshall Barton]

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.

When you costruct an SQL statement in code. the values you
are concatenating into the SQL need to be seen as literals.
this means that you must use the appropriate delimiters.
Add a Debug.Print strSQL line before running the query so
you can see the result of the concatenations

For text values (e.g. day of week), the delimiter can be
either ' or ". Because, ' is used as a normal character in
many contexts (e.g. Joe's place. O'Hare, won't, etc), I
almost always use ".

For date/time value, the delimitor is # Because dates are
converted to text using settings in Windows (can be
different on different machines), letting Access/SQL do the
conversion for you can result in invalid date literals. You
should use a specific format to make sure Access/SQL can
understand the dates.

Numbers do not use a delimitor.

Putting all that together:

strSQL = = "UPDATE tblLaptopsBackups SET DayofWeek=""" &
strDayofWeek & """ WHERE CurDate=" &
Format(Me.CurDate,"\#yyyy-m-d\#")

BUT, I see no reason to store the day of week in a table.
The date can easily displayed as day of week in any
form/report/datasheet text box whenever you want to see it
by just setting the text box's fFormat property to dddd

Another point, RunSQL can generate warnings and it runs
asynchronuously (you don't know when it is completed). It
is almost always better to use the Execute method.