Combo Box Limit of 20 Fields

[Maurita]

Hi All, hope someone can help me with a combo box problem. I have a
combo box that is retrieving fields from a query. The query has 25
fields that need to be populated in a report. Since the limit of a
combo box is 20 fields, I am hoping there is a workaround for this
limitation.

Thank you.

Maurita Searcy

 

[Douglas J. Steele]

I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just because there
are 25 fields in the query that the report uses, why do all 25 fields need
to be in the combo box?

 

[Maurita]

I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just because there
are 25 fields in the query that the report uses, why do all 25 fields need
to be in the combo box?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -

Doug,

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. Only one field will be visible to the
user. Obviously, I am going about populating the form in the wrong
way. Any suggestions? Thank you.

Maurita

 

[Klatuu]

If I understand correctly, you are wanting to use a combo box to select a
record from a quey an use the columns to populate the controls on your form.

Is the query based on the recordset of the form or is it from a different
table?

If it is from a different table, I would be concerned about your design as
the same data should not be kept in two tables. But, if this is necessary,
you could create a recordset and load the values from the recordset fields to
the form controls:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
MsgBox "Record Not Found"
Else
Me.txtOne = ![AField]
Me.txtTwo = ![AnotherField]
Me.txtThree = ![AnyOldField]
Me.txtFour = ![CowField]
End With
.Close
End With
Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE Clause
The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as the
form's recordset, the usual way to move to a selected record is to use the
After Update event of the combo box to move to the record:

With Me.RecordsetClone
.FindFirst "[FieldName] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. If it is a text field, the syntax would be:

.FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
.FindFirst "[FieldName] = #" & Me.MyCombo & "#"

 

[Maurita]

If I understand correctly, you are wanting to use a combo box to select a
record from a quey an use the columns to populate the controls on your form.

Is the query based on the recordset of the form or is it from a different
table?

If it is from a different table, I would be concerned about your design as
the same data should not be kept in two tables.  But, if this is necessary,
you could create a recordset and load the values from the recordset fieldsto
the form controls:

Dim rst As DAO.Recordset

    Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
   With rst
       If .RecordCount = 0 Then
           MsgBox "Record Not Found"
      Else
          Me.txtOne = ![AField]
          Me.txtTwo = ![AnotherField]
          Me.txtThree = ![AnyOldField]
          Me.txtFour = ![CowField]
     End With
     .Close
   End With
   Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE Clause
           The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as the
form's recordset, the usual way to move to a selected record is to use the
After Update event of the combo box to move to the record:

    With Me.RecordsetClone
        .FindFirst "[FieldName] = " & Me.MyCombo
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

The FindFirst line is coded assuming the field your are comparing to is
numeric.  If it is a text field, the syntax would be:

        .FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
        .FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query.  Only one field will be visible to the
user.  Obviously, I am going about populating the form in the wrong
way.  Any suggestions?  Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Doug,

Thank you so very much for your detailed instructions. I understand
the principal, but have a couple of questions. First, I am basing my
combo box and form on a query. On the AfterUpdate event of my combo
box, I've typed the following, but am having problems:

With Me.RecordsetClone

 

[Maurita]

If I understand correctly, you are wanting to use a combo box to select a
record from a quey an use the columns to populate the controls on your form.

Is the query based on the recordset of the form or is it from a different
table?

If it is from a different table, I would be concerned about your design as
the same data should not be kept in two tables.  But, if this is necessary,
you could create a recordset and load the values from the recordset fieldsto
the form controls:

Dim rst As DAO.Recordset

    Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
   With rst
       If .RecordCount = 0 Then
           MsgBox "Record Not Found"
      Else
          Me.txtOne = ![AField]
          Me.txtTwo = ![AnotherField]
          Me.txtThree = ![AnyOldField]
          Me.txtFour = ![CowField]
     End With
     .Close
   End With
   Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE Clause
           The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as the
