By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,939 Members | 1,684 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,939 IT Pros & Developers. It's quick & easy.

requery my subform based on date criteria from the main form

P: 57
Hi,

I'm using access 2003. I have a textbox called txtdate on the main form and when I click this textbox I have a calendar pop up and users can select which date they want it to be appear on this textbox.
I also have a subform called child181 that the source object is coming form a query called InOut. This subform will show all the in and out inventories.
What I want is when user select the date from the calendar, I want the subform to automatically requery and shows all the in and out inventory for that date.

Here is my code on the txtdate
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_Click()
  2.  
  3. Calendar7.Visible = True
  4. If Not IsNull(txtDate) Then
  5.    Calendar7.Value = txtDate.Value
  6. Else
  7.   Calendar7 = DateValue(Now())
  8. End If
  9.  
  10. End Sub 
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2. Calendar7 = txtDate
  3. Me.txtDate.SetFocus
  4. Forms!supervisor!Child181.Requery
  5. End Sub 
I did the same thing to the gotfocus event, but somehow it won't requery unless I click another field on the main form. Could someone please help me...???

Thank you...
Jul 19 '10 #1
Share this Question
Share on Google+
6 Replies


mseo
100+
P: 181
try to replace the textbox control with combobox
and try this
Expand|Select|Wrap|Line Numbers
  1. Private Sub Date_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. Calendar7.Visible = True
  3. Calendar7.SetFocus
  4. end sub
  5.  
set the default value of the subform date control =[Forms]![main form name]![txtdate]
hope this helps
Jul 19 '10 #2

P: 57
Hi Mseo,

I changed the textbox into combobox and created the mousedown event like you told me to, but it still not requerying my subform.

my subform is based on a query, here is the code for the query
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf([InOut]=1,"In","Out") AS Status, dbo_v_log.time, dbo_v_log.ID, DateValue([timestamp]) AS [date]
  2. FROM dbo_v_log
  3. WHERE (((dbo_v_log.ID)=[forms]![supervisor]![textid]) AND ((DateValue([timestamp]))=[forms]![supervisor]![txtdate]))
  4. ORDER BY dbo_v_log.TimeStamp;
  5.  
  6.  
please help... Am I missing something?
Jul 19 '10 #3

mseo
100+
P: 181
@ConfusedMay
ok
so, you want to filter the subform based on the criteria in the mainform not for populating the date value in the subform, right?
let me know if that right

and here is the whole code to get the calendar works

Expand|Select|Wrap|Line Numbers
  1. Private Sub Calendar7_click()
  2. Combo15.value = Calendar7.value
  3. Combo15.SetFocus
  4. Calendar7.Visible = False
  5. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo15_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. Calendar7.Visible = True
  3. Calendar7.SetFocus
  4. If Not IsNull(Combo15) Then
  5. Calendar7.value = Combo15.value
  6. Else
  7. Calendar7.value = Date
  8. End If
  9. End Sub
change the combobox name to fit your control name
Jul 19 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
ConfusedMay:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2. Calendar7 = txtDate
  3. Me.txtDate.SetFocus
  4. Forms!supervisor!Child181.Requery
  5. End Sub 
I would consider replacing line #4 with a reference that refers to your subform. I would not expect this to be found in the Forms() collection (as you seem to be trying to use). See Referring to Items on a Sub-Form for full details of how to do this using your own object names (You haven't shared this information so I can't go any further just now).
Jul 20 '10 #5

P: 57
I figured it out. The problem is my calendar. After deleting the calendar and create a new calendar again, the requery worked. Thank you so much for all your help...
Jul 20 '10 #6

mseo
100+
P: 181
you are welocome
glad we could help
Jul 20 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.