MS Access database support - a2k - bizarre subform bug

Author

a2k - bizarre subform bug

Deano

2006-02-19, 9:23 am

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!Cate
gory

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

2006-02-19, 1:24 pm

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!Cate
gory
>
>
> 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!Cate
gory") > 0 Then
MsgBox qdf.name
End If
Next qdf
MsgBox "Done"
End Sub

Deano

2006-02-19, 8:38 pm

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!Cate
gory") > 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

2006-02-20, 3:24 am

Deano wrote:
> salad wrote:
>
>
>
> 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

2006-02-20, 7:24 am

salad wrote:
<snipped>
>
> Hope you find the above useful.

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

Sponsored Links