form's recordset, the usual way to move to a selected record is to use the
After Update event of the combo box to move to the record:

    With Me.RecordsetClone
        .FindFirst "[FieldName] = " & Me.MyCombo
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

The FindFirst line is coded assuming the field your are comparing to is
numeric.  If it is a text field, the syntax would be:

        .FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
        .FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query.  Only one field will be visible to the
user.  Obviously, I am going about populating the form in the wrong
way.  Any suggestions?  Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. I understand
the concept, but have a couple of questions because I can't get the
code to work. I'm not sure what FindFirst represents. Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita

 

[Douglas J. Steele]

Actually, it's Dave that's been giving you the advice, not me...

FindFirst is supposed to find the matching row in the underlying recordset.

The reference to the combo box needs to be outside of the quotes. Since you
say EngineSerialNo is a text field, try

FindFirst "[EngineSerialNo] = '" & Me.Combo124 & "'"

Exagerated for clarity, that's

FindFirst "[EngineSerialNo] = ' " & Me.Combo124 & " ' "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thank you Doug for all your help with the above problem. I understand
the concept, but have a couple of questions because I can't get the
code to work. I'm not sure what FindFirst represents. Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita

If I understand correctly, you are wanting to use a combo box to select a
record from a quey an use the columns to populate the controls on your
form.

Is the query based on the recordset of the form or is it from a different
table?

If it is from a different table, I would be concerned about your design as
the same data should not be kept in two tables. But, if this is necessary,
you could create a recordset and load the values from the recordset fields
to
the form controls:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
MsgBox "Record Not Found"
Else
Me.txtOne = ![AField]
Me.txtTwo = ![AnotherField]
Me.txtThree = ![AnyOldField]
Me.txtFour = ![CowField]
End With
.Close
End With
Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE
Clause
The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as the
form's recordset, the usual way to move to a selected record is to use the
After Update event of the combo box to move to the record:

With Me.RecordsetClone
.FindFirst "[FieldName] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. If it is a text field, the syntax would be:

.FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
.FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. Only one field will be visible to the
user. Obviously, I am going about populating the form in the wrong
way. Any suggestions? Thank you.

 

[Klatuu]

There are some syntax problems with your code. Here is the corrected version:

With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the second
FindFirst will override the first and you may not get the record you expect.

FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text field
FindFirst "[Program] = Me.Combo124 & """ 'text
field
'Should be
FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """
If Not .NoMatch Then
'You need a period before the Bookmark
Me.Bookmark = Bookmark
'Should be
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP

If I understand correctly, you are wanting to use a combo box to select a
record from a quey an use the columns to populate the controls on your form.

Is the query based on the recordset of the form or is it from a different
table?

If it is from a different table, I would be concerned about your design as
the same data should not be kept in two tables. But, if this is necessary,
you could create a recordset and load the values from the recordset fields to
the form controls:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
MsgBox "Record Not Found"
Else
Me.txtOne = ![AField]
Me.txtTwo = ![AnotherField]
Me.txtThree = ![AnyOldField]
Me.txtFour = ![CowField]
End With
.Close
End With
Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE Clause
The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as the
form's recordset, the usual way to move to a selected record is to use the
After Update event of the combo box to move to the record:

With Me.RecordsetClone
.FindFirst "[FieldName] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. If it is a text field, the syntax would be:

.FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
.FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP

On Jan 10, 11:46�am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just because there
are 25 fields in the query that the report uses, why do all 25 fields need
to be in the combo box?

Hi All, hope someone can help me with a combo box problem. �I have a
combo box that is retrieving fields from a query. �The query has 25
fields that need to be populated in a report. �Since the limit of a
combo box is 20 fields, I am hoping there is a workaround for this
limitation.

Thank you.

Maurita Searcy- Hide quoted text -

- Show quoted text -

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. Only one field will be visible to the
user. Obviously, I am going about populating the form in the wrong
way. Any suggestions? Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. I understand
the concept, but have a couple of questions because I can't get the
code to work. I'm not sure what FindFirst represents. Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita

 

[Douglas J. Steele]

You sure about that, Dave?

FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """

