a2k - bizarre subform bug

[Deano]

I've copied a form that has a number of subforms each contained in a page
within a tab control.

The name of the forms are different but i'm reusing the code. I have to
make sure that I adjust all references to make this work.

But right now I'm getting a parameter prompt each time I change subforms;

"Enter Parameter Value"
Forms!frm_Input!Category
frm_Input is the original form but this copy of the form is called
frm_InputOther. There's nothing in code that could be causing this and all
the new forms have the correct names. The parent form is based on a table
(no query) but I filter the records on opening the form.

This is driving me around the bend - any ideas on what it could possibly be?

[salad]

Deano wrote:

I've copied a form that has a number of subforms each contained in a page
within a tab control.

The name of the forms are different but i'm reusing the code. I have to
make sure that I adjust all references to make this work.

But right now I'm getting a parameter prompt each time I change subforms;

"Enter Parameter Value"
Forms!frm_Input!Category
frm_Input is the original form but this copy of the form is called
frm_InputOther. There's nothing in code that could be causing this and all
the new forms have the correct names. The parent form is based on a table
(no query) but I filter the records on opening the form.

This is driving me around the bend - any ideas on what it could possibly be?

Put the following code in a code module. See if you find it. If not,
I'd run some code to check the recordsource of the form and row source
of any combos.

Public Sub QueryHasString()
Dim qdf As QueryDef
Dim strSQL As String
For Each qdf In CurrentDb.QueryDefs
strSQL = qdf.SQL
If InStr(strSQL, "Forms!frm_Input!Category") > 0 Then
MsgBox qdf.name
End If
Next qdf
MsgBox "Done"
End Sub

[Deano]

salad wrote:

Put the following code in a code module. See if you find it. If not,
I'd run some code to check the recordsource of the form and row source
of any combos.

Public Sub QueryHasString()
Dim qdf As QueryDef
Dim strSQL As String
For Each qdf In CurrentDb.QueryDefs
strSQL = qdf.SQL
If InStr(strSQL, "Forms!frm_Input!Category") > 0 Then
MsgBox qdf.name
End If
Next qdf
MsgBox "Done"
End Sub

Salad, you're the man. Bizarre bug no more, I fixed a dodgy combo that I
missed. Phew, I really needed the help. I'll squirrel that code away
somewhere as well.

thanks

[salad]

Deano wrote:

salad wrote:

Put the following code in a code module. See if you find it. If not,
I'd run some code to check the recordsource of the form and row source
of any combos.

Public Sub QueryHasString()
Dim qdf As QueryDef
Dim strSQL As String
For Each qdf In CurrentDb.QueryDefs
strSQL = qdf.SQL
If InStr(strSQL, "Forms!frm_Input!Category") > 0 Then
MsgBox qdf.name
End If
Next qdf
MsgBox "Done"
End Sub

Salad, you're the man. Bizarre bug no more, I fixed a dodgy combo that I
missed. Phew, I really needed the help. I'll squirrel that code away
somewhere as well.

thanks

You're welcome.

There's some sample code in on-line help that I modified slightly but
kept the same name of EmpSQL over the years due to laziness and to
remember where I got it from. The code is handy for checking record and
rowsources in forms. I too have gotten those enter parameter messages
and I go "Oh no, where the heck is it." This makes it easier to spot.

I have another routine call for reports. You can change this to look in
reports easily enough

Public Sub FormsRowSource()
Dim dbs As Database
Dim doc As Document
Dim frm As Form

Dim i As Integer
Dim j As Integer

Set dbs = CurrentDb
With dbs.Containers!Forms
For Each doc In .Documents
'uncomment the following IF line and the Endif if searching
'for a specific form. Keep commented if searching all forms
'If Left(doc.Name, 10) = "FormNameToSearch" Then

DoCmd.OpenForm doc.name, acDesign, , , , acHidden

Set frm = Forms(doc.name)

If InStr(frm.RecordSource, "WordToSearch") > 0 Then
i = i + 1
MsgBox frm.name
Else
j = j + 1
'check the controls
EmpSQL frm
End If
Set frm = Nothing
DoCmd.Close acForm, doc.name
'Endif
Next doc
End With

MsgBox "Done " & i & " " & j
End Sub
Public Sub EmpSQL(frm As Form)
Dim ctl As Control
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
For Each ctl In frm.Controls
'check each control in the form and if combo/list box
'check the rowsource for a string. Easy to update to
'check for tag, name, and other properties. You can
'change for different types of controls; labels, command
'buttons, etc.
If ctl.ControlType = acComboBox Or _
ctl.ControlType = acListBox Then
strSQL = ctl.RowSource
If InStr(strSQL, "WordToSearch") > 0 Then
MsgBox "Form " & frm.name & " Control " & ctl.name
End If
End If
Next ctl
End Sub

Hope you find the above useful.

[Deano]

salad wrote:
<snipped>

Hope you find the above useful.

Thanks Salad, if I get the time soon I'll give that a test.