ADO Recordset query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am currently using the following function in my DB to add records into a
table;

Function Add_Tapes(strContract As String, strCustomer As String, strTapeID
As String, datIDate As Date, datRDate As Date)

Dim cDB As Database
Dim rTF As Recordset
Dim strTape As String

strTape = UCase(strTapeID)

Set cDB = CurrentDb
Set rTF = cDB.OpenRecordset("tabTapes")

With rTF
.AddNew
!ContractNumber = strContract
!Customer = strCustomer
!TapeID = strTape
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close
End With

cDB.Close

End Function

I'd like to be able to change the above to amend the record if the TapeID
already exists or add a record if the TapeID does not exist. I have already
tried this using SQL based querires but as I have 20 different tapeid fields,
it just gets very complicated.

Can anyone help?
 
A few things to start off with,

1) your syntax appears to be DAO, not ADO.

2) I recommend that you specify the type of object in your DIM statements so
that there is no confusion between ADO and DAO. Access will use the first
Reference it comes to that has an object type that you specified. ADO and
DAO both have recordset objects, so you may accidentally get the wrong one.
Do clarify which you want, change your DIM statement to:

Dim cDB As DAO.Database
Dim rTF As DAO.Recordset
or
'I don't see a Database object for ADO
Dim rTF As ADODB.Recordset

3) You can close the recordset, but not the database (you didn't open it).
Instead, after you close the recordset, set both rTF and cDB = Nothing.

Set rTF = Nothing
Set cDB = Nothing

To help insure no duplicate TapeIDs, in the table design set a No Duplicates
index on this field. This won't allow you to add a duplicate TapeID. To
handle the duplicate ID, you can check to see if it exists before you try to
add your record or you can trap the error from the index when it occurs. To
use the "check first" method:

With rTF
.FindFirst "TapeID = '" & strTape & "'"
If .NoMatch Then
.AddNew
!ContractNumber = strContract
!Customer = strCustomer
!TapeID = strTape
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close
Else
.Edit
!ContractNumber = strContract
!Customer = strCustomer
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close
End If
End With

You don't need to update the TapeID field since it will still be the same
after the record is updated. Since you did a FindFirst, the recordset is at
the record you want to update. The syntax used in the FindFirst statement
assumes TapeID to be defined in the table as a Text data type field. I
assumed this due to your strTape variable that is assigning the value to
this field. If this is incorrect, let me know and I'll amend the syntax.
 
Thanks for that,

I have amended the code as follows;

Dim cDB As DAO.Database
Dim rTF As DAO.Database
Dim strTape As String
Dim msg As String
Dim strMsg As String
Dim TapeID As String


strTape = UCase(strTapeID)

Set cDB = CurrentDb
Set rTF = cDB.OpenRecordset("tabTapes")

With rTF
.FindFirst "TapeID = '" & strTape & "'"
If .NoMatch Then
.AddNew
!ContractNumber = strContract
!Customer = strCustomer
!TapeID = strTape
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close
Else
.Edit
!ContractNumber = strContract
!Customer = strCustomer
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close


Set rTF = Nothing
Set cDB = Nothing

End If

End With

End Function

I am now getting an error coming up though;

'Compile Error, Method or data member not found'

When I click on OK, it highlights .FindFirst
 
If tabTapes is a local table, then the OpenRecordset will open it as a Table
type recordset. This requires setting an index and doing a seek instead of
FindFirst. Instead, specify the type of recordset to open.

Set rTF = cDB.OpenRecordset("tabTapes", dbOpenDynaset)
 
AMHodgetts said:
Thanks for that,

I have amended the code as follows;

Dim cDB As DAO.Database
Dim rTF As DAO.Database
Dim strTape As String
Dim msg As String
Dim strMsg As String
Dim TapeID As String


strTape = UCase(strTapeID)

Set cDB = CurrentDb
Set rTF = cDB.OpenRecordset("tabTapes")

With rTF
.FindFirst "TapeID = '" & strTape & "'"
If .NoMatch Then
.AddNew
!ContractNumber = strContract
!Customer = strCustomer
!TapeID = strTape
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close
Else
.Edit
!ContractNumber = strContract
!Customer = strCustomer
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False
.Update
.Close


Set rTF = Nothing
Set cDB = Nothing

End If

End With

End Function

I am now getting an error coming up though;

'Compile Error, Method or data member not found'

When I click on OK, it highlights .FindFirst
Click to expand...

Just change this one line:
Set rTF = cDB.OpenRecordset("tabTapes")
Click to expand...

to this:

Set rTF = cDB.OpenRecordset("tabTapes", dbOpenDynaset)

A table-type recordset, which is what you get by default when you open a
recordset directly on a local table, doesn't have the FindFirst method.
But you can override that by specifying that you want a dynaset.

I think it would be a bit more efficient, actually, just to open the
recordset on a query that selects the record you're looking for, if it
exists:

Set rTF = cDB.OpenRecordset( _
"SELECT * FROM tabTapes WHERE TapeID = '" & _
strTape & "'")

With rTF

If .EOF Then
' No record for this TapeID; add one.
.AddNew
!TapeID = strTape
Else
' Got a record for this TapeID; edit it.
.Edit
End If

' Set all the other fields in the added/edited record.
!ContractNumber = strContract
!Customer = strCustomer
!IDate = datIDate
!RDate = datRDate
!stamped = False
!Returned = False

' Update the record and close the recordset
.Update
.Close

End With

Set rTF = Nothing
Set cDB = Nothing
 
Thanks,

I've tried this and i'm still getting the same error on .findfirst....do i
need to remove this?
 
Thanks,

I've tried entering the code you supplied but now I'm getting the same error
on .EOF
 
AMHodgetts said:
Thanks,

I've tried entering the code you supplied but now I'm getting the
same error on .EOF
Click to expand...

Argh, I didn't notice this:

That should be

Dim rTF As DAO.Recordset
 
Back
Top