doesn't look right to me (The quotes would appear to be wrong for
[EngineSerialNo])

I'm wondering, though, why there would be two fields with the same value!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There are some syntax problems with your code. Here is the corrected
version:

With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the second
FindFirst will override the first and you may not get the record you
expect.

FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """ 'text
field
'Should be
FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """
If Not .NoMatch Then
'You need a period before the Bookmark
Me.Bookmark = Bookmark
'Should be
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP

If I understand correctly, you are wanting to use a combo box to select
a
record from a quey an use the columns to populate the controls on your
form.

Is the query based on the recordset of the form or is it from a
different
table?

If it is from a different table, I would be concerned about your design
as
the same data should not be kept in two tables. But, if this is
necessary,
you could create a recordset and load the values from the recordset
fields to
the form controls:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
MsgBox "Record Not Found"
Else
Me.txtOne = ![AField]
Me.txtTwo = ![AnotherField]
Me.txtThree = ![AnyOldField]
Me.txtFour = ![CowField]
End With
.Close
End With
Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE
Clause
The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as
the
form's recordset, the usual way to move to a selected record is to use
the
After Update event of the combo box to move to the record:

With Me.RecordsetClone
.FindFirst "[FieldName] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. If it is a text field, the syntax would be:

.FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
.FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP



:
On Jan 10, 11:46?am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just
because there
are 25 fields in the query that the report uses, why do all 25
fields need
to be in the combo box?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Hi All, hope someone can help me with a combo box problem. ?I
have a
combo box that is retrieving fields from a query. ?The query has
25
fields that need to be populated in a report. ?Since the limit of
a
combo box is 20 fields, I am hoping there is a workaround for
this
limitation.

Thank you.

Maurita Searcy- Hide quoted text -

- Show quoted text -

Doug,

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. Only one field will be visible to the
user. Obviously, I am going about populating the form in the wrong
way. Any suggestions? Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. I understand
the concept, but have a couple of questions because I can't get the
code to work. I'm not sure what FindFirst represents. Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita

 

[Klatuu]

The quotes may not be correct. This has always been one of my weak points.
Interesting about the two fields. I was cutting and pasting her code and
didn't notice that. (back to the coffee pot).
--
Dave Hargis, Microsoft Access MVP

You sure about that, Dave?

FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """

doesn't look right to me (The quotes would appear to be wrong for
[EngineSerialNo])

I'm wondering, though, why there would be two fields with the same value!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There are some syntax problems with your code. Here is the corrected
version:

With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the second
FindFirst will override the first and you may not get the record you
expect.

FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """ 'text
field
'Should be
FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """
If Not .NoMatch Then
'You need a period before the Bookmark
Me.Bookmark = Bookmark
'Should be
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP

If I understand correctly, you are wanting to use a combo box to select
a
record from a quey an use the columns to populate the controls on your
form.

Is the query based on the recordset of the form or is it from a
different
table?

If it is from a different table, I would be concerned about your design
as
the same data should not be kept in two tables. But, if this is
necessary,
you could create a recordset and load the values from the recordset
fields to
the form controls:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
MsgBox "Record Not Found"
Else
Me.txtOne = ![AField]
Me.txtTwo = ![AnotherField]
Me.txtThree = ![AnyOldField]
Me.txtFour = ![CowField]
End With
.Close
End With
Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE
Clause
The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as
the
form's recordset, the usual way to move to a selected record is to use
the
After Update event of the combo box to move to the record:

With Me.RecordsetClone
.FindFirst "[FieldName] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. If it is a text field, the syntax would be:

.FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
.FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP



:
On Jan 10, 11:46?am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just
because there
are 25 fields in the query that the report uses, why do all 25
fields need
to be in the combo box?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Hi All, hope someone can help me with a combo box problem. ?I
have a
combo box that is retrieving fields from a query. ?The query has
25
fields that need to be populated in a report. ?Since the limit of
a
combo box is 20 fields, I am hoping there is a workaround for
this
limitation.

Thank you.

Maurita Searcy- Hide quoted text -

- Show quoted text -

Doug,

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. Only one field will be visible to the
user. Obviously, I am going about populating the form in the wrong
way. Any suggestions? Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. I understand
the concept, but have a couple of questions because I can't get the
code to work. I'm not sure what FindFirst represents. Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
FindFirst "[EngineSerialNo] = Me.Combo124 & """ 'text
field
FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita

 

