|
 |
access thread: Combo Box Error Message
Message #1 by amy.bender@p... on Thu, 12 Dec 2002 17:59:27
|
|
I am creating a search form for my customer. I have two combo boxes. One
lists fields the user can choose to query and then the other shows values
in the field for the user to choose from.
For example, the user would select "Date Started" from the first combo box
which would then populate the second combo box to show all the current
values in that field. Then it changes the form to show those records.
I'm doing this with vba code (See below). When I run it the first time,
it works (sometimes), but if I switch to a field of another data type, it
gives me the following error:
"The value you entered isn't valid for this field"
The funny thing is...I have another form setup exactly the same, and it
runs just fine. The first time I tested the other form, it showed the
same error message, but then I closed it out and went back in and it
corrected itself and has been working fine since.
I've checked and both combo boxes are unbound. I debugged and the error
comes up before it even runs the code for the 2nd combo box. I looked up
support.microsoft.com but didn't find anything. I'm stumped. Please
help!! I have training with my customer on this Tuesday. Augh!
Private Sub cboACFOField_AfterUpdate()
Dim SQL1
Me.cboACFOData.RowSource = ""
SQL1 = "SELECT " & Me.cboACFOField.Column(1) & " FROM " &
Me.cboACFOField.Column(3)
Me.cboACFOData.RowSource = SQL1
End Sub
Private Sub cboACFOData_AfterUpdate()
Me.frm_Search_AFOCFO.Form.FilterOn = False
Select Case Me.cboACFOField.Column(2)
'if a date field
Case "D"
Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = #" &
Me.cboACFOData & "#"
'if a text field
Case "S"
Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = '" &
Me.cboACFOData & "'"
Case Else
Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = " &
Me.cboACFOData
End Select
Me.frm_Search_AFOCFO.Form.FilterOn = True
Me.frm_Search_AFOCFO.Requery
End Sub
Message #2 by "Charlie Goodwin" <cgoodwin@c...> on Fri, 13 Dec 2002 16:12:06 -0500
|
|
Amy,
I have a form with linked listboxes that might offer a useful solution.
The RowSource for the first Listbox is a table
TblCbo1
QueryID AutoNum Just IDs the record
QueryDescText Text Test displayed in the LIstBox
QueryName Text Actual name of the query
A couple of typical records
3, "Search by alpha ascending", "QryAllByAlphaAscending"
6, "Search by age ascending", "QryAllByAgeAscending"
Only QueryDescText shows in List1 the others are hidden columns.
The essentials of the sub of the On click event for the first listbox:
Private Sub List1_Click()
' set value for row source of second ListBox
Forms!FrmTestSearch!List2.RowSource =3D Forms!FrmTestSearch!List1.Colum
n(2)
End Sub
The queries all are built to populate List2 with an ID and text to make sel
ection easy.
I made it work smoother by cleaning out the second listbox after it's used
and disabling
the search button, after the selected record is viewed. But the essense is
just using a
table to reference a bunch of queries to associate descriptive text.
I don't know if I am making this clear or not. Ask more If I need to desc
ribe it better.
Charlie
> I am creating a search form for my customer. I have two combo boxes. One
> lists fields the user can choose to query and then the other shows values
> in the field for the user to choose from.
>
> For example, the user would select "Date Started" from the first combo box
> which would then populate the second combo box to show all the current
> values in that field. Then it changes the form to show those records.
>
> I'm doing this with vba code (See below). When I run it the first time,
> it works (sometimes), but if I switch to a field of another data type, it
> gives me the following error:
>
> "The value you entered isn't valid for this field"
>
> The funny thing is...I have another form setup exactly the same, and it
> runs just fine. The first time I tested the other form, it showed the
> same error message, but then I closed it out and went back in and it
> corrected itself and has been working fine since.
>
> I've checked and both combo boxes are unbound. I debugged and the error
> comes up before it even runs the code for the 2nd combo box. I looked up
> support.microsoft.com but didn't find anything. I'm stumped. Please
> help!! I have training with my customer on this Tuesday. Augh!
>
> Private Sub cboACFOField_AfterUpdate()
> Dim SQL1
>
> Me.cboACFOData.RowSource =3D ""
>
> SQL1 =3D "SELECT " & Me.cboACFOField.Column(1) & " FROM " &
> Me.cboACFOField.Column(3)
>
> Me.cboACFOData.RowSource =3D SQL1
>
> End Sub
>
> Private Sub cboACFOData_AfterUpdate()
> Me.frm_Search_AFOCFO.Form.FilterOn =3D False
>
> Select Case Me.cboACFOField.Column(2)
> 'if a date field
> Case "D"
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
#" &
> Me.cboACFOData & "#"
> 'if a text field
> Case "S"
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
'" &
> Me.cboACFOData & "'"
> Case Else
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
" &
> Me.cboACFOData
> End Select
> Me.frm_Search_AFOCFO.Form.FilterOn =3D True
> Me.frm_Search_AFOCFO.Requery
> End Sub
Message #3 by "Bob Bedell" <bobbedell15@m...> on Fri, 13 Dec 2002 22:27:46 +0000
|
|
Hi Amy,
Sorry I don't have an answer for you, but I have a question.
Are you referring to a subform here, or a second form. I'm just
curious about your syntax. It's a subform, isn't it?
Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = #" &
Me.cboACFOData & "#"
Thanks
>From: "Charlie Goodwin" <cgoodwin@c...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Combo Box Error Message
>Date: Fri, 13 Dec 2002 16:12:06 -0500
>
>Amy,
>
> I have a form with linked listboxes that might offer a useful solution.
>
>The RowSource for the first Listbox is a table
>
>TblCbo1
>QueryID AutoNum Just IDs the record
>QueryDescText Text Test displayed in the LIstBox
>QueryName Text Actual name of the query
>
>
>A couple of typical records
>3, "Search by alpha ascending", "QryAllByAlphaAscending"
>6, "Search by age ascending", "QryAllByAgeAscending"
>
>
>
>Only QueryDescText shows in List1 the others are hidden columns.
>
>
>The essentials of the sub of the On click event for the first listbox:
>
>Private Sub List1_Click()
> ' set value for row source of second ListBox
> Forms!FrmTestSearch!List2.RowSource =
>Forms!FrmTestSearch!List1.Column(2)
>End Sub
>
>The queries all are built to populate List2 with an ID and text to make
>selection easy.
>
>I made it work smoother by cleaning out the second listbox after it's used
>and disabling
>the search button, after the selected record is viewed. But the essense is
>just using a
>table to reference a bunch of queries to associate descriptive text.
>
>I don't know if I am making this clear or not. Ask more If I need to
>describe it better.
>
>Charlie
>
>
>
>
>
> > I am creating a search form for my customer. I have two combo boxes.
>One
> > lists fields the user can choose to query and then the other shows
>values
> > in the field for the user to choose from.
> >
> > For example, the user would select "Date Started" from the first combo
>box
> > which would then populate the second combo box to show all the current
> > values in that field. Then it changes the form to show those records.
> >
> > I'm doing this with vba code (See below). When I run it the first time,
> > it works (sometimes), but if I switch to a field of another data type,
>it
> > gives me the following error:
> >
> > "The value you entered isn't valid for this field"
> >
> > The funny thing is...I have another form setup exactly the same, and it
> > runs just fine. The first time I tested the other form, it showed the
> > same error message, but then I closed it out and went back in and it
> > corrected itself and has been working fine since.
> >
> > I've checked and both combo boxes are unbound. I debugged and the error
> > comes up before it even runs the code for the 2nd combo box. I looked
>up
> > support.microsoft.com but didn't find anything. I'm stumped. Please
> > help!! I have training with my customer on this Tuesday. Augh!
> >
> > Private Sub cboACFOField_AfterUpdate()
> > Dim SQL1
> >
> > Me.cboACFOData.RowSource = ""
> >
> > SQL1 = "SELECT " & Me.cboACFOField.Column(1) & " FROM " &
> > Me.cboACFOField.Column(3)
> >
> > Me.cboACFOData.RowSource = SQL1
> >
> > End Sub
> >
> > Private Sub cboACFOData_AfterUpdate()
> > Me.frm_Search_AFOCFO.Form.FilterOn = False
> >
> > Select Case Me.cboACFOField.Column(2)
> > 'if a date field
> > Case "D"
> > Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = #"
>&
> > Me.cboACFOData & "#"
> > 'if a text field
> > Case "S"
> > Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = '"
>&
> > Me.cboACFOData & "'"
> > Case Else
> > Me.frm_Search_AFOCFO.Form.Filter = Me.cboACFOField & " = " &
> > Me.cboACFOData
> > End Select
> > Me.frm_Search_AFOCFO.Form.FilterOn = True
> > Me.frm_Search_AFOCFO.Requery
> > End Sub
>
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
Message #4 by "Charlie Goodwin" <cgoodwin@c...> on Fri, 13 Dec 2002 22:38:46 -0500
|
|
More on a scheme for linked combos,
One other aspect. Using the scheme I described makes it quite easy to add
new queries to change the selections that populate the second list box.
Don't know whether easy maintenance and updates are a factor in your applic
ation.
Charlie
Amy,
I have a form with linked listboxes that might offer a useful solution.
The RowSource for the first Listbox is a table
TblCbo1
QueryID AutoNum Just IDs the record
QueryDescText Text Test displayed in the LIstBox
QueryName Text Actual name of the query
A couple of typical records
3, "Search by alpha ascending", "QryAllByAlphaAscending"
6, "Search by age ascending", "QryAllByAgeAscending"
Only QueryDescText shows in List1 the others are hidden columns.
The essentials of the sub of the On click event for the first listbox:
Private Sub List1_Click()
' set value for row source of second ListBox
Forms!FrmTestSearch!List2.RowSource =3D Forms!FrmTestSearch!List1.Colum
n(2)
End Sub
The queries all are built to populate List2 with an ID and text to make sel
ection easy.
I made it work smoother by cleaning out the second listbox after it's used
and disabling
the search button, after the selected record is viewed. But the essense is
just using a
table to reference a bunch of queries to associate descriptive text.
I don't know if I am making this clear or not. Ask more If I need to desc
ribe it better.
Charlie
> I am creating a search form for my customer. I have two combo boxes. One
> lists fields the user can choose to query and then the other shows values
> in the field for the user to choose from.
>
> For example, the user would select "Date Started" from the first combo box
> which would then populate the second combo box to show all the current
> values in that field. Then it changes the form to show those records.
>
> I'm doing this with vba code (See below). When I run it the first time,
> it works (sometimes), but if I switch to a field of another data type, it
> gives me the following error:
>
> "The value you entered isn't valid for this field"
>
> The funny thing is...I have another form setup exactly the same, and it
> runs just fine. The first time I tested the other form, it showed the
> same error message, but then I closed it out and went back in and it
> corrected itself and has been working fine since.
>
> I've checked and both combo boxes are unbound. I debugged and the error
> comes up before it even runs the code for the 2nd combo box. I looked up
> support.microsoft.com but didn't find anything. I'm stumped. Please
> help!! I have training with my customer on this Tuesday. Augh!
>
> Private Sub cboACFOField_AfterUpdate()
> Dim SQL1
>
> Me.cboACFOData.RowSource =3D ""
>
> SQL1 =3D "SELECT " & Me.cboACFOField.Column(1) & " FROM " &
> Me.cboACFOField.Column(3)
>
> Me.cboACFOData.RowSource =3D SQL1
>
> End Sub
>
> Private Sub cboACFOData_AfterUpdate()
> Me.frm_Search_AFOCFO.Form.FilterOn =3D False
>
> Select Case Me.cboACFOField.Column(2)
> 'if a date field
> Case "D"
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
#" &
> Me.cboACFOData & "#"
> 'if a text field
> Case "S"
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
'" &
> Me.cboACFOData & "'"
> Case Else
> Me.frm_Search_AFOCFO.Form.Filter =3D Me.cboACFOField & " =3D
" &
> Me.cboACFOData
> End Select
> Me.frm_Search_AFOCFO.Form.FilterOn =3D True
> Me.frm_Search_AFOCFO.Requery
> End Sub
Message #5 by amy.bender@p... on Thu, 19 Dec 2002 17:31:24
|
|
Thank you.
Referencing subform? Yes, it is. The main screen includes the tabs with
a subform on each tab. In the last two tabs, I have the search forms.
I did solve this, however with a work around. Instead of having two combo
boxes - field, data - I have one combo box and a text box for the user to
enter the 'data' (criteria).
|
|
|
View Cart
