openform in VB

[Dave]

I am filtering a form from another form using the where
condition in the VB code OpenForm. But when the form
opens, all the records are still there even though
(Filtered) is show at the bottom. When I look at the
filter property in the forms design view, the value is -1.
Can anyone tell me why it might be doing this?
Dave.

 

[Dirk Goldgar]

I am filtering a form from another form using the where
condition in the VB code OpenForm. But when the form
opens, all the records are still there even though
(Filtered) is show at the bottom. When I look at the
filter property in the forms design view, the value is -1.
Can anyone tell me why it might be doing this?
Dave.

Please post the code that opens the form.

 

[Dirk Goldgar]

I am filtering a form from another form using the where
condition in the VB code OpenForm. But when the form
opens, all the records are still there even though
(Filtered) is show at the bottom. When I look at the
filter property in the forms design view, the value is -1.
Can anyone tell me why it might be doing this?
Dave.

Please post the code that opens the form.

 

[Dave]

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , MyNumber =
[Form_TasksOutstanding].PMNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , MyNumber =
[Form_TasksDue].PMNumber
End If

If the number of days over due is bigger than 0
(ie over due) then it should open the TasksOutstanding
form on that record and the same for if it is not over
due, but it should open the workordersdue form. Hope this
is enough.
Dave.

 

[Dave]

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , MyNumber =
[Form_TasksOutstanding].PMNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , MyNumber =
[Form_TasksDue].PMNumber
End If

If the number of days over due is bigger than 0
(ie over due) then it should open the TasksOutstanding
form on that record and the same for if it is not over
due, but it should open the workordersdue form. Hope this
is enough.
Dave.

 

[Dirk Goldgar]

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , MyNumber =
[Form_TasksOutstanding].PMNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , MyNumber =
[Form_TasksDue].PMNumber
End If

If the number of days over due is bigger than 0
(ie over due) then it should open the TasksOutstanding
form on that record and the same for if it is not over
due, but it should open the workordersdue form. Hope this
is enough.
Dave.

You're not using the right syntax for the WhereCondition argument of the
OpenForm method. This argument should be expressed as a string; plus,
your reference to the module of the form to be opened (e.g.,
"[Form_TasksOutstanding]") is unnecessary, and is probably actually
causing the forms to be opened even before the OpenForm method executes.

I believe what's been happening is that the expressions such as

MyNumber = [Form_TasksOutstanding].PMNumber

have had the effect of opening (hidden) the form named, then comparing
the value of the PMNumber field on that form to MyNumber, arriving at a
value of True (-1) or False (0), and then setting the opened form's
Filter to the string representation of that True/False value.

Try this revised version instead:

'----- start of revised code -----
Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , _
"PMNumber = " & MyNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , _
"PMNumber = " & MyNumber
End If
'----- end of revised code -----

 

[Dirk Goldgar]

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , MyNumber =
[Form_TasksOutstanding].PMNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , MyNumber =
[Form_TasksDue].PMNumber
End If

If the number of days over due is bigger than 0
(ie over due) then it should open the TasksOutstanding
form on that record and the same for if it is not over
due, but it should open the workordersdue form. Hope this
is enough.
Dave.

You're not using the right syntax for the WhereCondition argument of the
OpenForm method. This argument should be expressed as a string; plus,
your reference to the module of the form to be opened (e.g.,
"[Form_TasksOutstanding]") is unnecessary, and is probably actually
causing the forms to be opened even before the OpenForm method executes.

I believe what's been happening is that the expressions such as

MyNumber = [Form_TasksOutstanding].PMNumber

have had the effect of opening (hidden) the form named, then comparing
the value of the PMNumber field on that form to MyNumber, arriving at a
value of True (-1) or False (0), and then setting the opened form's
Filter to the string representation of that True/False value.

Try this revised version instead:

'----- start of revised code -----
Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , _
"PMNumber = " & MyNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , _
"PMNumber = " & MyNumber
End If
'----- end of revised code -----

 

[Dave]

'----- start of revised code -----

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , _
"PMNumber = " & MyNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , _
"PMNumber = " & MyNumber
End If
'----- end of revised code -----

I have tried the code you gave my and it returns an error
reading: Syntax error (missing operator) in query
expression 'PMNumber = 1234'
Any ideas why?
Dave.

 

[Dave]

'----- start of revised code -----

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , _
"PMNumber = " & MyNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , _
"PMNumber = " & MyNumber
End If
'----- end of revised code -----

I have tried the code you gave my and it returns an error
reading: Syntax error (missing operator) in query
expression 'PMNumber = 1234'
Any ideas why?
Dave.

 

[Dave]

Note: in using the single-quote (') as the text-value
delimiter, I've

assumed that MyNumber will never contain that character. If I'm wrong
in that assuption, let me know and I'll post a slightly different
version.

The single quote (') is not used in MyNumber but the same
error has appear all the same. Perhaps it is because of
some other property rather than the code itself?
Dave.

 

[Dave]

Note: in using the single-quote (') as the text-value
delimiter, I've

assumed that MyNumber will never contain that character. If I'm wrong
in that assuption, let me know and I'll post a slightly different
version.

The single quote (') is not used in MyNumber but the same
error has appear all the same. Perhaps it is because of
some other property rather than the code itself?
Dave.

 

[Dirk Goldgar]

The single quote (') is not used in MyNumber but the same
error has appear all the same. Perhaps it is because of
some other property rather than the code itself?

Is PMNumber a text field? If not, what is its data type? What are the
recordsources of the forms you're trying to open?

 

[Dirk Goldgar]

The single quote (') is not used in MyNumber but the same
error has appear all the same. Perhaps it is because of
some other property rather than the code itself?

Is PMNumber a text field? If not, what is its data type? What are the
recordsources of the forms you're trying to open?

 

[Steven Gotz]

I was having trouble getting the DoCmd.OpenForm to filter
for me. This post finally got the point across.

It is amazing what you can find in a forum with a search
function.

Thanks,

Steven

-----Original Message-----

Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , MyNumber =
[Form_TasksOutstanding].PMNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , MyNumber =
[Form_TasksDue].PMNumber
End If

If the number of days over due is bigger than 0
(ie over due) then it should open the TasksOutstanding
form on that record and the same for if it is not over
due, but it should open the workordersdue form. Hope this
is enough.
Dave.

You're not using the right syntax for the WhereCondition argument of the
OpenForm method. This argument should be expressed as a string; plus,
your reference to the module of the form to be opened (e.g.,
"[Form_TasksOutstanding]") is unnecessary, and is probably actually
causing the forms to be opened even before the OpenForm method executes.

I believe what's been happening is that the expressions such as

MyNumber = [Form_TasksOutstanding].PMNumber

have had the effect of opening (hidden) the form named, then comparing
the value of the PMNumber field on that form to MyNumber, arriving at a
value of True (-1) or False (0), and then setting the opened form's
Filter to the string representation of that True/False value.

Try this revised version instead:

'----- start of revised code -----
Dim MyNumber As String

MyNumber = [Form_AllMaintenance].PMNumber

If Me.DaysOverDue.Value > 0 Then
DoCmd.OpenForm "TasksOutstanding", acNormal, , _
"PMNumber = " & MyNumber
Else
DoCmd.OpenForm "TasksDue", acNormal, , _
"PMNumber = " & MyNumber
End If
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.