Search VBA Code

  • Thread starter Thread starter Aya
  • Start date Start date
A

Aya

Hi. I'm using an VBA code I've found on internet about looking for a
recordset. I decided to add it to the Search form I've created. The code I've
found it in this link:

http://www.functionx.com/vbaccess/howtoado/locaterecord.htm

The function of this code was when the user enters the ID in the textbox,
then when he/she clicks the Enter or Tab button, the information appears like
title, department, supervisor, etc.

When I run the form in VIEW, I wrote the ID correctly like it was in the
Search table, but it doesn't recognize the it. It always shows the message
box, instead of finding it and show it in the form.

I wrote the code exactly like the example, but with the name of the fields of
my table. Here's my code I've entered in the ID textbox of my Search form:


Private Sub ID_LostFocus()

Dim rstImpact_Safety_Analysis As ADODB.Recordset
Dim blnFound As Boolean
' This flag will allow us to know whether the item number was found
Dim fldItem As ADODB.Field

' Since we are only starting, we assume that no item number has been
found
blnFound = False

' If there is no value in the ID textbox, don't do nothing
If Me.ID = " " Then Exit Sub

Set rstImpact_Safety_Analysis = New ADODB.Recordset
rstImpact_Safety_Analysis.Open "SELECT * FROM Impact_Safety_Analysis
WHERE ID = ' " & _
ID & " ' ", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText

With rstImpact_Safety_Analysis
' Check each record
While Not .EOF
' Check the name of the column
For Each fldItem In .Fields
' If the current column is ID
If fldItem.Name = "ID" Then
' Check its value
' If the current column holds the ID that the user
entered
If fldItem.Value = Título Then
' ... then get the record and display its values in
the controls
Me.ID = .Fields("ID")
Me.Título = .Fields("Título")
Me.Descripción = .Fields("Descripción")
Me.Fecha = .Fields("Fecha")
Me.Supervisor = .Fields("Supervisor")
Me.Departamento = .Fields("Departamento")
Me.Análisis_Por = .Fields("Análisis_Por")
Me.Adiestramiento = .Fields("Adiestramiento")
Me.Procedimientos = .Fields("Procedimientos")
Me.EPP_Recomendado = .Fields("EPP_Recomendado")
Me.Secuencia_de_Pasos = .Fields("Secuencia_de_Pasos")
Me.Riesgos = .Fields("Riesgos")
Me.Conducta_Segura = .Fields("Conducta_Segura")
Me.Secuencia_1 = .Fields("Secuencia_1")
Me.Riesgos_1 = .Fields("Riesgos_1")
Me.Conducta_Segura_1 = .Fields("Conducta_Segura_1")
Me.Secuencia_2 = .Fields("Secuencia_2")
Me.Riesgos_2 = .Fields("Riesgos_2")
Me.Conducta_Segura_2 = .Fields("Conducta_Segura_2")
Me.Secuencia_3 = .Fields("Secuencia_3")
Me.Riesgos_3 = .Fields("Riesgos_3")
Me.Conducta_Segura_3 = .Fields("Conducta_Segura_3")
Me.Secuencia_4 = .Fields("Secuencia_4")
Me.Riesgos_4 = .Fields("Riesgos_4")
Me.Conducta_Segura_4 = .Fields("Conducta_Segura_4")
Me.Secuencia_5 = .Fields("Secuencia_5")
Me.Riesgos_5 = .Fields("Riesgos_5")
Me.Conducta_Segura_5 = .Fields("Conducta_Segura_5")
Me.Secuencia_6 = .Fields("Secuencia_6")
Me.Riesgos_6 = .Fields("Riesgos_6")
Me.Conducta_Segura_6 = .Fields("Conducta_Segura_6")
Me.Secuencia_7 = .Fields("Secuencia_7")
Me.Riesgos_7 = .Fields("Riesgos_7")
Me.Conducta_Segura_7 = .Fields("Conducta_Segura_7")
Me.Secuencia_8 = .Fields("Secuencia_8")
Me.Riesgos_8 = .Fields("Riesgos_8")
Me.Conducta_Segura_8 = .Fields("Conducta_Segura_8")
' Set the found flag to true (we will use it later)
blnFound = True
End If
End If
Next
' In case you didn't find it, move to the next record
.MoveNext
Wend
End With

' If the ID was not found, ...
If blnFound = False Then
' ... let the user know, ...
MsgBox "The task you are looking for is not in our system."
' ... and reset the form
cmdReset_Click
End If

rstImpact_Safety_Analysis.Close
Set rstImpact_Safety_Analysis = Nothing

End Sub


Can anyone help me with this code??? I'm hoping to finish it this week before
I left for next week. If anyone have any other Search codes I can use, you're
always welcome to reply me back and I'll give it a try.
 
Aya said:
rstImpact_Safety_Analysis.Open _
"SELECT * "
"FROM Impact_Safety_Analysis " & _
"WHERE ID = ' " & ID & " ' ", _
CurrentProject.Connection, _
adOpenStatic, _
adLockReadOnly, _
adCmdText
Click to expand...


