Load 2nd form - second one not working after hide - help pls

[Cheryl]

I have a user form that is used as an input/dialog box asking for Job Number
to search by... Once entered and OK is pressed.. it loads another form that
Displays the Information based on the search (from there the user can make
changes/delete the record).. This works fine.. The problem is when I use a
command button to Perform another Search... located on the Displayed
Information form so that they can look for more records.. rather than
completely exiting out of the forms.. I hide my Search form upon leaving the
Search form (if I completely unload.. same problem) and load/displaying the
Display the Information form. I want to use a command button the Display the
Information form to load the Search form again and close the current form. I
get an error message each time.. 'Run-time 361 Can't load or unload this
object' upon trying load/show the search form again..

i.e.
Private Sub cmdSearchAgain_Click()

' Save form contents before closing:
SaveRow

'Convert the date columns so they will reflect properly
Convert_Text_Numbers

'reset the print area
Set_Print_Area

frmJobSearchDisplay.Hide
Load frmSearch
frmSearch.Show
Unload frmJobSearchDisplay

End Sub

I am perplexed.. It has to be right in front of me.. the error.. but I can't
see it... When I go to this form from frmSearch the code is as follows:

Dim Completed As Boolean

Dim FoundCell As Object
Dim CompleteSheet
Dim rngWholebook As Range
Dim LastRow As Integer 'This is the LAST Non Empty Row
Dim sMessage As String
Dim TrimJobField As String
Completed = True

If Len(txtSearchJobNumber) < 1 Then
Completed = False
End If

Worksheets("Job Number List").Activate

If Completed = False Then
MsgBox "You have left the Job Number field blank.. Please try again."
txtSearchJobNumber.SetFocus
Else
'find the last row
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

'set FoundCell by searching only in the Job field column
Set FoundCell = Worksheets("Job Number List").Range("B1" & ":" & "B" &
LastRow).Find(frmSearch.txtSearchJobNumber)
If FoundCell Is Nothing Then
sMessage = "Sorry... Job Number: " + txtSearchJobNumber.Text + "
was Not Found. Try again?"
If MsgBox(sMessage, vbQuestion + vbYesNo, _
"Search") = vbYes Then
frmSearch.txtSearchJobNumber = vbNullString
frmSearch.txtSearchJobNumber.SetFocus
Else
frmSearch.Hide
Unload frmSearch
Set frmSearch = Nothing
End If
Else
frmSearch.Hide
frmJobSearchDisplay.txtSearchJobNumber = frmSearch.txtSearchJobNumber
Load frmJobSearchDisplay

' Increment row number:
frmJobSearchDisplay.lCurrentRow = FoundCell.Row
frmJobSearchDisplay.Show
Unload frmSearch

End If
End If

 

[Charles]

Hi,

Try unloading before you load next form.

Your code:

frmJobSearchDisplay.Hide
Load frmSearch
frmSearch.Show
Unload frmJobSearchDisplay

Try

Unload frmJobSearchDisplay
Load frmSearch
frmSearch.Show

HTH

Charles

 

[Cheryl]

Thank you for the response...

However, i have tried that variation as well and it didn't work. What I have
noticed is the following... (this is in a post from today - thought perhaps
my wording from this post wasn't clear enough so tried re-wording as follows):

It appears I can call and pass variables from one user form to another but
when I want to go close back to the originating user form to perform
additional tasks it doesn't seem to recognize the original form when i try to
load or show it. When I get the drop down for the form frmSearch it doesn't
show the option to Hide or Show as a form normally would... Yet, this form
loads okay when initially called from a command button on the worksheet.

Any ideas of what I may be doing wrong?

Thanks anyway,

Cheryl

 

[Charles]

Hi,

I took out the load statements and hide on your code.
See below

Private Sub cmdSearchAgain_Click()

' Save form contents before closing:
SaveRow

'Convert the date columns so they will reflect properly
Convert_Text_Numbers

'reset the print area
Set_Print_Area
Unload frmJobSearchDisplay
frmSearch.Show

End Sub

'I am perplexed.. It has to be right in front of me.. the error.. but
can't
'see it... When I go to this form from frmSearch the code is a
follows:

Dim Completed As Boolean

Dim FoundCell As Object
Dim CompleteSheet
Dim rngWholebook As Range
Dim LastRow As Integer 'This is the LAST Non Empty Row
Dim sMessage As String
Dim TrimJobField As String
Completed = True

If Len(txtSearchJobNumber) < 1 Then
Completed = False
End If

Worksheets("Job Number List").Activate
If Completed = False Then
MsgBox "You have left the Job Number field blank.. Please tr
again."
txtSearchJobNumber.SetFocus
Else
'find the last row
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
'set FoundCell by searching only in the Job field column
Set FoundCell = Worksheets("Job Number List").Range("B1" & ":"
"B" & LastRow).Find(frmSearch.txtSearchJobNumber)
If FoundCell Is Nothing Then
sMessage = "Sorry... Job Number: " & txtSearchJobNumber.Text
" was Not Found. Try again?"
If MsgBox(sMessage, vbQuestion + vbYesNo, "Search") = vbYe
Then
frmSearch.txtSearchJobNumber = vbNullString
frmSearch.txtSearchJobNumber.SetFocus
Else
Set frmSearch = Nothing
Unload frmSearch
End If
Else
Unload frmSearch
frmJobSearchDisplay.txtSearchJobNumber
frmSearch.txtSearchJobNumber
' Increment row number:
frmJobSearchDisplay.lCurrentRow = FoundCell.Row
frmJobSearchDisplay.Show
End If
End If

Not really sure if this will work but give it a try.

Charle