Form works fine on its own but error 2450 when opened in navigation form.

L

Laura_C

New Member
Joined
Oct 17, 2011
Messages
23
Hello, I have a search form that works fine when I open it alone, but I have added it to my main navigation form and now when I try and run my search criteria I get error 2450 Access cannot find the referenced form. I have another search form that works fine in the navigation form. I know the code for navigation forms should be a little different, I just can't seem to find the error in the code.

Thank you.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
What is the code you use to open the Search form?
What is the code you to run the Search criteria?
Is the subform open before you assign values to it?
 
Last edited:
Upvote 0
This is the code for the form on it's own when it works. The subform is open when you select criteria.


Private Sub cmdRestoreAll1_Click()
On Error GoTo Err_cmdRestoreAll1_Click


'The All button'
Dim strSQL1 As String
strSQL1 = ("SELECT * FROM QryTransactionsReport")
Me.frmSwitchboardList1.Form.RecordSource = strSQL1
Me.xSelectSQL = strSQL1


Exit_cmdRestoreAll1_Click:
Exit Sub


Err_cmdRestoreAll1_Click:
MsgBox Err.Number & "" & Err.Description
Resume Exit_cmdRestoreAll1_Click
End Sub


Private Sub cmdRptFlex_Click()
On Error GoTo Err_cmdRptFlex_Click


Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "frmSwitchboard_RptFlex"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_cmdRptFlex_Click:
Exit Sub


Err_cmdRptFlex_Click:
MsgBox "cmdRptFlex " & Err.Number & " " & Err.Description
Resume Exit_cmdRptFlex_Click


End Sub


Private Sub cmdSelect1_Click()
On Error GoTo Err_cmdSelect1_Click
Dim strSQL1 As String


'use function to define SQL string
'move querybuilding to function so we can use it in building a report on a similar but different query
strSQL1 = SelectSQL("SELECT * FROM QryTransactionsReport")

'for trouble shooting only
'MsgBox strSQL1

Me.frmSwitchboardList1.Form.RecordSource = strSQL1
Me.xSelectSQL = strSQL1


Exit_cmdSelect1_Click:
Exit Sub


Err_cmdSelect1_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdSelect1_Click


End Sub


Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open


'open subform with no records to start
Dim strSQL1 As String
strSQL1 = "SELECT * FROM QryTransactionsReport" & " WHERE [ID] = 0"
Me.frmSwitchboardList1.Form.RecordSource = strSQL1
Me.xSelectSQL = strSQL1




Exit_Form_Open:
Exit Sub


Err_Form_Open:
MsgBox Err.Number & "" & Err.Description
Resume Exit_Form_Open


End Sub


Function SelectSQL(strQuery As String) As String
On Error GoTo Err_SelectSQL


'refer to Forms(frm). ...
Dim frm As String
frm = "frmSwitchboard"
'other option
'define SelectSQL(strQuery As String, frm as form) As String
'format of parameter frm is then: Form_frmSwitchbord
'refer to form as frm. ...


Dim strSQL1 As String, strSQL1start As String
strSQL1 = strQuery
strSQL1start = strSQL1


'batch'
If Forms(frm).chkSelectCategory1 = True Then
If Not IsNull(Forms(frm).cboChooseCategory1) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[Batch] = " & Forms(frm).cboChooseCategory1
Else
MsgBox "deselect option batch or make a selection in the dropdown"
End If
End If

'type'
If Forms(frm).chkSelectCategory2 = True Then
If Not IsNull(Forms(frm).cboChooseCategory2) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[type] = " & Forms(frm).cboChooseCategory2
Else
MsgBox "deselect option type or make a selection in the dropdown"
End If
End If

'TC'
If Forms(frm).chkSelectCategory3 = True Then
If Not IsNull(Forms(frm).cboChooseCategory3) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[TC] = """ & Forms(frm).cboChooseCategory3 & """"
Else
MsgBox "deselect option TC or make a selection in the dropdown"
End If
End If

'Area'
If Forms(frm).chkSelectCategory4 = True Then
If Not IsNull(Forms(frm).cboChooseCategory4) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[area] = """ & Forms(frm).cboChooseCategory4 & """"
Else
MsgBox "deselect option Area or make a selection in the dropdown"
End If
End If

'Category'
If Forms(frm).chkSelectCategory5 = True Then
If Not IsNull(Forms(frm).cboChooseCategory5) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[Category] = """ & Forms(frm).cboChooseCategory5 & """"
Else
MsgBox "deselect option Category or make a selection in the dropdown"
End If
End If

'Benchmark'
If Forms(frm).chkSelectCategory6 = True Then
If Not IsNull(Forms(frm).cboChooseCategory6) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
If Forms(frm).cboChooseCategory6 = "yes" Then
strSQL1 = strSQL1 & "[memBenchmark] = true"
Else
strSQL1 = strSQL1 & "[memBenchmark] = false"
End If
Else
MsgBox "Select VIP yes or no or deselect"
End If
End If

'NUM'
If Forms(frm).chkSelectText1Like = True Then
If Not IsNull(Forms(frm).txtChooseText1Like) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "([NUM] like ""*" & Forms(frm).txtChooseText1Like & "*"" OR [NUM] like ""*" & Forms(frm).txtChooseText1Like & "*"")"
Else
MsgBox "deselect option customer NUM or type your search string"
End If
End If

'date'
If Forms(frm).chkSelectDate1 = True Then
If Not (IsNull(Forms(frm).txtDate1Start) Or IsNull(Forms(frm).txtDate1End)) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[DateAmt] >= #" & Forms(frm).txtDate1Start & "# AND [DateAmt] <= #" & Forms(frm).txtDate1End & "#"
Else
MsgBox "deselect option date transaction or make an entry for both dates"
End If
End If