You have quote marks around the ID number -- this is fine if you are
looking for a text value, but if it's a numeric key you should not
delimit it at all. Try something like this:


..... "WHERE ID = " & me.controls("ID").Value & ""


Hope that helps


Tim F
 
The ID field I've created is a mix of text and number. All of them are
written like this: ISA-00*. ISA comes from Impact Safety Analysis and added
the numbers for easy accessibility.

Anyway I did try the code you wrote and two errors appears. One of them says
the following:


"Run-time error '-2147217900 (80040e14)':

Syntax error in string in query expression 'ID ='ISA-001'."


It was located in this part of the code:

rstImpact_Safety_Analysis.Open "SELECT * FROM Impact_Safety_Analysis
WHERE ID = ' " & _
Me.Controls("ID").Value & "", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText

Another error in this procedure says this:

"The changes you requested to the table were not successful because they
would duplicate values in the index, primary key, or relationship. Change the
data in the field or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again."

I have the ID as primary key and with no duplicates in it.
 
The ID field I've created is a mix of text and number.
Click to expand...

Okay, ignore the first guess. Quotes are required around a text value.
Syntax error in string in query expression 'ID ='ISA-001'."


It was located in this part of the code:

rstImpact_Safety_Analysis.Open _
"SELECT * FROM Impact_Safety_Analysis " & _
"WHERE ID = '" & Me.Controls("ID").Value & "", _
Click to expand...

There is an unbalanced quote mark here: you've forgotten to put the last
one back in:

"WHERE ID = '" & Me.Controls("ID").Value & "'", _

The other thing I have just noticed in your original post is that there
are extra spaces all over the place:
"WHERE ID = ' " ID & " ' ", _
Click to expand...

will end up like
WHERE ID = ' ISA-001 '

which is not what you want, presumably. For this reason it's really a
good idea to (a) build any sql string in a variable and then (b) display
it either in a msgbox or the debug window, so that you can see what you
are actually passing on to the db engine. In this case it would have been
obvious immediately.
Another error in this procedure says this:

"The changes you requested to the table were not successful because
they would duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again."
Click to expand...

This cannot be the result of a select query: you must be running an
insert or update to get this. It's coming from another part of your code.

Hope that helps


Tim F
 
In case, here's the whole codes that uses my Search/Edit form. I still got
the error from the ID_LostFocus code in the same place I've previously posted.
I'm still stocked with it, trying to figure out how to make it work properly.
Any comments and/or suggestions, please reply me back. Thanks.

Option Compare Database

Private Sub cmdClose_Click()

DoCmd.Close acForm, "Edit Impact Safety Analysis"
DoCmd.OpenForm "Menú Principal", acNormal, "", "", , acNormal

End Sub


Private Sub cmdReset_Click()

Me.ID = " "
Me.Título = " "
Me.Descripción = " "
Me.Fecha = Date
Me.Supervisor = " "
Me.Departamento = " "
Me.Análisis_Por = " "
Me.Adiestramiento = " "
Me.Procedimientos = " "
Me.EPP_Recomendado = " "
Me.Secuencia_de_Pasos = " "
Me.Riesgos = " "
Me.Conducta_Segura = " "
Me.Secuencia_1 = " "
Me.Riesgos_1 = " "
Me.Conducta_Segura_1 = " "
Me.Secuencia_2 = " "
Me.Riesgos_2 = " "
Me.Conducta_Segura_2 = " "
Me.Secuencia_3 = " "
Me.Riesgos_3 = " "
Me.Conducta_Segura_3 = " "
Me.Secuencia_4 = " "
Me.Riesgos_4 = " "
Me.Conducta_Segura_4 = " "
Me.Secuencia_5 = " "
Me.Riesgos_5 = " "
Me.Conducta_Segura_5 = " "
Me.Secuencia_6 = " "
Me.Riesgos_6 = " "
Me.Conducta_Segura_6 = " "
Me.Secuencia_7 = " "
Me.Riesgos_7 = " "
Me.Conducta_Segura_7 = " "
Me.Secuencia_8 = " "
Me.Riesgos_8 = " "
Me.Conducta_Segura_8 = " "
Me.ID.SetFocus

End Sub


Private Sub cmdSave_Click()

DoCmd.Save acForm, "Edit Impact Safety Analysis"
Beep
MsgBox "Your changes were saved in the system.", vbInformation, "Safety
IMPACT"

End Sub


Private Sub Form_Load()

DoCmd.Maximize

End Sub


Private Sub ID_LostFocus()

Dim rstImpact_Safety_Analysis As ADODB.Recordset
Dim blnFound As Boolean
' This flag will allow us to know whether the ID was found
Dim fldItem As ADODB.Field

' Since we are only starting, we assume that no ID has been found
blnFound = False

' If there is no value in the ID text box, don't do nothing
If Me.ID = " " Then Exit Sub

Set rstImpact_Safety_Analysis = New ADODB.Recordset
rstImpact_Safety_Analysis.Open "SELECT * FROM Impact_Safety_Analysis
WHERE ID = '" & _
Me.Controls("ID").Value & "'", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText

With rstImpact_Safety_Analysis
' Check each record
While Not .EOF
' Check the name of the column
For Each fldItem In .Fields
' If the current column is ID
If fldItem.Name = "ID" Then
' Check its value
' If the current column holds the ID that the user
entered
If fldItem.Value = ID Then
' ... then get the record and display its values in
the controls
Me.ID = .Fields("ID")
Me.Título = .Fields("Título")
Me.Descripción = .Fields("Descripción")
Me.Fecha = .Fields("Fecha")
Me.Supervisor = .Fields("Supervisor")
Me.Departamento = .Fields("Departamento")
Me.Análisis_Por = .Fields("Análisis_Por")
Me.Adiestramiento = .Fields("Adiestramiento")
Me.Procedimientos = .Fields("Procedimientos")
Me.EPP_Recomendado = .Fields("EPP_Recomendado")
Me.Secuencia_de_Pasos = .Fields("Secuencia_de_Pasos")
Me.Riesgos = .Fields("Riesgos")
Me.Conducta_Segura = .Fields("Conducta_Segura")
Me.Secuencia_1 = .Fields("Secuencia_1")
Me.Riesgos_1 = .Fields("Riesgos_1")
Me.Conducta_Segura_1 = .Fields("Conducta_Segura_1")
Me.Secuencia_2 = .Fields("Secuencia_2")
Me.Riesgos_2 = .Fields("Riesgos_2")
Me.Conducta_Segura_2 = .Fields("Conducta_Segura_2")
Me.Secuencia_3 = .Fields("Secuencia_3")
Me.Riesgos_3 = .Fields("Riesgos_3")
Me.Conducta_Segura_3 = .Fields("Conducta_Segura_3")
Me.Secuencia_4 = .Fields("Secuencia_4")
Me.Riesgos_4 = .Fields("Riesgos_4")
Me.Conducta_Segura_4 = .Fields("Conducta_Segura_4")
Me.Secuencia_5 = .Fields("Secuencia_5")
Me.Riesgos_5 = .Fields("Riesgos_5")
Me.Conducta_Segura_5 = .Fields("Conducta_Segura_5")
Me.Secuencia_6 = .Fields("Secuencia_6")
Me.Riesgos_6 = .Fields("Riesgos_6")
Me.Conducta_Segura_6 = .Fields("Conducta_Segura_6")
Me.Secuencia_7 = .Fields("Secuencia_7")
Me.Riesgos_7 = .Fields("Riesgos_7")
Me.Conducta_Segura_7 = .Fields("Conducta_Segura_7")
Me.Secuencia_8 = .Fields("Secuencia_8")
Me.Riesgos_8 = .Fields("Riesgos_8")
Me.Conducta_Segura_8 = .Fields("Conducta_Segura_8")
' Set the found flag to true (we will use it later)
blnFound = True
End If
End If
Next
' In case you didn't find it, move to the next record
.MoveNext
Wend
End With

' If the item number was not found, ...
If blnFound = False Then
' ... let the user know, ...
MsgBox "The ID wasn't found in the system."
' ... and reset the form
cmdReset_Click
End If

rstImpact_Safety_Analysis.Close
Set rstImpact_Safety_Analysis = Nothing

End Sub
 
In case, here's the whole codes that uses my Search/Edit form. I still
got the error from the ID_LostFocus code in the same place I've
previously posted. I'm still stocked with it, trying to figure out how
to make it work properly. Any comments and/or suggestions, please
reply me back. Thanks.
Click to expand...

I just have to say that I don't understand what is going on.

First thing: you cannot get a duplicate key error by running a select
query. It's coming from somewhere else.

Second thing: setting the text boxes to a single space " " does not seem
to be a very sensible thing to do. If you want to clear them, set them to
Null, or at least an empty string "".

Third thing: I don't see the point of running nearly 6K of code just to
duplicate what Access does anyway. If you make this a bound form, then
the problem goes away.

Best wishes


Tim F
 
I've changed the textboxes settings to NULL, but when I write the ID and
press the Tab button in my keyboard, it doesn't show the data that I'm
looking for.

With the cmdReset button, it clears the values of the form. When I write the
ID, it only shows the ID that I wrote. I'm just thinking that the cmdReset
button may have erased my first record that shows on screen when the form
opens and was replaced by an empty one with only the ID name I wrote.

I went to the table to check on it, and I was right, the first record was
totally erased and replaced by the empty one.

How can I find the record I write and not erase the record that shows when
the form opens? This code is making me go crazy. I wonder is there another
example I can use for my form. Anyway, any comments or suggestions, please
reply me.
 
I went to the table to check on it, and I was right, the first record
was totally erased and replaced by the empty one.
Click to expand...

I suddenly have a very bad feeling... are you using a bound form or an
unbound form? If you are doing all this writing to real database fields,
then you are going to get in a pickle.

If this is a bound form (and even if it isn't) I suggest removing all this
code and just let Access do the work for you. Read up on DoCmd.GoToRecord
action, which will do everything you want.

Hope that helps


Tim F
 
Back
Top