Combo offset

[Jeanette Cunningham]

strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query field
you want to look for matches in, and what is already in the form's
recordsource or is passed as Open Args.

Jeanette Cunningham

And "strCriteria"?

Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but looks
for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in the
combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up and
that could be happening here since the box is appearing below where it
should be.

Good luck.

Ron

 

[Bill]

I have to leave for an hour, I'll give the DCount code a
shot when I return.
Bill

strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query field
you want to look for matches in, and what is already in the form's
recordsource or is passed as Open Args.

Jeanette Cunningham

And "strCriteria"?

Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but looks
for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in the
combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up and
that could be happening here since the box is appearing below where it
should be.

Good luck.

Ron

 

[Marshall Barton]

Bill wrote:
[]

So, now we've returned to the Dropdown shifting
the display of the combo over and down from the
base of the combo itself, i.e., just looks like a list
box off to the side of the combo.

If UBound(a) > 0 Then
If a(1) = "New" Then
Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown
End If
Else
LastName.SetFocus
End If

Really clueless about what could be happening.

I have never seen that effect, but I agree with Jeanette
that it sounds like some menu/tool bar getting the way.

If it is the menu bars coming or going causing it, I would
expect that situation to have stablilized by the time the
Current event occurs.

Just for kicks, double check the ColumnWidths property for a
strange entry.

 

[Bill]

I'd already checked the column widths: 0";1", where the
hidden column is the primary key to which the combo
is bound.

I need to try Jeanette's code segment to see if that
flushes out the problem.

Bill

Bill wrote:
[]

So, now we've returned to the Dropdown shifting
the display of the combo over and down from the
base of the combo itself, i.e., just looks like a list
box off to the side of the combo.

If UBound(a) > 0 Then
If a(1) = "New" Then
Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown
End If
Else
LastName.SetFocus
End If

Really clueless about what could be happening.

I have never seen that effect, but I agree with Jeanette
that it sounds like some menu/tool bar getting the way.

If it is the menu bars coming or going causing it, I would
expect that situation to have stablilized by the time the
Current event occurs.

Just for kicks, double check the ColumnWidths property for a
strange entry.

 

[Bill]

Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill

strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query field
you want to look for matches in, and what is already in the form's
recordsource or is passed as Open Args.

Jeanette Cunningham

And "strCriteria"?

Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but looks
for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in the
combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up and
that could be happening here since the box is appearing below where it
should be.

Good luck.

Ron

 

[Jeanette Cunningham]

Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox and the
combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham

Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill

strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query field
you want to look for matches in, and what is already in the form's
recordsource or is passed as Open Args.

Jeanette Cunningham

And "strCriteria"?


Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but looks
for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in the
combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up
and
that could be happening here since the box is appearing below where
it
should be.

Good luck.

Ron

 

[Bill]

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill

Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox and
the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham

Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill

strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the form's
recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but
looks for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in
the combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up
and
that could be happening here since the box is appearing below where
it
should be.

Good luck.

Ron

 

[Jeanette Cunningham]

Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt form
to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill

Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox and
the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham

Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



strCriteria is the normal sort of thing to use with DCount. vba help on
DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


Bill,
coming at this from a completely different angle, assuming a solution
that does not try to fix what you currently have going wrong, but
looks for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in
the combo is in the currently set (by code) row source for the combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when
you
save the form out of design mode. I have seen button menus open at
design time but NOT requested a run time cause the form to jump up
and
that could be happening here since the box is appearing below where
it
should be.

Good luck.

Ron

 

[Bill]

New form; Copy'n'Paste offending control from
failing form to new form; rename the forms so that
the new form opens in the same environment.
Failure continues with old control in new form.

However, if I just open the new form from database
window, the dropdown works normally Likewise,
if I open the previously failing form, that has only the
combo control in it, the combo's dropdown works
normally.

The code that opens the form is really vanilla:

Private Sub Form_DblClick(Cancel As Integer)
Dim frm As String
'===========================================
'User has double-clicked the record selector adjacent to LastName.
'===========================================

frm = "frmIndividual"

DoCmd.OpenForm frm, , , , acFormEdit, , Me.RecordID
End Sub

I remember as a kid listening to "I Love A Mystery" on the radio.
That notion seems to have lost all of its alluring charm.

Bill

Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt
form to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill

Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox and
the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham


Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



strCriteria is the normal sort of thing to use with DCount. vba help
on DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


