Record search.

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

And i put this within code for what? THe forms properties for open?

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

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

 

[Guest]

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.

And i put this within code for what? THe forms properties for open?

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

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

:

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.

:

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

 

[Guest]

It is not a combo box though, it is a text box

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.

And i put this within code for what? THe forms properties for open?

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


:

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

:

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.

:

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

 

[Guest]

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.

It is not a combo box though, it is a text box

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.

And i put this within code for what? THe forms properties for open?



:

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


:

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

:

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.

:

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

 

[Guest]

I used a text box as it was the only way i could get the search to work,
using a combo didnt make any difference bar adding the down arrow. No data
was within this as the field is unbound.

I do not fully understand the code. If the table is called Acceptance and
the Textbox as Combo24 (dont know why) what would the code look like.

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.

It is not a combo box though, it is a text box

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.

:

And i put this within code for what? THe forms properties for open?



:

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


:

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

:

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.

:

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