VBA control of field value match with excel value in cell

[Mads Petersen]

Hi, and thanks for previous help.

I use following code to export from excel to access. It is executed in
excel.
I have an excel spreadsheet with one sheet pr. week. this code is
therefore executed on every sheet, and is supposed to be it on comming
sheets as well. Therefore I would very much like if I could check if
the value in A98 (excel) matches a record in the access table
TimeReview, field "Uge". And if it does the export should be cancled.
Hope you can help.

Regards Mads

*************** *************** *************** ************'

Sub DAOFromExcelToA ccess()
' exports data from the active worksheet to a table in an Access
database

Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("c :\data\BPO\Time review\TimeRevi ew.mdb")
' open the database
Set rs = db.OpenRecordse t("TimeReview ", dbOpenTable)
' get all records in a table
r = 98 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Uge") = Range("A" & r).Value
.Fields("Manage r") = Range("B" & r).Value
.Fields("Medarb ejder") = Range("C" & r).Value
.Fields("MA-niv") = Range("D" & r).Value
.Fields("Totalt imer") = Range("E" & r).Value
.Fields("Overti d") = Range("F" & r).Value
.Fields("DirTim er") = Range("G" & r).Value
.Fields("Ferie" ) = Range("H" & r).Value
.Fields("Hellig dage") = Range("I" & r).Value
.Fields("Sygdom ") = Range("J" & r).Value
.Fields("Barsel ") = Range("K" & r).Value
.Fields("Skole/intern uddannelse") = Range("L" & r).Value
.Fields("Charge ability") = Range("M" & r).Value
.Fields("Effici ency") = Range("N" & r).Value
.Fields("Opspar et overtid") = Range("O" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

[Peter Hoyle]

Hi Mads

You could just use another recordset to check for existing records something
like

Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String
r As Long
Set db = OpenDatabase("c :\data\BPO\Time review\TimeRevi ew.mdb")
' open the database

'check for existing records
strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98")
Set rsCheck = db.OpenRecordse t(strSQL)
If Not rsCheck.BOF And Not rsCheck.EOF Then
MsgBox("Records already present")
rsCheck.Close
Set rsCheck = Nothing
Exit Sub
End If

rsCheck.Close
Set rsCheck = Nothing

Set rs = db.OpenRecordse t("TimeReview ", dbOpenTable)
' get all records in a table
r = 98 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
etc............ ....

Cheers,
Peter

[Mads A. Petersen]

Hi Peter

Thanks a lot it looks like what i need. But I recieve a complie error on
the second row of your code.
r As Long

Why is that. I am pretty new to VBA. SO sorry for that.
Hope you know why.

Cheers
, Newbie Mads
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mads Petersen]

"Peter Hoyle" <pe*********@nt lworld-NOT.com> wrote in message news:<q8******* *****@newsfe1-gui.ntli.net>.. .

Hi Mads

You could just use another recordset to check for existing records something
like

Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String
r As Long
Set db = OpenDatabase("c :\data\BPO\Time review\TimeRevi ew.mdb")
' open the database

'check for existing records
strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98")
Set rsCheck = db.OpenRecordse t(strSQL)
If Not rsCheck.BOF And Not rsCheck.EOF Then
MsgBox("Records already present")
rsCheck.Close
Set rsCheck = Nothing
Exit Sub
End If

rsCheck.Close
Set rsCheck = Nothing

Set rs = db.OpenRecordse t("TimeReview ", dbOpenTable)
' get all records in a table
r = 98 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
etc............ ....

Cheers,
Peter

Hi Peter

Thanks a lot it looks like what i need. But I recieve a complie error on
the second row of your code.
r As Long

Why is that. I am pretty new to VBA. SO sorry for that.
Hope you know why.

Cheers
, Newbie Mads

[Peter Hoyle]

Hi Mads.

Hopefully you've sorted this one now.

If not then the first line should have been...

Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String,
r As Long

This should be all on one line but gets split by the news reader.

Dim db As Database
Dim rs As Recordset
Dim rsCheck As Recordset
Dim strSQL As String
Dim r As Long

would be the equivalent,

Cheers,
Peter

[Mads Petersen]

Hi again.

Ya the newbie got that figured out...(but thanks any way)
But I have someproblems with this part:
strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98")
Set rsCheck = db.OpenRecordse t(strSQL)

there is a datatype mismatch. Could it be beacause of the fact that my
weeknumbers isn't numbers but text in both excel and access. And if
what to do?

Hope some helo is available.

Cheers Mads
"Peter Hoyle" <pe*********@nt lworld-NOT.com> wrote in message news:<ZA******* ******@newsfe3-gui.ntli.net>.. .

Hi Mads.

Hopefully you've sorted this one now.

If not then the first line should have been...

Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String,
r As Long

This should be all on one line but gets split by the news reader.

Dim db As Database
Dim rs As Recordset
Dim rsCheck As Recordset
Dim strSQL As String
Dim r As Long

would be the equivalent,

Cheers,
Peter