shari

G

Guest

I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
 
G

Guest

Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub
 
G

Guest

This sorta works. I don't get any errors anymore, however, it does not bring
up the record(s) in form. It works as though it knows the data exists, but
doesn't display. When I enter data that is not in the field I get the
correct response of "No Cases match". I am stumped. Shari

Klatuu said:
Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub


Shari said:
I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
Click to expand...
Click to expand...
 
G

Guest

How does your form know what record to show? The code will work, but it may
be the form is not knowing which record to display.

To see if the code is getting what you want, go in to VB editor and put a
breakpoint on the line:
If rstWorkset.NoMatch Then
Then run the code and enter a value you know that will return a record.
Then in the immediate window type in:
? rstWorkset.RecordCount
If it is 0, then it is not finding any records. If it is > 0 then there is
a problem with the form not knowing what record(s) to display.

One other question. Is your form expecting only one record to be returned,
or could it be more than one that match?

Shari said:
This sorta works. I don't get any errors anymore, however, it does not bring
up the record(s) in form. It works as though it knows the data exists, but
doesn't display. When I enter data that is not in the field I get the
correct response of "No Cases match". I am stumped. Shari

Klatuu said:
Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub


Shari said:
I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I will have to check the form, however, if I type in the exact text the
record appears. There could be more than one record that could appear.

Klatuu said:
How does your form know what record to show? The code will work, but it may
be the form is not knowing which record to display.

To see if the code is getting what you want, go in to VB editor and put a
breakpoint on the line:
If rstWorkset.NoMatch Then
Then run the code and enter a value you know that will return a record.
Then in the immediate window type in:
? rstWorkset.RecordCount
If it is 0, then it is not finding any records. If it is > 0 then there is
a problem with the form not knowing what record(s) to display.

One other question. Is your form expecting only one record to be returned,
or could it be more than one that match?

Shari said:
This sorta works. I don't get any errors anymore, however, it does not bring
up the record(s) in form. It works as though it knows the data exists, but
doesn't display. When I enter data that is not in the field I get the
correct response of "No Cases match". I am stumped. Shari

Klatuu said:
Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub


:

I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

What if you type in something with the * in it so one or more records should
match?

Shari said:
I will have to check the form, however, if I type in the exact text the
record appears. There could be more than one record that could appear.

Klatuu said:
How does your form know what record to show? The code will work, but it may
be the form is not knowing which record to display.

To see if the code is getting what you want, go in to VB editor and put a
breakpoint on the line:
If rstWorkset.NoMatch Then
Then run the code and enter a value you know that will return a record.
Then in the immediate window type in:
? rstWorkset.RecordCount
If it is 0, then it is not finding any records. If it is > 0 then there is
a problem with the form not knowing what record(s) to display.

One other question. Is your form expecting only one record to be returned,
or could it be more than one that match?

Shari said:
This sorta works. I don't get any errors anymore, however, it does not bring
up the record(s) in form. It works as though it knows the data exists, but
doesn't display. When I enter data that is not in the field I get the
correct response of "No Cases match". I am stumped. Shari

:

Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub


:

I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tried that. Same thing.

Klatuu said:
What if you type in something with the * in it so one or more records should
match?

Shari said:
I will have to check the form, however, if I type in the exact text the
record appears. There could be more than one record that could appear.

Klatuu said:
How does your form know what record to show? The code will work, but it may
be the form is not knowing which record to display.

To see if the code is getting what you want, go in to VB editor and put a
breakpoint on the line:
If rstWorkset.NoMatch Then
Then run the code and enter a value you know that will return a record.
Then in the immediate window type in:
? rstWorkset.RecordCount
If it is 0, then it is not finding any records. If it is > 0 then there is
a problem with the form not knowing what record(s) to display.

One other question. Is your form expecting only one record to be returned,
or could it be more than one that match?

:

This sorta works. I don't get any errors anymore, however, it does not bring
up the record(s) in form. It works as though it knows the data exists, but
doesn't display. When I enter data that is not in the field I get the
correct response of "No Cases match". I am stumped. Shari

:

Note Item is a method in Access. Using it as a variable name could create
some ambiguity. This is one of the reasons to use good naming conventions.
For example, start all String variables with str, Integer variables with int.
Look in MSDN for "naming conventions". It will help you a lot. Here is my
version of your code with the correct way to do the find first:

Dim myDB As Database
Dim strCriteria As String
Dim rstWorkset As Recordset

strCriteria = "[Description] Like '*" & Me.Text1 & "*'"

Set myDB = CurrentDb
Set rstWorkset = myDB.OpenRecordset("tblDailySupport", dbOpenSnapShot)

rstWorkset.FindFirst (strCriteria)

If rstWorkset.NoMatch Then
MsgBox "No Cases match."
Else
DoCmd.OpenForm "frmTestSearc1"
End If

rstWorkset.Close
Set rstWorkset = Nothing
Set myDb = Nothing

End Sub


:

I am looking for the code to be able to enter data into a field on a form and
after update, it will search for the data in the query (any part of the
field) and open a form and show the record. Below is the code I have.
Private Sub Test1_AfterUpdate()

Dim myDB As Database
Dim criteria As String
Dim item As String
Dim response As Integer
Dim workset As Recordset

item = Me![Test1]
criteria = "[Description] = '" & item & "'"


Set myDB = CurrentDb()
Set workset = myDB.OpenRecordset("tblDailySupport", DB_OPEN_SNAPSHOT)

workset.FindFirst (criteria)

If workset.NoMatch Then
response = MsgBox("No Cases match.")

Else
DoCmd.OpenForm "frmTestSearc1"

End If

'DoCmd.FindRecord criteria


End Sub

Currently the findfirst on the workset will only bring up the record if it
is typed exactly correct. I want it to look at the whole field and match any
part of the field.
HELP
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need access 2000 code for searching any part of the field 12
Search and Find - SLOW 6
Type mismatch error. 1
problem finding the table when using OpenRecordset 1
Run-Time error '91': Object variable or With block variable not se 3
trying to add data from a form to a table 8
Including blank fields in results when filtering a report 1
Again:Recordset and update table "almost there" 1

Top