Opening a form from another form

[Guest]

Hi all,
Upon the initial opening of a form (form1), I would like it to then open a
second form (form2), but only if there are records within the table that the
second form is controlled by. I am guessing I would need to use the onLoad
event for form1.
To make it more complex, it I was to open form2 manually, it currently
performs this check, but instead of either opening the form or not, it
questions the user if they would like to add a record or not. I need this to
stay this way as it works great if a user tries to open the form manually
later on, but what I really also need it to do is open upon with the initial
load of form1, but only show up if there are records that meet the criteria
to load it and not prompt the user.
form2 is a task list for employees and should only open if there are open
tasks (controlled by a yes/no) that are available for the currentUser
(employee)
Not sure if this makes sense? Help would be greatly appreciated.
-gary

 

[tina]

you can use a DCount() function to check for records that meet the criteria,
and open the form if the count is greater than zero. something along the
lines of

If DCount(1, "TableName", "SomeField = True " _
& "And AnotherField = UserID") > 0 Then
DoCmd.OpenForm "FormName"
End If

read up on the DCount() function in Help, so you'll understand how it works.

hth

 

[Marshall Barton]

Upon the initial opening of a form (form1), I would like it to then open a
second form (form2), but only if there are records within the table that the
second form is controlled by. I am guessing I would need to use the onLoad
event for form1.
To make it more complex, it I was to open form2 manually, it currently
performs this check, but instead of either opening the form or not, it
questions the user if they would like to add a record or not. I need this to
stay this way as it works great if a user tries to open the form manually
later on, but what I really also need it to do is open upon with the initial
load of form1, but only show up if there are records that meet the criteria
to load it and not prompt the user.
form2 is a task list for employees and should only open if there are open
tasks (controlled by a yes/no) that are available for the currentUser
(employee)

There are several approaches to this problem, but because
you want the form's load event to stay the way it is, I
think you should check if there are records before trying to
open the form.

This ties into the standard way of opening a form with
filtered data by constructing the criteria in code. Using
form1's load event, it would be something along these lines:

Dim strWhere As String
strWhere = "EmployeeID=" & Me.EmployeeID _
& " And taskopen = True"
If DCount("*", "Tasktable", strWhere) > 0 Then
DoCmd.OpenForm "form2", , , strWhere
Else
MsgBox "No open tasks"
End If

 

[Guest]

Thank you so much! I really appreciate the help!
-gary

My final code looked liked this (hopefully it can help others as well!)

If DCount(1, "employee_list", "task_closed = false " _
& "And 'employee =' & CurrentUser()") > 0 Then
DoCmd.OpenForm "CS_TaskList"
End If

 

[Guest]

Marshall, as always, thank you. You have been one who has helped me on
countless occassions. I really appreciate it!
-gary

here is the final code, in hopes it can help others!

If DCount(1, "employee_list", "task_closed = false " _
& "And 'employee =' & CurrentUser()") > 0 Then
DoCmd.OpenForm "CS_TaskList"
End If

 

[Marshall Barton]

I thought you wanted to open the form to the same user's
open tasks?? Shouldn't the OpenForm use a where condition
similar to the DCount?

FYI, using * in a count is a special code that is supposed
to be way more efficient than counting a value or a never
null field.

 

[Guest]

Hi Marshall,
Ohhhh, I see the problem... wow, working on it last night, I only was
playing with one user and did not see what was happening.
I updated the coding and here is the final (working) code:
Form1 (MAIN_MENU) has the following code in the onLoad event:

Dim strWhere As String
strWhere = "assigned_employee = CurrentUser() " _
& " And task_closed = false "
If DCount("*", "employee_list", strWhere) > 0 Then
DoCmd.OpenForm "CS_TaskList", , , strWhere
End If

Form2 (CS_TaskList) has in the onLoad event:

If Me.RecordsetClone.EOF Then
iResponse = MsgBox("No tasks are currently assigned. Would you like to add
a task?", vbYesNo, "Task Manager Notification")
If iResponse = vbYes Then
DoCmd.Close acForm, "CS_TaskList"
DoCmd.RunSQL "INSERT INTO employee_list (date_entered,
assigned_employee ) VALUES (Now(), CurrentUser() ) "
DoCmd.OpenForm "CS_TaskList"
DoCmd.GoToControl "employee"
ElseIf iResponse = vbNo Then
DoCmd.Close
End If
End If

Thanks for the follow up, I really, truly appreciate it!
-gary

I thought you wanted to open the form to the same user's
open tasks?? Shouldn't the OpenForm use a where condition
similar to the DCount?

FYI, using * in a count is a special code that is supposed
to be way more efficient than counting a value or a never
null field.
--
Marsh
MVP [MS Access]

Marshall, as always, thank you. You have been one who has helped me on
countless occassions. I really appreciate it!

here is the final code, in hopes it can help others!

If DCount(1, "employee_list", "task_closed = false " _
& "And 'employee =' & CurrentUser()") > 0 Then
DoCmd.OpenForm "CS_TaskList"
End If

 

[Marshall Barton]

Hi Marshall,
Ohhhh, I see the problem... wow, working on it last night, I only was
playing with one user and did not see what was happening.
I updated the coding and here is the final (working) code:
Form1 (MAIN_MENU) has the following code in the onLoad event:

Dim strWhere As String
strWhere = "assigned_employee = CurrentUser() " _
& " And task_closed = false "
If DCount("*", "employee_list", strWhere) > 0 Then
DoCmd.OpenForm "CS_TaskList", , , strWhere
End If

Form2 (CS_TaskList) has in the onLoad event:

If Me.RecordsetClone.EOF Then
iResponse = MsgBox("No tasks are currently assigned. Would you like to add
a task?", vbYesNo, "Task Manager Notification")
If iResponse = vbYes Then
DoCmd.Close acForm, "CS_TaskList"
DoCmd.RunSQL "INSERT INTO employee_list (date_entered,
assigned_employee ) VALUES (Now(), CurrentUser() ) "
DoCmd.OpenForm "CS_TaskList"
DoCmd.GoToControl "employee"
ElseIf iResponse = vbNo Then
DoCmd.Close
End If
End If

Whoa!!! I have never tried to have a form close itself, add
a record to its record source table and then (re)open
itself. I am flabergasted that that really works.

Actually, there is no need to go through all that. If the
form has no records in its recordset, it will automatically
be positioned on a new record ready for the user to enter
data. It seems like all you need to do is set the values in
the appropriate text boxes and be done with it.

If Me.Recordset.RecordCount = 0 Then
iResponse = MsgBox("No tasks are currently assigned." _
& vbCrLf & "Would you like to add a task?", _
vbYesNo, "Task Manager Notification")
If iResponse = vbYes Then
Me.date_entered = Now
Me.assigned_employee = CurrentUser()
ElseIf iResponse = vbNo Then
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End If

If your users might change their mind after clicking Yes in
the message box and decide not to create the new task, then
it would be safer for you to se the text box's DefaultValue
property instead of the Value.

Me.date_entered = Format(Now, "\#yyyy\/m\/d\#")
Me.assigned_employee = """" & CurrentUser() & """"

 

[Guest]

Hi again,
I had a similar issue before that made me use this "logic" (I use this term
very loosely here!). I am using MySQL tables and I was not able to open to a
"default" record as I had before with Access tables (seemed that I was not
able to add or edit a record that technically did not exist yet) - I found
the only way I could get around the "this record has been edited by another
user" message, was to close the form, create a new record, and then reopen it
to the current record.
Thanks for the code, I will test it out
-gary

 

[Marshall Barton]

I have never used MySQL, but your problem there would
normally imply that your form's current record was dirty and
needed to be saved before adding or editing a different
record.