[Maurita]

The quotes may not be correct. �This has always been one of my weak points.
Interesting about the two fields. �I was cutting and pasting her code and
didn't notice that. (back to the coffee pot).
--
Dave Hargis, Microsoft Access MVP

You sure about that, Dave?

� � � � FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """

doesn't look right to me (The quotes would appear to be wrong for
[EngineSerialNo])

I'm wondering, though, why there would be two fields with the same value!

There are some syntax problems with your code. �Here is the corrected
version:
� �With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the second
FindFirst will override the first and you may not get the record you
expect.
� � � �FindFirst "[EngineSerialNo] =Me.Combo124 & """ � � � � � �'text
field
� � � �FindFirst "[Program] = Me.Combo124 & """ � � � � � � � � � � 'text
field
'Should be
� � � �FindFirst "[EngineSerialNo] =Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """
� � � �If Not .NoMatch Then
'You need a period before the Bookmark
� � � � � �Me.Bookmark= Bookmark
'Should be
� � � � � �Me.Bookmark= .Bookmark
� � � �End If
� �End With
--
Dave Hargis, Microsoft Access MVP
:
If I understand correctly, you are wanting to use a combo box to select
a
record from a quey an use the columns to populate the controls on your
form.
Is the query based on the recordset of the form or is it from a
different
table?
If it is from a different table, I would be concerned about your design
as
the same data should not be kept in two tables. �But, if this is
necessary,
you could create a recordset and load the values from the recordset
fields to
the form controls:
Dim rst As DAO.Recordset
� � Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
� �With rst
� � � �If .RecordCount = 0 Then
� � � � � �MsgBox "Record Not Found"
� � � Else
� � � � � Me.txtOne = ![AField]
� � � � � Me.txtTwo = ![AnotherField]
� � � � � Me.txtThree = ![AnyOldField]
� � � � � Me.txtFour = ![CowField]
� � �End With
� � �.Close
� �End With
� �Set rst = Nothing
Notes: The syntax rules as stated below apply here, also in the WHERE
Clause
� � � � � �The names are all made up, use your own.
If it is from the form's recordset or based on the same query/tableas
the
form's recordset, the usual way to move to a selected record is to use
the
After Update event of the combo box to move to the record:
� � With Me.RecordsetClone
� � � � .FindFirst "[FieldName] =" & Me.MyCombo
� � � � If Not .NoMatch Then
� � � � � � Me.Bookmark = .Bookmark
� � � � End If
� � End With
The FindFirst line is coded assuming the field your are comparing to is
numeric. �If it is a text field, the syntax would be:
� � � � .FindFirst "[FieldName] ='" & Me.MyCombo & "'"
And for a Date/Time field:
� � � � .FindFirst "[FieldName] =#" & Me.MyCombo & "#"
--
Dave Hargis, Microsoft Access MVP
:
On Jan 10, 11:46?am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.
What's relationship between the combo box and the report? Just
because there
are 25 fields in the query that the report uses, why do all 25
fields need
to be in the combo box?
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi All, hope someone can help me with a combo box problem. ?I
have a
combo box that is retrieving fields from a query. ?The query has
25
fields that need to be populated in a report. ?Since the limit of
a
combo box is 20 fields, I am hoping there is a workaround for
this
limitation.
Thank you.
Maurita Searcy- Hide quoted text -
- Show quoted text -
Doug,
I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. �Only one field will be visible to the
user. �Obviously, I am going about populating the form inthe wrong
way. �Any suggestions? �Thank you.
Maurita- Hide quoted text -
- Show quoted text -
Thank you Doug for all your help with the above problem. �I understand
the concept, but have a couple of questions because I can't get the
code to work. �I'm not sure what FindFirst represents. �Please note
that my combo box is based on the same query that the form is based
on.
With Me.RecordsetClone
� � FindFirst "[EngineSerialNo] = Me.Combo124 & """� � � � � �'text
field
� � FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
� � Me.Bookmark = Bookmark
End If
End With
Thank you so much for all your help.
Maurita- Hide quoted text -