message Bill,
coming at this from a completely different angle, assuming a
solution that does not try to fix what you currently have going
wrong, but looks for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in
the combo is in the currently set (by code) row source for the
combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when
you
save the form out of design mode. I have seen button menus open
at
design time but NOT requested a run time cause the form to jump up
and
that could be happening here since the box is appearing below
where it
should be.

Good luck.

Ron

 

[Bill]

Even this produces a failure.

Private Sub Form_DblClick(Cancel As Integer)
Dim frm As String
'=============================================
'User has double-clicked the record selector adjacent to LastName.
'=============================================

frm = "frmIndividual"

'DoCmd.OpenForm frm, , , , acFormEdit, , Me.RecordID
DoCmd.OpenForm frm
End Sub

Bill

Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt
form to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill

Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox and
the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham


Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



strCriteria is the normal sort of thing to use with DCount. vba help
on DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


message Bill,
coming at this from a completely different angle, assuming a
solution that does not try to fix what you currently have going
wrong, but looks for something that does work.
I routinely use code like this in form load events to set up a combo

I am using a saved query to check if the value I want to display in
the combo is in the currently set (by code) row source for the
combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when
you
save the form out of design mode. I have seen button menus open
at
design time but NOT requested a run time cause the form to jump up
and
that could be happening here since the box is appearing below
where it
should be.

Good luck.

Ron

 

[Jeanette Cunningham]

Bill,
I agree, the fun disappears after couple of attempts.
We know the form isn't corrputed.
We know it can open with the combo working normally.
My guess is that both forms are bound to the same table (or query based on
the same table or tables).
So try the code to open the form like this:

'DoCmd.OpenForm frm, , , , acFormEdit, acDialog, Me.RecordID

Jeanette Cunningham

Even this produces a failure.

Private Sub Form_DblClick(Cancel As Integer)
Dim frm As String
'=============================================
'User has double-clicked the record selector adjacent to LastName.
'=============================================

frm = "frmIndividual"

'DoCmd.OpenForm frm, , , , acFormEdit, , Me.RecordID
DoCmd.OpenForm frm
End Sub

Bill

Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt
form to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill



Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox
and the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham


Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



strCriteria is the normal sort of thing to use with DCount. vba help
on DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


message Bill,
coming at this from a completely different angle, assuming a
solution that does not try to fix what you currently have going
wrong, but looks for something that does work.
I routinely use code like this in form load events to set up a
combo

I am using a saved query to check if the value I want to display in
the combo is in the currently set (by code) row source for the
combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when
you
save the form out of design mode. I have seen button menus open
at
design time but NOT requested a run time cause the form to jump
up and
that could be happening here since the box is appearing below
where it
should be.

Good luck.

Ron

 

[Bill]

BINGO!!!!!!!!

And yes, both forms are bound to the same query.

I assume with the use of acDialog that the PopUp properties
necessarily needed to be set to "yes"?

A very subtle little nuance for me, I'll save this one for
future reference.

Thanks Jeanette for hanging in there with this one. Now
we can return to having fun!

Bill

Bill,
I agree, the fun disappears after couple of attempts.
We know the form isn't corrputed.
We know it can open with the combo working normally.
My guess is that both forms are bound to the same table (or query based on
the same table or tables).
So try the code to open the form like this:

'DoCmd.OpenForm frm, , , , acFormEdit, acDialog, Me.RecordID

Jeanette Cunningham

Even this produces a failure.

Private Sub Form_DblClick(Cancel As Integer)
Dim frm As String
'=============================================
'User has double-clicked the record selector adjacent to LastName.
'=============================================

frm = "frmIndividual"

'DoCmd.OpenForm frm, , , , acFormEdit, , Me.RecordID
DoCmd.OpenForm frm
End Sub

Bill

Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt
form to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill



Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant to
opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox
and the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham


Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



message strCriteria is the normal sort of thing to use with DCount. vba help
on DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


message Bill,
coming at this from a completely different angle, assuming a
solution that does not try to fix what you currently have going
wrong, but looks for something that does work.
I routinely use code like this in form load events to set up a
combo

I am using a saved query to check if the value I want to display
in the combo is in the currently set (by code) row source for the
combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something like
that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable when
you
save the form out of design mode. I have seen button menus open
at
design time but NOT requested a run time cause the form to jump
up and
that could be happening here since the box is appearing below
where it
should be.

Good luck.

Ron

 

[Jeanette Cunningham]

