openform in VB

  • Thread starter Thread starter Dave
  • Start date Start date
D

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.
 
Dave said:
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.
Click to expand...

Please post the code that opens the form.
 
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 said:
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.
Click to expand...

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
Click to expand...


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 -----
 
'----- 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 -----
Click to expand...

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.
 
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.
Click to expand...

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 said:
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?
Click to expand...

Is PMNumber a text field? If not, what is its data type? What are the
recordsources of the forms you're trying to open?
 
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-----
Dave said:
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.
Click to expand...

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
Click to expand...


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)


.
Click to expand...
 
Back
Top