Condition in form, requery in subform with new condition

[saddist]

Hello,

I just can't get thru it. I have a form and a subform in it. Subform is tabular and it displays a query(Id,name,d ate). In main form I have text field "enterDateF rom" and "enterDateT o". I want subform to display only records where 'data' is between 'enterDateFrom' and 'enterDateTo'. How? I guess I will have to write some AfterUpdate function in VB but I don't know how. I will accually have to change the query, not just requering.

saddist

[puppydogbuddy]

Hello,

I just can't get thru it. I have a form and a subform in it. Subform is tabular and it displays a query(Id,name,d ate). In main form I have text field "enterDateF rom" and "enterDateT o". I want subform to display only records where 'data' is between 'enterDateFrom' and 'enterDateTo'. How? I guess I will have to write some AfterUpdate function in VB but I don't know how. I will accually have to change the query, not just requering.

saddist

do you mean something like the following? replace the object names that I used with the actual object names used in your application.

Private Sub enterDateTo_AfterUpdate
If Not IsNull(Me!enterDateFrom) And Not IsNull(Me!enterDateTo) Then
        "Select Id,name, date From YourQuery where date Between [enterDateTo] And [enterDateFrom]";
Else
         MsgBox "you did not enter the dates properly"
         Exit sub
End Sub

[JConsulting]

do you mean something like the following? replace the object names that I used with the actual object names used in your application.

Private Sub enterDateTo_AfterUpdate
If Not IsNull(Me!enterDateFrom) And Not IsNull(Me!enterDateTo) Then
        "Select Id,name, date From YourQuery where date Between [enterDateTo] And [enterDateFrom]";
Else
         MsgBox "you did not enter the dates properly"
         Exit sub
End Sub

To followup on PDBs response

you want to set the subform recordsource using the SQL he prepared, Or apply a filter. In either case, you might want to create two buttons. One to actually run the "filter" and one to restore the original recordset of the subform.

Private Sub enterDateTo_AfterUpdate
dim strSQL as string
If Not IsNull(Me!enterDateFrom) And Not IsNull(Me!enterDateTo) Then
strSQL = "Select Id,name, [date] From YourQuery where date Between #" & me.[DateFrom] & "# And #" & me.[DateTo] & "#;"
me.mysubform.recordsource = strSQL
Else
         MsgBox "you did not enter the dates properly"
         Exit sub
End Sub

[puppydogbuddy]

Hi Jeff,
Good catch! Can't believe I forgot to set the recordSource.

pDog

[JConsulting]

Hi Jeff,
Good catch! Can't believe I forgot to set the recordSource.

pDog

Hi Pdog,
I was just stopping by...Happy to help.
J

[Lysander]

me.mysubform.recordsource = strSQL

Hi JConsulting,

I too am trying to change the recordset of a subform but when I put this in the main form, it does not compile

Me.fsubPostPreg nancyDataEntry. RecordSource=st rSQL

I get error "Method or Data Member not found"

Using Access 2003

[puppydogbuddy]

Hi JConsulting,

I too am trying to change the recordset of a subform but when I put this in the main form, it does not compile

Me.fsubPostPreg nancyDataEntry. RecordSource=st rSQL

I get error "Method or Data Member not found"

Using Access 2003

Lysander,
try it this way:

Me!fsubPostPreg nancyDataEntry. Form.RecordSour ce=strSQL


pDog

[Lysander]

Lysander,
try it this way:

Me!fsubPostPreg nancyDataEntry. Form.RecordSour ce=strSQL


pDog

Works a treat, thanks

[ravikumarmp]

Hi,

I am new member . I am having doubt in ms access forms


I am having one dropdown list in main form when we select the data from dropdown list the filter data should display in sub form in the corressponding fields.

i used

str = "select EmployeeName,De signation,Salar y,EmailId,Phone from Employee where EmployeeId='" & cboEmployee & "'"
me.subformname. form.recordsour ce=str


it shows an error "previous operation is cancelled"

[puppydogbuddy]

Hi,

I am new member . I am having doubt in ms access forms


I am having one dropdown list in main form when we select the data from dropdown list the filter data should display in sub form in the corressponding fields.

i used

str = "select EmployeeName,De signation,Salar y,EmailId,Phone from Employee where EmployeeId='" & cboEmployee & "'"
me.subformname. form.recordsour ce=str


it shows an error "previous operation is cancelled"

Site rules require you to post your own question instead of hijacking a closed question..just think, if everyone did this we would never close a question. However, I will make an exception this time.

Your syntax should probably be as shown below because employeeId is probably numeric, not text. Note also that employeeid has been added to the select list. It should be the bound column of your combobox, If you don't want it to display, you can hide it by setting its column width to zero. Finally, note that your syntax for the recordsource is general syntax. you need to replace "subformnam e" with the actual name of your subform control.

str = "select EmployeeId,Empl oyeeName,Design ation,Salary,Em ailId,Phone from Employee where EmployeeId=" & cboEmployee
me.subformname. form.recordsour ce=str