- Show quoted text -

Thanks All for your help, but I am totally confused. I have tried
different scenarios with the code but am still having trouble. I
continually get a Compile Error: Sub or Function not defined. My
latest code attempt is:

FindFirst [EngineSerialNo] = Me.Combo124 & "" And _
[Program] = Me.Combo124 & "" _

I appreciate all your patience and help.

Maurita

 

[Klatuu]

It is a syntax problem there needs to be a period before the word FindFirst
..FindFirst [EngineSerialNo] = Me.Combo124 & "" And _
[Program] = Me.Combo124 & "" _

Also, as Douglas pointed out, are you sure you want to compare two fields to
the same value?
--
Dave Hargis, Microsoft Access MVP

The quotes may not be correct. �This has always been one of my weak points.
Interesting about the two fields. �I was cutting and pasting her code and
didn't notice that. (back to the coffee pot).
--
Dave Hargis, Microsoft Access MVP

You sure about that, Dave?

� � � � FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """

doesn't look right to me (The quotes would appear to be wrong for
[EngineSerialNo])

I'm wondering, though, why there would be two fields with the same value!

There are some syntax problems with your code. �Here is the corrected
version:

� �With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the second
FindFirst will override the first and you may not get the record you
expect.

� � � �FindFirst "[EngineSerialNo] = Me.Combo124 & """ � � � � � �'text
field
� � � �FindFirst "[Program] = Me.Combo124 & """ � � � � � � � � � � 'text
field
'Should be
� � � �FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program = """ &
Me.Combo124 & """
� � � �If Not .NoMatch Then
'You need a period before the Bookmark
� � � � � �Me.Bookmark = Bookmark
'Should be
� � � � � �Me.Bookmark = .Bookmark
� � � �End If
� �End With

"Maurita" wrote:

If I understand correctly, you are wanting to use a combo box to select
a
record from a quey an use the columns to populate the controls on your
form.

Is the query based on the recordset of the form or is it from a
different
table?

If it is from a different table, I would be concerned about your design
as
the same data should not be kept in two tables. �But, if this is
necessary,
you could create a recordset and load the values from the recordset
fields to
the form controls:

Dim rst As DAO.Recordset

� � Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
� �With rst
� � � �If .RecordCount = 0 Then
� � � � � �MsgBox "Record Not Found"
� � � Else
� � � � � Me.txtOne = ![AField]
� � � � � Me.txtTwo = ![AnotherField]
� � � � � Me.txtThree = ![AnyOldField]
� � � � � Me.txtFour = ![CowField]
� � �End With
� � �.Close
� �End With
� �Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the WHERE
Clause
� � � � � �The names are all made up, use your own.

If it is from the form's recordset or based on the same query/table as
the
form's recordset, the usual way to move to a selected record is to use
the
After Update event of the combo box to move to the record:

� � With Me.RecordsetClone
� � � � .FindFirst "[FieldName] = " & Me.MyCombo
� � � � If Not .NoMatch Then
� � � � � � Me.Bookmark = .Bookmark
� � � � End If
� � End With

The FindFirst line is coded assuming the field your are comparing to is
numeric. �If it is a text field, the syntax would be:

� � � � .FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
� � � � .FindFirst "[FieldName] = #" & Me.MyCombo & "#"

