New Record SQL Linked table Error

[CP1234]

I have spent the last three hours reading countless posts, but have found
nothing that address the issue at hand.

Trying accomplish adding a record to a linked table. See structure and code
below. At times the code works, and sometimes it gives me the following
error. Any help would be greatly appreciated.

Operating System: Windows XP Pro
Office Suite: Office XP Pro
Access DB: Access 2002
Basic Structure: Forms, queries, reports in database file (.mdb). Tables
in SQL Backend using SQL client.

Error: 3146 - ODBC Call Failed
More: [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement
conflicted with COLUMN FOREIGN KEY constraint 'tblActivity_FK00'. The
conflict occurred in database 'DatabaseSQL1', table 'tblMLS', column
'MLSListNumber'.

Code Failing at: rst.Update

Code:

Private Sub cmdLaunchSystem_Click()

Dim dteOMD As Date
Dim strMLSNum, strCriteria As String
Dim intCount As Integer 'Used as a count to count the number of activities
entered
Dim intDays As Integer 'Defines the number of days inbetween activities
Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Defines the criteria for the DCount function below
strCriteria = "[TAXPropertyIdentificationNumber] = '" &
Me.MLSPropertyIDNumber & _
"' And [ClientOneFN] is null"

'Error trap checking for properties being launched without filling out the
name columns
If Nz(DCount("*", "tblTax", strCriteria), 0) > 0 Then
MsgBox "Please click the MLS number button and enter the name of the
client into the appropriate fields."
Exit Sub
End If

'Defines the criteria for the DCount function below
strCriteria = "[MLSListNumber] = " & Me.MLSListNumber & " And
[SystemStep] = 1" & _
" And [SystemNumber] = 1"

'Error trap checking for activities with the same MLS number that has a
system step and
'number = 1 which would mean the system was previously launched
If Nz(DCount("*", "tblActivity", strCriteria), 0) > 0 Then
MsgBox "You can't launch the system more than once."
Exit Sub
End If

'Creates the recordset to insert the new activity into
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblActivity", dbOpenDynaset, dbSeeChanges)
strMLSNum = Me.MLSListNumber

'This function checks to see if the Off Market Date is before today (the day
the activities are being entered). If the OMD is before today, the system
will use today's date rather than the Off Market Date to create a starting
point for the system.
If Me.MLSOffMarketDate >= Date Then
dteOMD = Me.MLSOffMarketDate
Else
dteOMD = Date
End If

'The dteCorrect function makes sure that the date being entered lands on a
weekday. For dates that land on a saturday, the code will move the date to
Friday and for dates on sunday, the code will move the date to Monday.
dteOMD = dteCorrect(dteOMD)

'Add First Step of Program
rst.AddNew
rst.Fields("DateStart").Value = dteOMD
rst.Fields("DateDue").Value = dteOMD
rst.Fields("DateEntered").Value = Now
rst.Fields("MLSListNumber").Value = strMLSNum
rst.Fields("ActivityNote").Value = "Step One."
rst.Fields("AgentAssign").Value = 3
rst.Fields("ActivityType").Value = "Property Visit"
rst.Fields("SystemNumber").Value = 1
rst.Fields("SystemStep").Value = 1
rst.Fields("StartTime").Value = "05:00 PM"
intCount = intCount + 1
rst.Update

Exit_Here:

'Exiting the code and releasing these objects
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

DoCmd.Requery

MsgBox intCount & " Activities created.", vbOKOnly

End Sub

 

[Alex - IntraLAN]

Hi,

your error is stating that the field MLSListNumber in the tblActivity
is using a value that is not tblMLS table because of this the contraint
will fail. To test this make sure you have a record in the tblMLS table
with the MLSLisNumber set to a value and hard code it on the line

rst.Fields("MLSListNumber").Va­lue = strMLSNum
to
rst.Fields("MLSListNumber").Va­lue = 1



make sure you have

Option Explicit

at the very top of your code this will make sure that your variable
references are spelt correctly as sometimes that can be the fault.


The other thing to do to debug this is

just above the

rst.Fields("MLSListNumber").Va­lue = strMLSNum

line

do

msgbox strMLSNum

to check the value before writting to the record.

hope it helps

regards

Alex