Home > Archive > Visual Basic > March 2004 > Need Tip: Storing unique data in recordset
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need Tip: Storing unique data in recordset
|
|
| Faraz Azhar 2004-03-30, 1:30 pm |
| Hello,
I have a database (Access) and a table and a field in it which is a
Primary Key. Field's name is 'Location'. it is a string field. Im
using ADO 2.7. ok...
My program allows to add and edit records in the db. ok .. first of
all, ive dont have as such any problems in doing the following but i
need tip creating efficient coding.
Ok when the user adds a record, I use the Find method of the recordset
to search if that location name (which is a primary key) already
exists or not. I do the following:
If .RecordCount > 0 Then
.MoveFirst
.Find "Location = '" & Replace(txtLocation, "'", "''") & "'"
If Not .EOF Then
MsgBox "exists.", vbInformation
Exit Sub
End If
End If
The above code works fine. If the any record by txtLocation name
doesnt exists, it adds a new record and saves the recordset. Now.. in
case of Editing a record; I use the same coding above to search if the
Location name already exists or not. If the location name exists it
exits the sub. ok.. but if it doesnt, it brings the recordset to the
end (ie EOF). The trouble im having is that the record that is to be
edited is already selected. So when i do the above search, it brings
the recordset to EOF. And when i do this to edit:
RS("Location") = txtLocation
... it gives error because the record position has been changed. The
work-around i use for this situation is really pathetic :) so i was
wondering what would you guys suggest to make this thing work
efficiently?
PS. I hope you read all that and understood it :) i know ppl dont read
long posts.
- Faraz Azhar
| |
|
| What about :
If IsLocationThere("Mikes Place") = True Then
'Update the record
Conn.Execute "Update MyTable Set .... " _
& " Where Location = '" & Replace
(txtLocation, "'", "''") & "'"
Else
'Insert Record
Conn.Execute "Insert Into MyTable.....)
End If
Private Function IsLocationThere(strMyLoc As String) As
Boolean
rst.Open "Select Location From MyTable " _
& " Where Location = '" & Replace
(txtLocation, "'", "''") & "'", Conn
If Rst.RecordCount > 0 Then
IsLocationThere = True
Else
IsLocationThere = False
End If
rst.Close
End Function
HTH
--- Andy
>-----Original Message-----
>Hello,
>
>I have a database (Access) and a table and a field in it
which is a
>Primary Key. Field's name is 'Location'. it is a string
field. Im
>using ADO 2.7. ok...
>
>My program allows to add and edit records in the db.
ok .. first of
>all, ive dont have as such any problems in doing the
following but i
>need tip creating efficient coding.
>
>Ok when the user adds a record, I use the Find method of
the recordset
>to search if that location name (which is a primary key)
already
>exists or not. I do the following:
>
>If .RecordCount > 0 Then
> .MoveFirst
> .Find "Location = '" & Replace(txtLocation, "'", "''")
& "'"
> If Not .EOF Then
> MsgBox "exists.", vbInformation
> Exit Sub
> End If
>End If
>
>The above code works fine. If the any record by
txtLocation name
>doesnt exists, it adds a new record and saves the
recordset. Now.. in
>case of Editing a record; I use the same coding above to
search if the
>Location name already exists or not. If the location name
exists it
>exits the sub. ok.. but if it doesnt, it brings the
recordset to the
>end (ie EOF). The trouble im having is that the record
that is to be
>edited is already selected. So when i do the above
search, it brings
>the recordset to EOF. And when i do this to edit:
> RS("Location") = txtLocation
>... it gives error because the record position has been
changed. The
>work-around i use for this situation is really
pathetic :) so i was
>wondering what would you guys suggest to make this thing
work
>efficiently?
>
>PS. I hope you read all that and understood it :) i know
ppl dont read
>long posts.
>
>- Faraz Azhar
>.
>
| |
|
| Ooops, it should say:
Private Function IsLocationThere(strMyLoc As String) As
Boolean
rst.Open "Select Location From MyTable " _
& " Where Location = '" & Replace
(strMyLoc , "'", "''") & "'", Conn
| |
| Faraz Azhar 2004-03-31, 12:30 am |
| ok.. that would be a good idea... see my coding got complexed because
i used one recordset. i think two recordsets oughta do it: one to
add/edit data and one to search if the primary key exists.
- Faraz Azhar
|
|
|
|
|