:
On Jan 10, 11:46?am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report? Just
because there
are 25 fields in the query that the report uses, why do all 25
fields need
to be in the combo box?

Hi All, hope someone can help me with a combo box problem. ?I
have a
combo box that is retrieving fields from a query. ?The query has
25
fields that need to be populated in a report. ?Since the limit of
a
combo box is 20 fields, I am hoping there is a workaround for
this
limitation.

Thank you.

Maurita Searcy- Hide quoted text -

- Show quoted text -

I am trying to populate 25 fields on the form using a combo box to
select from, based on a query. �Only one field will be visible to the
user. �Obviously, I am going about populating the form in the wrong
way. �Any suggestions? �Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. �I understand
the concept, but have a couple of questions because I can't get the
code to work. �I'm not sure what FindFirst represents. �Please note
that my combo box is based on the same query that the form is based
on.

With Me.RecordsetClone
� � FindFirst "[EngineSerialNo] = Me.Combo124 & """ � � � � � �'text
field
� � FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
� � Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita- Hide quoted text -

- Show quoted text -

Thanks All for your help, but I am totally confused. I have tried
different scenarios with the code but am still having trouble. I
continually get a Compile Error: Sub or Function not defined. My
latest code attempt is:

FindFirst [EngineSerialNo] = Me.Combo124 & "" And _
[Program] = Me.Combo124 & "" _

I appreciate all your patience and help.

Maurita

 

[Douglas J. Steele]

Also, even if you do want to compare two fields to the same value, the
FindFirst condition is wrong. The names of the field must be in quotes, the
values being compared to must be outside the quotes. As well, the line
continuation character is incorrectly positioned: it cannot be inside the
quotes.

.FindFirst "[EngineSerialNo] = '" & Me.Combo124 & _
"' And [Program] = '" & Me.Combo124 & "'"

Exagerated for clarity, that's


.FindFirst "[EngineSerialNo] = ' " & Me.Combo124 & _
" ' And [Program] = ' " & Me.Combo124 & " ' "

That assumes that the value in Me.Combo124 does not contain any apostrophes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It is a syntax problem there needs to be a period before the word
FindFirst
.FindFirst [EngineSerialNo] = Me.Combo124 & "" And _
[Program] = Me.Combo124 & "" _

Also, as Douglas pointed out, are you sure you want to compare two fields
to
the same value?
--
Dave Hargis, Microsoft Access MVP

The quotes may not be correct. ?This has always been one of my weak
points.
Interesting about the two fields. ?I was cutting and pasting her code
and
didn't notice that. (back to the coffee pot).
--
Dave Hargis, Microsoft Access MVP



:
You sure about that, Dave?

? ? ? ? FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program =
""" &
Me.Combo124 & """

doesn't look right to me (The quotes would appear to be wrong for
[EngineSerialNo])

I'm wondering, though, why there would be two fields with the same
value!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There are some syntax problems with your code. ?Here is the
corrected
version:

? ?With Me.RecordsetClone
'You need to use both fields in the same Findfirst, otherwise, the
second
FindFirst will override the first and you may not get the record
you
expect.

? ? ? ?FindFirst "[EngineSerialNo] = Me.Combo124 & """ ? ? ? ? ?
?'text
field
? ? ? ?FindFirst "[Program] = Me.Combo124 & """ ? ? ? ? ? ? ? ? ? ?
'text
field
'Should be
? ? ? ?FindFirst "[EngineSerialNo] = Me.Combo124 & "" AND [Program
= """ &
Me.Combo124 & """
? ? ? ?If Not .NoMatch Then
'You need a period before the Bookmark
? ? ? ? ? ?Me.Bookmark = Bookmark
'Should be
? ? ? ? ? ?Me.Bookmark = .Bookmark
? ? ? ?End If
? ?End With

--
Dave Hargis, Microsoft Access MVP

:

