Best way to call openform on a loaded form?

[dabruins]

I have a form (FindForm) that I use to retrieve a record in a MySQL database and then open a second unbound form (MainForm) displaying numerous fields from the record. In the following code I am able to open the form successfully and things seem to work ok but the user may choose to return to the FindForm and search for another record in which case the FindForm does not close as it should and I cannot get focus on the Main form. It also does not display the correct record info in the Main Form. The Form_Activate event on the main Form contains the instructions to bind the data from the records to the controls of the form as well as set up many other display features. I'm not sure if the Form_Activate event is the best way to go about doing this? Looking for any suggestions on the best way to go about doing this.

Private Sub cmdFindJob_Click()
On Error GoTo Err_Handler

strJobno = Me.Jobno
strDept = Me.dept

Dim rst As ADODB.Recordset
Dim myconn As ADODB.Connection

Set myconn = SetMyConn

strSql = "SELECT jobid FROM usijobs as j " & _
"WHERE j.jobno = '" & strJobno & "' AND dept = '" & strDept & "';"

Set rst = New ADODB.Recordset
rst.Open strSql, myconn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rst.EOF Or Not rst.BOF Then
rst.MoveFirst

lngJobSetupOpenargs = rst!jobid

strFrmName = "USIJobSetup"
If Application.CurrentProject.AllForms(strFrmName).IsLoaded Then
Debug.Print "Form is loaded"
Forms.Item(strFrmName).SetFocus
' DoCmd.Close acForm, "USIFindJobSetup", acSaveNo
Else
DoCmd.OpenForm strFrmName, acNormal, , , acFormEdit, acWindowNormal, rst!jobid
End If

Dim jsform As Form
Set jsform = Application.Forms(strFrmName)
jsform.Controls("Jobno") = strJobno
jsform.Controls("Dept") = strDept

DoCmd.Close acForm, "USIFindJob", acSaveNo

Else
MsgBox "No Job record exisits for the criteria you have provided.", vbOKOnly, "NO JOB RECORD"
End If

rst.Close
Set rst = Nothing

myconn.Close
Set myconn = Nothing

Exit_cmdFindJob_Click:
Exit Sub

Err_Handler:
Dim erADO As ADODB.Error
For Each erADO In myconn.Errors
Select Case erADO.NativeError
Case 1045
Debug.Print erADO.Number & " " & erADO.description & " " & erADO.SQLState & " " & erADO.NativeError
Set myconn = SetMyConn
Resume Next
Case Else
Debug.Print erADO.Number & " " & erADO.description & " " & erADO.SQLState & " " & erADO.NativeError
Resume Exit_cmdFindJob_Click
End Select
Next
If Err.Number = 3709 Then
Call SetMyConn
Resume Next
Else
MsgBox Err.Number & " " & Err.description
Resume Exit_cmdFindJob_Click
End If

End Sub

 

[ChanceD2]

OK, I am totally confused by your post. So, let's see if I have this right.

You have 3 forms. Form1, Form2 and Form3.
Form1 shows Level1 of data. Upon selecting a Level1 data, you are brought to Level2 and Level3 data (Form2 and Form3, respectivly)
Form1 opens Form2 and Form3.
Form2 shows Level2 data (a little more detailed data)
Form3 shows Level3 data (super detailed data of Level2 data)

Am I even close?
Please dumb this down as much as possible.

C-D2

 

[dabruins]

No there are only two forms that I am working with. The first (Find Form) only prompts the user to provide a job number and dept name. The only other control on this form is a command button (Findbutton), that when pressed, uses the job number and dept to find a corresponding jobsetup record in the database.

If a valid jobsetup record is found it opens the JobSetup form displaying the values of the record found in the numerous controls of the form. The Find Form which was used to open the JobSetup form is still open at this point.

If the user attempts to provide another job number and dept name in the FindForm, and then presses the Findbutton the Jobsetup form is not reloaded with the new record data from the second find attempt. I cannot get focus back on the JobSetup form at this point and it does not display the results of the second find query that was attempted.

I've since changed this action to automatically close the jobsetup form if it is open when a Find is attempted from the Findform. I've moved the code that binds the Jobsetup form controls to the record data from the Form_activate sub to the Form_Load sub of the Jobsetup form.

It now works as I want it to but I'm still not certain that this is the best way to handle this.

 

[ChanceD2]

Dabruins,

OK, I now fully understand what you are trying to do. I have two responses for you. You can continue to do what you are doing, of course. However, I'd like to offer another idea. So, I am going to outline both.

-------------
1 - Your Way
-------------
If you choose to go with what you have now, this is what I would do.
1. On your find form, I would put more code behind that button.
2. Make the code query the tables based on what is entered into the Job Number / Dept Name.
3. Then, create an IF
4. If a Job Number / Dept Name is found, then open the 2nd form.
5. ELSE (if the Job Number / Dept Name is NOT found) then send a message to the user "Job Number / Dept Name not found"

NOTE: This would keep you from having to set focus and all of that other 'stuff'.

-------------
2 - Alternate Way
-------------
OK, I am making some assumptions here. That being said, let me clearly state what I am assuming. I am assuming that:
1. There are a list of departments (Dept Name)
2. Each "Department" has a list of "Job Numbers"

OK, that being said, here is what I would suggest as an alternate way:

1. Copy that "JobSetup" form and name it something else. 2. On that form, put in 2 combo boxes on the far left.
3. In the first combo box put in all of the department names
4. Make sure that the BOUND COLUMN is some ID field or whatever.
5. In the second combo box put in all of the "Job Numbers"
6. Make sure that the BOUND COLUMN is some ID field or whatever.

OK, that was the easy stuff. Now onto the difficult stuff.

1. Make combo box 2 filtered on the ID (bound) column in cobmo box 1.
2. Make the recordset of the form itself based on the bound column in combobox 2.
3. On the On-Click event in combobox 1, do a REQUERY of combo box 2
4. On the On-Click event in combobox 2, do a REQUERY of the form

Now you have both forms merged into one. This may sound difficult, but if you go step by step you should be able to figure it out.


C-D2

 

[dabruins]

Hi ChanceD2.

Concerning option 1 my code given above does just that; prompt the user for the dept and job number. If it finds a match in the database then it opens the jobsetup form otherwise it informs the user it could not and the focus remain on the findform.

As for option 2 I cannot provide a lengthy list of job number and dept ids in the form as I do not want to bog down the opening of that form by querying for a list of thousands of records, hence the prompt to provide the job number and dept to the user. I am NOT useng BOUND forms. All of my forms are UNBOUND. Quite frankly I don't believe I will ever use a bound form again? This is vital so that I am not burdening the frontend with thousands of records that the user does not need to see. Once I find the record of interest i then bind the controls to the various fields of the recordset retrieved, permitting the user to make changes within the form.

The retrieving of the records is not a problem for me I was just wondering about the order of calls to the form events Form_OPEN,Form_LOAD, Form_ACTIVATE etc. Again I placed the code for setting up the jobsetupform on the form_activate event and was wondering if this option resulted in my problems with regaining focus if the form was already open when a new search was initiated from the FindForm. I've already worked my way around it by simply closing the find form after the search is conducted successfully. I was just wondering if anyone could shed some light on the form events and how they might be at the root of the problem I was experiencing.

Thanks for your time in trying to come up with an answer to this problem but I think your spending valuable time trying to provide an answer to a problem I don't have.