requery my subform based on date criteria from the main form

[ConfusedMay]

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

Private Sub txtDate_Click()

Calendar7.Visible = True
If Not IsNull(txtDate) Then
   Calendar7.Value = txtDate.Value
Else
  Calendar7 = DateValue(Now())
End If

End Sub

Private Sub txtDate_AfterUpdate()
Calendar7 = txtDate
Me.txtDate.SetFocus
Forms!supervisor!Child181.Requery
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...

[mseo]

try to replace the textbox control with combobox
and try this

Private Sub Date_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar7.Visible = True
Calendar7.SetFocus
end sub

set the default value of the subform date control =[Forms]![main form name]![txtdate]
hope this helps

[ConfusedMay]

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

SELECT IIf([InOut]=1,"In","Out") AS Status, dbo_v_log.time, dbo_v_log.ID, DateValue([timestamp]) AS [date]
FROM dbo_v_log
WHERE (((dbo_v_log.ID)=[forms]![supervisor]![textid]) AND ((DateValue([timestamp]))=[forms]![supervisor]![txtdate]))
ORDER BY dbo_v_log.TimeStamp;

please help... Am I missing something?

[mseo]

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

SELECT IIf([InOut]=1,"In","Out") AS Status, dbo_v_log.time, dbo_v_log.ID, DateValue([timestamp]) AS [date]
FROM dbo_v_log
WHERE (((dbo_v_log.ID)=[forms]![supervisor]![textid]) AND ((DateValue([timestamp]))=[forms]![supervisor]![txtdate]))
ORDER BY dbo_v_log.TimeStamp;

please help... Am I missing something?

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

Private Sub Calendar7_click()
Combo15.value = Calendar7.value
Combo15.SetFocus
Calendar7.Visible = False
End Sub

Private Sub Combo15_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar7.Visible = True
Calendar7.SetFocus
If Not IsNull(Combo15) Then
Calendar7.value = Combo15.value
Else
Calendar7.value = Date
End If
End Sub

change the combobox name to fit your control name

[NeoPa]

ConfusedMay:

Private Sub txtDate_AfterUpdate()
Calendar7 = txtDate
Me.txtDate.SetFocus
Forms!supervisor!Child181.Requery
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).

[ConfusedMay]

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

[mseo]

you are welocome
glad we could help