On Jan 10, 1:40 pm, Klatuu <[email protected]>
wrote:
If I understand correctly, you are wanting to use a combo box to
select
a
record from a quey an use the columns to populate the controls
on your
form.

Is the query based on the recordset of the form or is it from a
different
table?

If it is from a different table, I would be concerned about your
design
as
the same data should not be kept in two tables. ?But, if this is
necessary,
you could create a recordset and load the values from the
recordset
fields to
the form controls:

Dim rst As DAO.Recordset

? ? Set rst = Currentdb.OpenRecordset("SELECT * FROM MyTable
Where
[FieldName] = " & Me.MyCombo & ";", dbOpenDynaset)
? ?With rst
? ? ? ?If .RecordCount = 0 Then
? ? ? ? ? ?MsgBox "Record Not Found"
? ? ? Else
? ? ? ? ? Me.txtOne = ![AField]
? ? ? ? ? Me.txtTwo = ![AnotherField]
? ? ? ? ? Me.txtThree = ![AnyOldField]
? ? ? ? ? Me.txtFour = ![CowField]
? ? ?End With
? ? ?.Close
? ?End With
? ?Set rst = Nothing

Notes: The syntax rules as stated below apply here, also in the
WHERE
Clause
? ? ? ? ? ?The names are all made up, use your own.

If it is from the form's recordset or based on the same
query/table as
the
form's recordset, the usual way to move to a selected record is
to use
the
After Update event of the combo box to move to the record:

? ? With Me.RecordsetClone
? ? ? ? .FindFirst "[FieldName] = " & Me.MyCombo
? ? ? ? If Not .NoMatch Then
? ? ? ? ? ? Me.Bookmark = .Bookmark
? ? ? ? End If
? ? End With

The FindFirst line is coded assuming the field your are
comparing to is
numeric. ?If it is a text field, the syntax would be:

? ? ? ? .FindFirst "[FieldName] = '" & Me.MyCombo & "'"

And for a Date/Time field:
? ? ? ? .FindFirst "[FieldName] = #" & Me.MyCombo & "#"

--
Dave Hargis, Microsoft Access MVP

:
On Jan 10, 11:46?am, "Douglas J. Steele"
I'm not sure I understand how you're using the combo box.

What's relationship between the combo box and the report?
Just
because there
are 25 fields in the query that the report uses, why do all
25
fields need
to be in the combo box?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Hi All, hope someone can help me with a combo box problem.
?I
have a
combo box that is retrieving fields from a query. ?The
query has
25
fields that need to be populated in a report. ?Since the
limit of
a
combo box is 20 fields, I am hoping there is a workaround
for
this
limitation.

Thank you.

Maurita Searcy- Hide quoted text -

- Show quoted text -

Doug,

I am trying to populate 25 fields on the form using a combo
box to
select from, based on a query. ?Only one field will be visible
to the
user. ?Obviously, I am going about populating the form in the
wrong
way. ?Any suggestions? ?Thank you.

Maurita- Hide quoted text -

- Show quoted text -

Thank you Doug for all your help with the above problem. ?I
understand
the concept, but have a couple of questions because I can't get
the
code to work. ?I'm not sure what FindFirst represents. ?Please
note
that my combo box is based on the same query that the form is
based
on.

With Me.RecordsetClone
? ? FindFirst "[EngineSerialNo] = Me.Combo124 & """ ? ? ? ? ?
?'text
field
? ? FindFirst "[Program] = Me.Combo124 & """
'text field
If Not .NoMatch Then
? ? Me.Bookmark = Bookmark
End If
End With

Thank you so much for all your help.

Maurita- Hide quoted text -

- Show quoted text -

Thanks All for your help, but I am totally confused. I have tried
different scenarios with the code but am still having trouble. I
continually get a Compile Error: Sub or Function not defined. My
latest code attempt is:

FindFirst [EngineSerialNo] = Me.Combo124 & "" And _
[Program] = Me.Combo124 & "" _

I appreciate all your patience and help.

Maurita