Access2000: Requerying combos RecourdSource in continous subform

[Arvi Laanemets]

Hi

I have a continous form, where amongst another controls are 2 combos. The
RecordSource for second combo depends on selection in first one. Settings
below work, until the form is inserted as subform.

Form fmForm:
Name="fmForm"
RecordSource=tblTable1

Private Sub Form_Current()
If Nz(Me!cbbCombo2) = 0 Then
Me!cbbCombo1.Enabled = True
Me!cbbCombo1.SetFocus
Else
Me!cbbCombo1.Enabled = False
End If
End Sub

Private Sub cbbCombo2_GotFocus()
If Nz(Me!cbbCombo1) = 0 Then
MsgBox ("Vali sõidugrupp!")
Me!cbbCombo1.Enabled = True
Me!cbbCombo1.SetFocus
Exit Sub
End If
Me!cbbCombo2.Requery
End Sub

cbbCombo1:
Name=cbbCombo1
ColumnCount=2
ControlSource=Reg1ID
RowSourceType=Table/Query
RowSource=SELECT [tblRegistry1].[Reg1ID], [tblRegistry1].[Reg1Name] FROM
tblRegistry1;
BoundColumn=1
Enabled=No
cbbCombo2:
Name=cbbCombo2
ColumnCount=2
ControlSource=Reg2ID
RowSourceType=Table/Query
RowSource=SELECT DISTINCT [tblRegistry2].[Reg2ID],
[tblRegistry2].[Reg2Name] FROM tblRegistry2 WHERE
((([tblRegistry1].[Reg1ID])=[Forms]![fmForm]![cbbCombo1]));
BoundColumn=1
Enabled=Yes

I used a form like this as subform before - only adjustments needed, were:

Form fmMainForm:
Name="fmMainForm"

Form fmForm:
...
Private Sub cbbCombo2_GotFocus()
...
Forms!fmMainForm!fmForm!cbbCombo2.Requery
End Sub

cbbCombo2:
...
RowSource=SELECT DISTINCT [tblRegistry2].[Reg2ID],
[tblRegistry2].[Reg2Name] FROM tblRegistry2 WHERE
((([tblRegistry1].[Reg1ID])=[Forms]![fmMainForm]![fmForm]![cbbCombo1]));

Now I tried to use same design for another project, but it doesn't work
there. Both main forms are multitab forms. The only difference - in new
project the main form doesn't have data source - on every tab is inserted an
independent subform with his own datasource (like a switchboard, but with
tabs instead of buttons calling forms). And when debbugging the code, I
can't refer to main form somehow at all.

Ons same subform (fmForm) I have another control - a calculated TextBox.
txtText:
ControlSource =DLookUp("[SomeField]";"[tblRegistry3]";"[Reg3ID]=" &
[Forms]![fmForm]![txtReg3ID])
When the form is used as subform on fmMainForm, this field returns an error
too - althoughe the ControlSource is edited:
ControlSource =DLookUp("[SomeField]";"[tblRegistry3]";"[Reg3ID]=" &
[Forms]![fmMainForm]![fmForm]![txtReg3ID])


I hope someone gives some help!
Thanks in advance

Arvi Laanemets

 

[Tom Wickerath]

Hi Arvi,

While you don't really describe what is *not* working, I'm going to make a guess. You might want
to see the following tip posted by FMS:

Tip #16: Data in a combo box control on a continuous form/datasheet disappears
http://www.fmsinc.com/free/tips.html#tip16foraccess


Tom
_____________________________________


Hi

I have a continous form, where amongst another controls are 2 combos. The
RecordSource for second combo depends on selection in first one. Settings
below work, until the form is inserted as subform.

Form fmForm:
Name="fmForm"
RecordSource=tblTable1

Private Sub Form_Current()
If Nz(Me!cbbCombo2) = 0 Then
Me!cbbCombo1.Enabled = True
Me!cbbCombo1.SetFocus
Else
Me!cbbCombo1.Enabled = False
End If
End Sub

Private Sub cbbCombo2_GotFocus()
If Nz(Me!cbbCombo1) = 0 Then
MsgBox ("Vali sõidugrupp!")
Me!cbbCombo1.Enabled = True
Me!cbbCombo1.SetFocus
Exit Sub
End If
Me!cbbCombo2.Requery
End Sub

cbbCombo1:
Name=cbbCombo1
ColumnCount=2
ControlSource=Reg1ID
RowSourceType=Table/Query
RowSource=SELECT [tblRegistry1].[Reg1ID], [tblRegistry1].[Reg1Name] FROM
tblRegistry1;
BoundColumn=1
Enabled=No
cbbCombo2:
Name=cbbCombo2
ColumnCount=2
ControlSource=Reg2ID
RowSourceType=Table/Query
RowSource=SELECT DISTINCT [tblRegistry2].[Reg2ID],
[tblRegistry2].[Reg2Name] FROM tblRegistry2 WHERE
((([tblRegistry1].[Reg1ID])=[Forms]![fmForm]![cbbCombo1]));
BoundColumn=1
Enabled=Yes