Dim strDayStart1 As String
Dim strDayEnd1 As String


'amount'
If Forms(frm).chkSelectAmount1 = True Then
If Not (IsNull(Forms(frm).txtAmount1Min) Or IsNull(Forms(frm).txtAmount1Max)) Then
If Not (strSQL1start = strSQL1) Then
strSQL1 = strSQL1 & " AND "
Else
strSQL1 = strSQL1 & " WHERE "
End If
strSQL1 = strSQL1 & "[AMT] >= " & Forms(frm).txtAmount1Min & " AND [AMT] <= " & Forms(frm).txtAmount1Max
Else
MsgBox "deselect option amount or make an entry for both amounts"
End If
End If



'if there is no string at all return nothing
If strSQL1start = strSQL1 Then
strSQL1 = strSQL1 & " WHERE [ID] = 0"
End If



'for trouble shooting only
'MsgBox strSQL1

'set function to value
SelectSQL = strSQL1

Exit_SelectSQL:
Exit Function


Err_SelectSQL:
MsgBox Err.Number & " " & Err.Description
Resume Exit_SelectSQL


End Function


Private Sub rptMailingLabels_Click()
On Error GoTo Err_rptMailingLabels_Click


Dim stDocName As String


If Not (Me.txtCntOfID = 0) Then
intLabelOption = 1
stDocName = "RptContactsLabels"
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "select at least one swtid first"
End If


Exit_rptMailingLabels_Click:
Exit Sub


Err_rptMailingLabels_Click:
MsgBox "rptMailingLabels " & Err.Number & " " & Err.Description
Resume Exit_rptMailingLabels_Click


End Sub



'for testing
'MsgBox strSQL1

'filter and show data
Me.frmSwitchboardList1.Form.RecordSource = strSQL1
Me.xSelectSQL = strSQL1
'move cursor to previous position
If intKeyCode = 32 Then
Me.txtChooseNameLikeQuick1V2 = Me.txtChooseNameLikeQuick1V2 & " "
End If
Me.txtChooseNameLikeQuick1V2.SelStart = Len(Me.txtChooseNameLikeQuick1V2)

Exit_txtChooseNameLikeQuick1V2_KeyUp:
Exit Sub


Err_txtChooseNameLikeQuick1V2_KeyUp:
Select Case Err.Number
Case 94
'occurs when there is nothing in textbox after key up, just ignore
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
Resume Exit_txtChooseNameLikeQuick1V2_KeyUp


End Sub






Private Sub cmdCloseForm_Click()
On Error GoTo Err_cmdCloseForm_Click


DoCmd.Close


Exit_cmdCloseForm_Click:
Exit Sub


Err_cmdCloseForm_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdCloseForm_Click

End Sub


Function ExtractSQL(strQuery As String) As String

ExtractSQL = Mid(Me.xSelectSQL, Len(strQuery) + 1, Len(Me.xSelectSQL) - Len(strQuery))

End Function
'Date Rec 1
Private Sub chkSelectDateRange1_AfterUpdate()

If Me.txtMonthStart1 = 0 Or Me.txtMonthEnd1 = 0 Then
MsgBox "select a start and end month first"
Me.chkSelectDateRange1 = False
End If

End Sub
'Date Rec 1
Private Sub txtMonthStart1_AfterUpdate()


Me.txtDayStart1.Enabled = True
Select Case Me.txtMonthStart1
Case 1, 3, 5, 7, 8, 10, 12
Me.txtDayStart1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29';'30';'31'"
Case 2
Me.txtDayStart1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29'"
If Me.txtDayStart1 = "30" Or Me.txtDayStart1 = "31" Then Me.txtDayStart1 = "29"
Case 4, 6, 9, 11
Me.txtDayStart1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29';'30'"
If Me.txtDayStart1 = "31" Then Me.txtDayStart1 = "30"
Case Else 'error
End Select


End Sub
'Date Rec 1
Private Sub txtMonthEnd1_AfterUpdate()


Me.txtDayEnd1.Enabled = True
Select Case Me.txtMonthEnd1
Case 1, 3, 5, 7, 8, 10, 12
Me.txtDayEnd1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29';'30';'31'"
Case 2
Me.txtDayEnd1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29'"
If Me.txtDayEnd1 = "30" Or Me.txtDayEnd1 = "31" Then Me.txtDayEnd1 = "29"
Case 4, 6, 9, 11
Me.txtDayStart1.RowSource = "'01';'02';'03';'04';'05';'06';'07';'08';'09';'10';'11';'12';'13';'14';'15';'16';'17';'18';'19';'20';'21';'22';'23';'24';'25';'26';'27';'28';'29';'30'"
If Me.txtDayEnd1 = "31" Then Me.txtDayEnd1 = "30"
Case Else 'error
End Select

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Struggling with Editting a "search" query
2 3
Replies
22
Views
1K
welshgasman
welshgasman
K
  • Question
Modern Chart update settings
Replies
0
Views
292
Kemidan2014
K
D
  • Solved
Find Record in Access using .FindFirst with Multiple Criteria
2
Replies
14
Views
831
Micron
M
P
  • Question
Open a form with data from another form and then update a table
Replies
2
Views
813
Pettor
P
S
  • Question
Simba Spark ODBC Connection Error
Replies
0
Views
407
santa12345
S

Forum statistics

Threads
1,214,954
Messages
6,122,461
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top