|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
In Access 2003 I have designed a form with a search field for Request ID.
This field is unbound but when typed in to this field it brings up the
relevant record if typed in correctly.
However, should a entry be typed in that does no exist, this automatically
brings up the first record within the dataset. The users will most likely not
realise that this has happened and will assume that the data is correct.
I would try and use limit to list but as the field is not bound then this
does not work
Does anyone have any ideas?
Many Thanks,
Matt
|
|
Whether or not a control is bound has no effect on whether the Limit To List
event fires. It is controled by the Limit to List property of the control
(assuming it is a combo box or a list box - they are the only controls with
the limit to list property). Although it seems counterintuitive, the limit
to list property has to be set to yes for the event to fire. You then have
to code the event to handle the event properly.
If you need more detail, post back with specifics, and perhaps we can
correct your problem.
"Matt Dawson" wrote:
[Quoted Text] > In Access 2003 I have designed a form with a search field for Request ID.
> This field is unbound but when typed in to this field it brings up the
> relevant record if typed in correctly.
>
> However, should a entry be typed in that does no exist, this automatically
> brings up the first record within the dataset. The users will most likely not
> realise that this has happened and will assume that the data is correct.
>
> I would try and use limit to list but as the field is not bound then this
> does not work
>
> Does anyone have any ideas?
> Many Thanks,
> Matt
|
|
Ok, you have lost me now!
I understand your point about the limit to list! That was my own fault but
how do i go about the record set not changing if the typed Request ID does
not exist?
Reuqest ID is an unbound field and has to be this way in order for me to
search!
Matt
"Klatuu" wrote:
[Quoted Text] > Whether or not a control is bound has no effect on whether the Limit To List
> event fires. It is controled by the Limit to List property of the control
> (assuming it is a combo box or a list box - they are the only controls with
> the limit to list property). Although it seems counterintuitive, the limit
> to list property has to be set to yes for the event to fire. You then have
> to code the event to handle the event properly.
>
> If you need more detail, post back with specifics, and perhaps we can
> correct your problem.
>
> "Matt Dawson" wrote:
>
> > In Access 2003 I have designed a form with a search field for Request ID.
> > This field is unbound but when typed in to this field it brings up the
> > relevant record if typed in correctly.
> >
> > However, should a entry be typed in that does no exist, this automatically
> > brings up the first record within the dataset. The users will most likely not
> > realise that this has happened and will assume that the data is correct.
> >
> > I would try and use limit to list but as the field is not bound then this
> > does not work
> >
> > Does anyone have any ideas?
> > Many Thanks,
> > Matt
|
|
You are correct. A combo box has to be unbound to use in a search (well, not
really, but that takes a little bit more complex coding to accomplish). If
your search is not successful, the form will still contain the same record.
Here is an example:
Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
'See if the user wants to add a record
If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
'Create the record in the table
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
'Requery the form recordset so it will include the new record
Me.Requery
'Position the form on the new record
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
'Not really necessary, but for user convenience, takes out the incorrect value
Me.cboActivity.Undo
Response = acDataErrContinue
End If
End Sub
"Matt Dawson" wrote:
[Quoted Text] > Ok, you have lost me now!
>
> I understand your point about the limit to list! That was my own fault but
> how do i go about the record set not changing if the typed Request ID does
> not exist?
> Reuqest ID is an unbound field and has to be this way in order for me to
> search!
>
> Matt
>
> "Klatuu" wrote:
>
> > Whether or not a control is bound has no effect on whether the Limit To List
> > event fires. It is controled by the Limit to List property of the control
> > (assuming it is a combo box or a list box - they are the only controls with
> > the limit to list property). Although it seems counterintuitive, the limit
> > to list property has to be set to yes for the event to fire. You then have
> > to code the event to handle the event properly.
> >
> > If you need more detail, post back with specifics, and perhaps we can
> > correct your problem.
> >
> > "Matt Dawson" wrote:
> >
> > > In Access 2003 I have designed a form with a search field for Request ID.
> > > This field is unbound but when typed in to this field it brings up the
> > > relevant record if typed in correctly.
> > >
> > > However, should a entry be typed in that does no exist, this automatically
> > > brings up the first record within the dataset. The users will most likely not
> > > realise that this has happened and will assume that the data is correct.
> > >
> > > I would try and use limit to list but as the field is not bound then this
> > > does not work
> > >
> > > Does anyone have any ideas?
> > > Many Thanks,
> > > Matt
|
|
And i put this within code for what? THe forms properties for open?
"Klatuu" wrote:
[Quoted Text] > You are correct. A combo box has to be unbound to use in a search (well, not
> really, but that takes a little bit more complex coding to accomplish). If
> your search is not successful, the form will still contain the same record.
>
> Here is an example:
>
> Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
> Dim rst As Recordset
>
> 'See if the user wants to add a record
> If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
> & "Do you want to add it", _
> vbInformation + vbYesNo, "Not Found") = vbYes Then
> 'Create the record in the table
> CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
> & "VALUES ('" & NewData & "');"), dbFailOnError
> 'Requery the form recordset so it will include the new record
> Me.Requery
> 'Position the form on the new record
> Set rst = Me.RecordsetClone
> rst.FindFirst "[Activity] = '" & NewData & "'"
> Me.Bookmark = rst.Bookmark
> Set rst = Nothing
> Response = acDataErrAdded
> Else
> 'Not really necessary, but for user convenience, takes out the incorrect value
> Me.cboActivity.Undo
> Response = acDataErrContinue
> End If
>
> End Sub
>
>
> "Matt Dawson" wrote:
>
> > Ok, you have lost me now!
> >
> > I understand your point about the limit to list! That was my own fault but
> > how do i go about the record set not changing if the typed Request ID does
> > not exist?
> > Reuqest ID is an unbound field and has to be this way in order for me to
> > search!
> >
> > Matt
> >
> > "Klatuu" wrote:
> >
> > > Whether or not a control is bound has no effect on whether the Limit To List
> > > event fires. It is controled by the Limit to List property of the control
> > > (assuming it is a combo box or a list box - they are the only controls with
> > > the limit to list property). Although it seems counterintuitive, the limit
> > > to list property has to be set to yes for the event to fire. You then have
> > > to code the event to handle the event properly.
> > >
> > > If you need more detail, post back with specifics, and perhaps we can
> > > correct your problem.
> > >
> > > "Matt Dawson" wrote:
> > >
> > > > In Access 2003 I have designed a form with a search field for Request ID.
> > > > This field is unbound but when typed in to this field it brings up the
> > > > relevant record if typed in correctly.
> > > >
> > > > However, should a entry be typed in that does no exist, this automatically
> > > > brings up the first record within the dataset. The users will most likely not
> > > > realise that this has happened and will assume that the data is correct.
> > > >
> > > > I would try and use limit to list but as the field is not bound then this
> > > > does not work
> > > >
> > > > Does anyone have any ideas?
> > > > Many Thanks,
> > > > Matt
|
|
No, notice the name of the sub. It goes in the NotInList event of the combo
you use to do the search. What I posted is an example from one of my forms.
You will need to change the names to match yours.
"Matt Dawson" wrote:
[Quoted Text] > And i put this within code for what? THe forms properties for open?
>
>
>
> "Klatuu" wrote:
>
> > You are correct. A combo box has to be unbound to use in a search (well, not
> > really, but that takes a little bit more complex coding to accomplish). If
> > your search is not successful, the form will still contain the same record.
> >
> > Here is an example:
> >
> > Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
> > Dim rst As Recordset
> >
> > 'See if the user wants to add a record
> > If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
> > & "Do you want to add it", _
> > vbInformation + vbYesNo, "Not Found") = vbYes Then
> > 'Create the record in the table
> > CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
> > & "VALUES ('" & NewData & "');"), dbFailOnError
> > 'Requery the form recordset so it will include the new record
> > Me.Requery
> > 'Position the form on the new record
> > Set rst = Me.RecordsetClone
> > rst.FindFirst "[Activity] = '" & NewData & "'"
> > Me.Bookmark = rst.Bookmark
> > Set rst = Nothing
> > Response = acDataErrAdded
> > Else
> > 'Not really necessary, but for user convenience, takes out the incorrect value
> > Me.cboActivity.Undo
> > Response = acDataErrContinue
> > End If
> >
> > End Sub
> >
> >
> > "Matt Dawson" wrote:
> >
> > > Ok, you have lost me now!
> > >
> > > I understand your point about the limit to list! That was my own fault but
> > > how do i go about the record set not changing if the typed Request ID does
> > > not exist?
> > > Reuqest ID is an unbound field and has to be this way in order for me to
> > > search!
> > >
> > > Matt
> > >
> > > "Klatuu" wrote:
> > >
> > > > Whether or not a control is bound has no effect on whether the Limit To List
> > > > event fires. It is controled by the Limit to List property of the control
> > > > (assuming it is a combo box or a list box - they are the only controls with
> > > > the limit to list property). Although it seems counterintuitive, the limit
> > > > to list property has to be set to yes for the event to fire. You then have
> > > > to code the event to handle the event properly.
> > > >
> > > > If you need more detail, post back with specifics, and perhaps we can
> > > > correct your problem.
> > > >
> > > > "Matt Dawson" wrote:
> > > >
> > > > > In Access 2003 I have designed a form with a search field for Request ID.
> > > > > This field is unbound but when typed in to this field it brings up the
> > > > > relevant record if typed in correctly.
> > > > >
> > > > > However, should a entry be typed in that does no exist, this automatically
> > > > > brings up the first record within the dataset. The users will most likely not
> > > > > realise that this has happened and will assume that the data is correct.
> > > > >
> > > > > I would try and use limit to list but as the field is not bound then this
> > > > > does not work
> > > > >
> > > > > Does anyone have any ideas?
> > > > > Many Thanks,
> > > > > Matt
|
|
It is not a combo box though, it is a text box
"Klatuu" wrote:
[Quoted Text] > No, notice the name of the sub. It goes in the NotInList event of the combo
> you use to do the search. What I posted is an example from one of my forms.
> You will need to change the names to match yours.
>
> "Matt Dawson" wrote:
>
> > And i put this within code for what? THe forms properties for open?
> >
> >
> >
> > "Klatuu" wrote:
> >
> > > You are correct. A combo box has to be unbound to use in a search (well, not
> > > really, but that takes a little bit more complex coding to accomplish). If
> > > your search is not successful, the form will still contain the same record.
> > >
> > > Here is an example:
> > >
> > > Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
> > > Dim rst As Recordset
> > >
> > > 'See if the user wants to add a record
> > > If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
> > > & "Do you want to add it", _
> > > vbInformation + vbYesNo, "Not Found") = vbYes Then
> > > 'Create the record in the table
> > > CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
> > > & "VALUES ('" & NewData & "');"), dbFailOnError
> > > 'Requery the form recordset so it will include the new record
> > > Me.Requery
> > > 'Position the form on the new record
> > > Set rst = Me.RecordsetClone
> > > rst.FindFirst "[Activity] = '" & NewData & "'"
> > > Me.Bookmark = rst.Bookmark
> > > Set rst = Nothing
> > > Response = acDataErrAdded
> > > Else
> > > 'Not really necessary, but for user convenience, takes out the incorrect value
> > > Me.cboActivity.Undo
> > > Response = acDataErrContinue
> > > End If
> > >
> > > End Sub
> > >
> > >
> > > "Matt Dawson" wrote:
> > >
> > > > Ok, you have lost me now!
> > > >
> > > > I understand your point about the limit to list! That was my own fault but
> > > > how do i go about the record set not changing if the typed Request ID does
> > > > not exist?
> > > > Reuqest ID is an unbound field and has to be this way in order for me to
> > > > search!
> > > >
> > > > Matt
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > Whether or not a control is bound has no effect on whether the Limit To List
> > > > > event fires. It is controled by the Limit to List property of the control
> > > > > (assuming it is a combo box or a list box - they are the only controls with
> > > > > the limit to list property). Although it seems counterintuitive, the limit
> > > > > to list property has to be set to yes for the event to fire. You then have
> > > > > to code the event to handle the event properly.
> > > > >
> > > > > If you need more detail, post back with specifics, and perhaps we can
> > > > > correct your problem.
> > > > >
> > > > > "Matt Dawson" wrote:
> > > > >
> > > > > > In Access 2003 I have designed a form with a search field for Request ID.
> > > > > > This field is unbound but when typed in to this field it brings up the
> > > > > > relevant record if typed in correctly.
> > > > > >
> > > > > > However, should a entry be typed in that does no exist, this automatically
> > > > > > brings up the first record within the dataset. The users will most likely not
> > > > > > realise that this has happened and will assume that the data is correct.
> > > > > >
> > > > > > I would try and use limit to list but as the field is not bound then this
> > > > > > does not work
> > > > > >
> > > > > > Does anyone have any ideas?
> > > > > > Many Thanks,
> > > > > > Matt
|
|
A text box is not a good control to use for searching. Sure, you can do it,
but it is easier for you to set up and easier for the user to use. Based on
the fact that your original post said
"I would try and use limit to list but as the field is not bound then this
does not work"
I was pretty sure it was a combo. As I said earlier, only comb and list
boxes have Limit to List.
If you want to use a text box for a search (or a shoe for a hammer)
Move the code to the text box's After Upate event. It will take some
modification, but will work.
"Matt Dawson" wrote:
[Quoted Text] > It is not a combo box though, it is a text box
>
> "Klatuu" wrote:
>
> > No, notice the name of the sub. It goes in the NotInList event of the combo
> > you use to do the search. What I posted is an example from one of my forms.
> > You will need to change the names to match yours.
> >
> > "Matt Dawson" wrote:
> >
> > > And i put this within code for what? THe forms properties for open?
> > >
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > You are correct. A combo box has to be unbound to use in a search (well, not
> > > > really, but that takes a little bit more complex coding to accomplish). If
> > > > your search is not successful, the form will still contain the same record.
> > > >
> > > > Here is an example:
> > > >
> > > > Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
> > > > Dim rst As Recordset
> > > >
> > > > 'See if the user wants to add a record
> > > > If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
> > > > & "Do you want to add it", _
> > > > vbInformation + vbYesNo, "Not Found") = vbYes Then
> > > > 'Create the record in the table
> > > > CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
> > > > & "VALUES ('" & NewData & "');"), dbFailOnError
> > > > 'Requery the form recordset so it will include the new record
> > > > Me.Requery
> > > > 'Position the form on the new record
> > > > Set rst = Me.RecordsetClone
> > > > rst.FindFirst "[Activity] = '" & NewData & "'"
> > > > Me.Bookmark = rst.Bookmark
> > > > Set rst = Nothing
> > > > Response = acDataErrAdded
> > > > Else
> > > > 'Not really necessary, but for user convenience, takes out the incorrect value
> > > > Me.cboActivity.Undo
> > > > Response = acDataErrContinue
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > >
> > > > "Matt Dawson" wrote:
> > > >
> > > > > Ok, you have lost me now!
> > > > >
> > > > > I understand your point about the limit to list! That was my own fault but
> > > > > how do i go about the record set not changing if the typed Request ID does
> > > > > not exist?
> > > > > Reuqest ID is an unbound field and has to be this way in order for me to
> > > > > search!
> > > > >
> > > > > Matt
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > Whether or not a control is bound has no effect on whether the Limit To List
> > > > > > event fires. It is controled by the Limit to List property of the control
> > > > > > (assuming it is a combo box or a list box - they are the only controls with
> > > > > > the limit to list property). Although it seems counterintuitive, the limit
> > > > > > to list property has to be set to yes for the event to fire. You then have
> > > > > > to code the event to handle the event properly.
> > > > > >
> > > > > > If you need more detail, post back with specifics, and perhaps we can
> > > > > > correct your problem.
> > > > > >
> > > > > > "Matt Dawson" wrote:
> > > > > >
> > > > > > > In Access 2003 I have designed a form with a search field for Request ID.
> > > > > > > This field is unbound but when typed in to this field it brings up the
> > > > > > > relevant record if typed in correctly.
> > > > > > >
> > > > > > > However, should a entry be typed in that does no exist, this automatically
> > > > > > > brings up the first record within the dataset. The users will most likely not
> > > > > > > realise that this has happened and will assume that the data is correct.
> > > > > > >
> > > > > > > I would try and use limit to list but as the field is not bound then this
> > > > > > > does not work
> > > > > > >
> > > > > > > Does anyone have any ideas?
> > > > > > > Many Thanks,
> > > > > > > Matt
|
|
|