I used a form like this as subform before - only adjustments needed, were:

Form fmMainForm:
Name="fmMainForm"

Form fmForm:
...
Private Sub cbbCombo2_GotFocus()
...
Forms!fmMainForm!fmForm!cbbCombo2.Requery
End Sub

cbbCombo2:
...
RowSource=SELECT DISTINCT [tblRegistry2].[Reg2ID],
[tblRegistry2].[Reg2Name] FROM tblRegistry2 WHERE
((([tblRegistry1].[Reg1ID])=[Forms]![fmMainForm]![fmForm]![cbbCombo1]));

Now I tried to use same design for another project, but it doesn't work
there. Both main forms are multitab forms. The only difference - in new
project the main form doesn't have data source - on every tab is inserted an
independent subform with his own datasource (like a switchboard, but with
tabs instead of buttons calling forms). And when debbugging the code, I
can't refer to main form somehow at all.

Ons same subform (fmForm) I have another control - a calculated TextBox.
txtText:
ControlSource =DLookUp("[SomeField]";"[tblRegistry3]";"[Reg3ID]=" &
[Forms]![fmForm]![txtReg3ID])
When the form is used as subform on fmMainForm, this field returns an error
too - althoughe the ControlSource is edited:
ControlSource =DLookUp("[SomeField]";"[tblRegistry3]";"[Reg3ID]=" &
[Forms]![fmMainForm]![fmForm]![txtReg3ID])


I hope someone gives some help!
Thanks in advance

Arvi Laanemets

 

[Arvi Laanemets]

Hi

So long as I open fmForm alone, it works all nicely.
ComboBox cbbCombo1 is disabled for all rows, until I select cbbCombo2 in
some row with no value in it - then cbbCombo1 is enabled and activated, so I
can change the selection when I want. When I try to select something in
cbbCombo2, with nothing selected in cbbCombo1 in same row, I'm notified to
select some value to cbbCombo1 at first, and redirected back to cbbCombo1.
When I selected something for cbbCombo1, or when there was a value before, I
can make a selection for cbbCombo2 - selections displayed depend on value in
cbbCombo1 in same row (the row source is refreshed whenever I activate
cbbCombo2). After I select something for cbbCombo2, or activate some other
row with non-empty cbbComo2, cbbCombo1 is disabled.
Additionally, when I select some value into 3rd combo (cbbCombo3), in
unbound text box next to it a corresponding value is displayed (Dlookup
function)

When I try to open a form fmMainForm, with form fmForm inserted as subform
onto tab, I'm asked to enter the parameter value for Forms!fmForm!cbbCombo1.
It was all OK in previous project - I replaced all references to fmForm!
with fmMainForm!Form!, and it worked again (but not when I tried to open
fmForm alone anymore - a minor drawback)

When I try this with new project, I'm asked for parameter value
Forms!fmMainForm!fmForm!cbbCombo1 - and cbbCombo2 remains empty for all
rows, when I press Cancel. And Dlookup in text box returns #Name? I have
spent hours trying different syntaxes, and experimenting with Watch window,
but all in vain.


Arvi Laanemets

 

[Tom Wickerath]

Arvi,

I'm hoping someone else has the time to jump in here, since I need to leave for work now.

Tom
_______________________________


Hi

So long as I open fmForm alone, it works all nicely.
ComboBox cbbCombo1 is disabled for all rows, until I select cbbCombo2 in
some row with no value in it - then cbbCombo1 is enabled and activated, so I
can change the selection when I want. When I try to select something in
cbbCombo2, with nothing selected in cbbCombo1 in same row, I'm notified to
select some value to cbbCombo1 at first, and redirected back to cbbCombo1.
When I selected something for cbbCombo1, or when there was a value before, I
can make a selection for cbbCombo2 - selections displayed depend on value in
cbbCombo1 in same row (the row source is refreshed whenever I activate
cbbCombo2). After I select something for cbbCombo2, or activate some other
row with non-empty cbbComo2, cbbCombo1 is disabled.
Additionally, when I select some value into 3rd combo (cbbCombo3), in
unbound text box next to it a corresponding value is displayed (Dlookup
function)

When I try to open a form fmMainForm, with form fmForm inserted as subform
onto tab, I'm asked to enter the parameter value for Forms!fmForm!cbbCombo1.
It was all OK in previous project - I replaced all references to fmForm!
with fmMainForm!Form!, and it worked again (but not when I tried to open
fmForm alone anymore - a minor drawback)

When I try this with new project, I'm asked for parameter value
Forms!fmMainForm!fmForm!cbbCombo1 - and cbbCombo2 remains empty for all
rows, when I press Cancel. And Dlookup in text box returns #Name? I have
spent hours trying different syntaxes, and experimenting with Watch window,
but all in vain.


Arvi Laanemets