Bill,
using acDialog temporarily sets the popup property to true so you don't need
to do that on the form's property dialog.
There are enough problems with having 2 forms open in edit mode or add mode
at the same time if they are bound to the same query or table, that it is
never a good idea to do it.
Wow, that seemed like a marathon, glad you have it fixed.

Jeanette Cunningham

BINGO!!!!!!!!

And yes, both forms are bound to the same query.

I assume with the use of acDialog that the PopUp properties
necessarily needed to be set to "yes"?

A very subtle little nuance for me, I'll save this one for
future reference.

Thanks Jeanette for hanging in there with this one. Now
we can return to having fun!

Bill

Bill,
I agree, the fun disappears after couple of attempts.
We know the form isn't corrputed.
We know it can open with the combo working normally.
My guess is that both forms are bound to the same table (or query based
on the same table or tables).
So try the code to open the form like this:

'DoCmd.OpenForm frm, , , , acFormEdit, acDialog, Me.RecordID

Jeanette Cunningham

Even this produces a failure.

Private Sub Form_DblClick(Cancel As Integer)
Dim frm As String
'=============================================
'User has double-clicked the record selector adjacent to LastName.
'=============================================

frm = "frmIndividual"

'DoCmd.OpenForm frm, , , , acFormEdit, , Me.RecordID
DoCmd.OpenForm frm
End Sub

Bill







Bill,
very peculiar. Probably corruption of the form.
Try creating a new blank form and copying the controls from the corrupt
form to the new form.

Jeanette Cunningham

My form now has as its code sheet:

Private Sub Form_Load()

Me.cmboClasses.SetFocus
Me.cmboClasses.Dropdown

End Sub

That's it! and the one single combo control
"cmboClasses".

Looks more-or-less like this:

[base w/dwn-arrow]


[1Dropped portion]
[2Dropped portion]
[3Dropped portion]
[4Dropped portion]
[5Dropped portion]

Bill



Bill,
Working on a copy of your form:
What happens if you comment out all other code that is not relevant
to opening the form and dropping the combo?
What happens if you remove all the other controls except the textbox
and the combo?
What happens if you remove any menus and toolbars for this form?

Jeanette Cunningham


Jeanette,

Your code worked, I think, as intended. If the criteria was
satisfied, the sought after row was pre-selected in the combo,
otherwise the combo dropped down. The dropdown still
produces the disjointed list from the combo's base, so I
still DON'T have this problem solved.

This combo is really as "vanilla" as they come. The RowSource
query returns two fields, the first of which is the primary key
of the query's underlying table to which the combo is bound.
The key is hidden (ColumnWidth = 0";1") and the user selects
the item of interest from the displayed 2nd column.

I sure do appreciate the thought you've given to this, as well as
those of Marsh and Ron.

Bill



message strCriteria is the normal sort of thing to use with DCount. vba
help on DCount explains it very well.
In your case, strCriteria will be determined by the table or query
field you want to look for matches in, and what is already in the
form's recordsource or is passed as Open Args.

Jeanette Cunningham


And "strCriteria"?


message Bill,
coming at this from a completely different angle, assuming a
solution that does not try to fix what you currently have going
wrong, but looks for something that does work.
I routinely use code like this in form load events to set up a
combo

I am using a saved query to check if the value I want to display
in the combo is in the currently set (by code) row source for the
combo.

note: TrID is the bound column of the combo
lngTrID is a variable passed in with Open Args

If DCount("[TrID]", "qChkChosenTrack", strCriteria) > 0 Then
'yes chosen track is in the combo's list
Me.cboTrID = lngTrID
Else
'drop down the track combo
Me.cboTrID.SetFocus
Me.cboTrID.Dropdown
End If

Jeanette Cunningham

The box stays correctly positioned, but the expanded
portion is disjointed from the combo itself. As a stretch,
I wondered if the positioning coordinates got messed up
and might show up if I captured them in a Debug.Print.
Nothing seems disturbed:

(Form_Load) Before DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300
(Form_Load) After DropDown
Left: 11100
Right: 960
Width: 1800
Height: 300

Bill



Sorry it didn't work. I seem to halfway remember something
like that
happening a long time ago, but it has been a long week.

Does the form seem to jump?

Try making sure that no buttons or extra menus are viewable
when you
save the form out of design mode. I have seen button menus
open at
design time but NOT requested a run time cause the form to jump
up and
that could be happening here since the box is appearing below
where it
should be.

Good luck.

Ron