OK John, thanks
one other thing i noticed and refresh and requery does not work
if I select date in the calendar list box, then SRED in the next list box, then the sub form updates, all good
if i change selection in the SRED list box, the sub also updates
but if i change selection in calendar list box, the sub form does not update
what update code can i add?
(i do have VBA code to go to the first item in SRED when selection change is done in calendar)
Thanks,
Daniel
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, November 18, 2012 11:24:20 PM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
1. Simply open tblCalendar in Design view and set the Format property of
calendarID field to "mmm/dd/yyy".
2. Conditional formatting or formatting using VBA works only for entire
controls, not for controls within a listbox or combo box. You would have
to change your calendar list to a subform so that you have the individual
controls for each field and row. That would mean you would also have to
change the filter in the ListBoxCalendarID Row Source - which would now
become the Record Source of the form, and the filter in the Row Source of
ListBoxSRED_List.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Sunday, November 18, 2012 9:29 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
ok thanks John,
that worked, i always used the pencil to save record
did not know that right arrow "next record" did the same
if you watch carefully, after clicking the pencil it seems to update and
show record 1 of 1 for a split second
and then jump to record 2 of 2
i have 2 more questions then i think this will be ready for use
1.
in the calendar list i want to format the date differently
is there a way to format the current calendarID column?
for now what i did is make another column with this expression and it works
Date: Format([calendarID],"mmm/dd/yyyy")
is there a way to not have to make another column?
if not i can probably remove the calendarID column and use the new one for
filtering the other list box / sub form
or is it better to just hide it?
2.
for the calendar list i would like to grey out or have different foreground
/ background color for Saturdays and Sundays
i found this code for conditional formatting but VBA does not recognize
".ListItems.Item( )"
do you know why? or any other code that will do conditional formatting?
Wiht Me.ListView1
.ListItems.Item(Counter).ForeColor = vbRed
.ListItems.Item(Counter).ListSubItems(1).ForeColor = vbRed
.ListItems.Item(Counter).ListSubItems(2).ForeColor = vbRed
.ListItems.Item(Counter).ListSubItems(3).ForeColor = vbRed
thanks,
Daniel
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, November 18, 2012 1:35:09 AM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
Here's what I did:
1) Select Department 1, Employee 1, 11/4/2012, and the record 11/5/2012,
Department 1, 5, 3
2) Type in 5 in SREDProjectID (the first three values then defaulted), 2
in SREDTime, and some xxx's in one of the comment boxes. Clicked the
right arrow on the navigation bar to force the save.
Result: New record shows up in ListBoxSRED and is selected, subform shows
Record 1 of 1.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Saturday, November 17, 2012 10:57 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Hi John,
if I click new blank record, fill it in and click the little pencil to
save record
it updates listboxSRED
but still shows record 2 of 2, it is a new blank one, i have to click on
the left arrow to select the first record (1 of 1)
seems like the me.requery does not work for me
can you please look at my latest DB (SRED times 6.accdb), it is uploaded
thanks,
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, November 17, 2012 12:05:56 AM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
The limited test that I ran on my copy of the database had the subform
come up with one record after the Me.Requery.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 16, 2012 6:47 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Hi John,
works good, thanks
i was searching a bit and could not find how to set the record back to the
first one in the sub form, because it still says record 2 of 2 after the
list correctly updates
thanks,
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, November 16, 2012 12:14:19 AM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
OK, I got it to work. First, you have to add SRED_ID to the Record Source
of the subform. Access might not recognize that the new field exists
unless you copy the SQL to the clipboard, select some other table (doesn't
matter which one), then paste the SQL back. It's a way to "wake up" the
form to get it to realize you added a new field. Then change your code to
this:
Private Sub Form_AfterUpdate()
Me.Parent!ListBoxCalendarId.Requery
Me.Parent!ListBoxSRED_List.Requery
Me.Parent!ListBoxSRED_List = Me.SRED_ID
Me.Requery
End Sub
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, November 15, 2012 6:46 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Hi John,
I uploaded the latest DB of the time tracking in the same file location
you specified
"SRED times 5.accdb"
thanks,
Daniel
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, November 15, 2012 3:24:29 AM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
I fiddled with a Requery macro, but couldn't get it to work. The problem
is the Requery action takes only one simple argument - blank to requery
the entire form (the subform in this case) or the simple name of a control
on the current form. I don't think you can use "Parent."
I would have to see a fresh copy of your database to see about selecting
the newly added record in the listbox. I'm sure it's possible.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, November 15, 2012 7:29 AM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Thanks again John,
worked, i also added code in the same sub for the other list so the hours
update
I tried to add requery macro "Me.Parent!ListBoxSRED_List"
should that work too??
i was able to set the 3 default values
only problem now is that sub form still says record 2 of 2 once a record
is added
would be nice if the list box would select the newly added record and
sub form to show record 1 of 1
thanks,
Daniel
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 14, 2012 1:19:03 PM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
In the AfterUpdate event of frmSubRed, you need this:
Private Sub Form_AfterUpdate()
Me.Parent!ListBoxSRED_List.Requery
End Sub
But note that the list box will update to show the new record ONLY if the
Employee, Department, and Date match the current filter. If you add, for
example, a record for another date, it won't show up. You really should
add code to set the DefaultValue of those three fields in the subform to
match the current filter so that the user is encouraged to enter new rows
that only match the current filter.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 14, 2012 9:43 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
ok, i uploaded the file in the location you specified
Thanks John,
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 14, 2012 12:19:10 PM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
You are confusing CONTROL SOURCE with ROW SOURCE. And you cannot attach
anything to a message posted to this forum - it won't go through. If you
want us to look at your database, you must upload it to Files /
2_AssistanceNeeded.
http://tech.groups.yahoo.com/group/MS_Access_Professionals/files/2_Assistan
ceNeeded/
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 14, 2012 9:14 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Hi John,
one list box uses date_ID as row source
the other uses SRED_ID
I attached the access file that might be easier
sorting is done from top to bottom
in combo box select department then
in combo box select employee then
in list box select date then
in list box select specific time recording for that date
final is the sub form
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 14, 2012 11:54:46 AM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
SRED_ID might be the Control Source for List Box 2, but it's not the ROW
SOURCE - which should be a query or an SQL statement. I also need to see
the Record Source of the form inside the subform control (SQL if it's a
query), the Link Master Fields / Link Child Field properties of the
subform control, and any code you're running in AfterUpdate of the form
inside the subform control.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 14, 2012 8:38 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] sub form refreshing
Hi John,
row source is SRED_ID
and in query for sub form
hidden field SRED_ID
has criteria [Forms]![frmSRED]![ListBoxSRED_List]
(open and close main for will refresh data)
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 13, 2012 11:32:06 PM
Subject: Re: [MS_AccessPros] sub form refreshing
Daniel-
What is the Row Source of List Box 2?
What is the Record Source of the subform, and do you have any Link
properties set in the subform control?
What code do you have in AfterUpdate of the form inside the subform
control?
By the way, the fact that the form is modal has no bearing on the problem.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: Dan Abc <daniel2478a@yahoo.ca>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 14, 2012 6:37 AM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] sub form refreshing
Hi All,
i have 2 combo boxes and 2 list boxes and one sub form
on a modal dialog
- combo box 1 filters combo box 2 and refreshes it
- combo box 2 filters list box 1 and refreshes it
- list box 1 filters list box 2 and refreshes it
these all work and use requery macro
list box 2 is used to select a record on subform and that works too
but if i add a record to the subform, it will show record 2 of 2 but not
refresh it
requery from list box 2 for "after update" event does not work
i have to select record 1 of 2
and re-select one on the combo boxes or list boxes
is there another way to refresh the sub form?
thanks,
Daniel
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (28) |
Tidak ada komentar:
Posting Komentar