open a table to update or add record

I want to open table and check to see if PDN exsists in that table
If PDN is in the table i would like to update that Record with some
txt boxes

If PDN is not in the table i want it to insert a New record

It sounds simple but i think i am doind something wrong

Here is the code... What is wrong with it?

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList WHERE
[num] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst, PrincLast,
pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '" &
Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (qstr)
End If


Thanks

what happens when you run this code?

" wrote:

I want to open table and check to see if PDN exsists in that table
If PDN is in the table i would like to update that Record with some
txt boxes

If PDN is not in the table i want it to insert a New record

It sounds simple but i think i am doind something wrong

Here is the code... What is wrong with it?

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList WHERE
[num] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst, PrincLast,
pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '" &
Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (qstr)
End If


Thanks

I changed the code to this:


Private Sub btnSaveChanges_Click()

Dim qstr As String
Dim db As Database
Dim searchcrit As Long
Dim rstedit As Recordset
Dim QrySQL As String

On Error GoTo error_handler
Set db = CurrentDb
searchcrit = Me.txtUserInput

Set rstedit = db.OpenRecordset("SELECT * FROM tblAlternateDealerInfo
WHERE [num] = " & searchcrit)

If rstedit.EOF Then
qstr = "update [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst, PrincLast,
pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '" &
Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (qstr)
Else
With rstedit
.Edit
!BC = Me.txtBC
!PDN = Me.txtPDN
!WSN = Me.txtWSN
!Name = Me.txtName
!Address = Me.txtAddress
!City = Me.txtCity
!State = Me.txtState
!Zip = Me.txtZIP
!Phone = Me.txtPhone
!Fax = Me.txtFax
!PrincSal = Me.TxtPrincSal
!PrincFirst = Me.TxtPrincFirst
!PrincLast = Me.TxtPrincLast
!PMsection = Me.txtPM
!PMname = Me.txtPMName
!PMnumber = Me.txtPMNumber
.Update
End With
End If

rstedit.Close
Set rstedit = Nothing
Exit Sub
error_handler:
MsgBox "Error " & Err.Number & "-" & Err.Description
Exit Sub
Resume 'debug purpose
End Sub


And i still get an error TOO FEW PARAMETERS expected one

I am trying everything
mscertified wrote:
what happens when you run this code?

" wrote:

I want to open table and check to see if PDN exsists in that table
If PDN is in the table i would like to update that Record with some
txt boxes

If PDN is not in the table i want it to insert a New record

It sounds simple but i think i am doind something wrong

Here is the code... What is wrong with it?

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList WHERE
[num] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst, PrincLast,
pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '" &
Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (qstr)
End If


Thanks

What's wrong....
With building and using the qrySQL string
-- You never execute qrySQL
-- You are missing the quote marks at the beginning and ending of every line
-- You have no continuation srings at the end of all the lines
-- You have no text delimeters for when you are assigning text values to the
fields

And you have not posted the entire procedure since we don't see where db is
Set and searchcrit is not declared to name just a bit of the problem

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList WHERE
[num] = " & searchcrit)

For example, assuming that BC and WSN are text fields and PDN is a number
field, you should be building the string as

QrySQL = "UPDATE [tblAlternateDealerInfo] " & _
" Set tblAlternateDealerInfo.BC = " & Chr(34) & Me.txtBC & Chr(34)
& _
", tblAlternateDealerInfo.PDN = " & Me.TxtPDN &
", tblAlternateDealerInfo.WSN = " & Chr(34) & Me.TxtWSN & Chr(34) &
_
... & _
" WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn

db.execute QrySQL, dbFailonerror 'Execute the query


Else
...
End If


wrote in message
ups.com...
I want to open table and check to see if PDN exsists in that table
If PDN is in the table i would like to update that Record with some
txt boxes

If PDN is not in the table i want it to insert a New record

It sounds simple but i think i am doind something wrong

Here is the code... What is wrong with it?

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList WHERE
[num] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst, PrincLast,
pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '" &
Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (qstr)
End If


Thanks

OK here is my entire statement
The only fields that are not TEXT are BC, PDN and WSN and they are all
double

I am getting syntax error
I keep changing things
What is the best way to do it??
Thanks

Private Sub btnSaveChanges_Click()

Dim qstr As String
Dim db As Database
Dim searchcrit As Long
Dim rstedit As Recordset
Dim QrySQL As String

On Error GoTo error_handler
Set db = CurrentDb
searchcrit = Me.txtUserInput

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList
WHERE[PDN] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst,
PrincLast,pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '"
& Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (QrySQL)
End If


rstedit.Close
Set rstedit = Nothing
Exit Sub
error_handler:
MsgBox "Error " & Err.Number & "-" & Err.Description
Exit Sub
Resume 'debug purpose
End Sub

First you could have implemented the changes that were already suggested and
tried the code to see if it works.

Private Sub btnSaveChanges_Click()

Dim db As Database
Dim QrySQL As String

On Error GoTo error_handler
Set db = CurrentDb

IF DCount("*","tblDocumentList","PDN=" & me.txtUserInput) = 1 then

QrySQL = "UPDATE [tblAlternateDealerInfo] " & _
" Set tblAlternateDealerInfo.BC = " & Me.txtBC & _
", tblAlternateDealerInfo.PDN = " & Me.TxtPDN & _
", tblAlternateDealerInfo.WSN = " & Me.TxtWSN & _
", tblAlternateDealerInfo.Name = " & Chr(34) & Me.TxtName &
Chr(34) & _
", tblAlternateDealerInfo.Address = " & Chr(34) & Me.TxtAddress &
Chr(34) & _
", tblAlternateDealerInfo.City = " & Chr(34) & Me.TxtCity &
Chr(34) & _
", tblAlternateDealerInfo.State = " & Chr(34) & Me.TxtState &
Chr(34) & _
", tblAlternateDealerInfo.Zip = " & Chr(34) & Me.TxtZip & Chr(34)
& _
", tblAlternateDealerInfo.Phone = " & Chr(34) & Me.TxtPhone &
Chr(34) & _
", tblAlternateDealerInfo.Fax = " & & Chr(34) Me.TxtFax & Chr(34)
& _
", tblAlternateDealerInfo.PrincSal = " & Chr(34) & Me.TxtPrincSal
& Chr(34) & _
", tblAlternateDealerInfo.PrincFirst = " & Chr(34) &
Me.TxtPrincFirst & Chr(34) & _
", tblAlternateDealerInfo.PrincLast = " & Chr(34) &
Me.TxtPrincLast & Chr(34) & _
", tblAlternateDealerInfo.PMsection = " & Chr(34) & Me.TxtPM &
Chr(34) & _
", tblAlternateDealerInfo.PMname = " & Chr(34) & Me.TxtPmname &
Chr(34) & _
", tblAlternateDealerInfo.PMnumber = " & Chr(34) & Me.TxtPMnumber
& Chr(34) & _
" WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn

DB.Execute QrySQL, dbFailOnError

Else
'Remove the apostrophes around the values to be entered into the number
fields.
QrySQL = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst,
PrincLast,pmsection, pmname, PMnumber) VALUES ( " & Me.txtBC & ", "
& Me.txtPDN & ", " & Me.txtWSN & ", '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DB.Execute QrySQL, dbFailOnError
End If


Exit Sub
error_handler:
MsgBox "Error " & Err.Number & "-" & Err.Description

Resume 'debug purpose
End Sub

wrote in message
ups.com...
OK here is my entire statement
The only fields that are not TEXT are BC, PDN and WSN and they are all
double

I am getting syntax error
I keep changing things
What is the best way to do it??
Thanks

Private Sub btnSaveChanges_Click()

Dim qstr As String
Dim db As Database
Dim searchcrit As Long
Dim rstedit As Recordset
Dim QrySQL As String

On Error GoTo error_handler
Set db = CurrentDb
searchcrit = Me.txtUserInput

Set rstedit = db.OpenRecordset("SELECT * FROM tblDocumentList
WHERE[PDN] = " & searchcrit)
If rstedit.RecordCount = 1 Then
QrySQL = "UPDATE [tblAlternateDealerInfo] "
Set tblAlternateDealerInfo.BC = " & Me.txtBC & ",
tblAlternateDealerInfo.PDN = " & Me.TxtPDN & ",
tblAlternateDealerInfo.WSN = " & Me.TxtWSN & ",
tblAlternateDealerInfo.Name = " & Me.TxtNAme & ",
tblAlternateDealerInfo.Address = " & Me.TxtAddress & ",
tblAlternateDealerInfo.City = " & Me.TxtCity & ",
tblAlternateDealerInfo.State = " & Me.TxtState & ",
tblAlternateDealerInfo.Zip = " & Me.TxtZip & ",
tblAlternateDealerInfo.Phone = " & Me.TxtPhone & ",
tblAlternateDealerInfo.Fax = " & Me.TxtFax & ",
tblAlternateDealerInfo.PrincSal = " & Me.TxtPrincSal & ",
tblAlternateDealerInfo.PrincFirst = " & Me.TxtPrincFirst & ",
tblAlternateDealerInfo.PrincLast = " & Me.TxtPrincLast & ",
tblAlternateDealerInfo.PMsection = " & Me.TxtPM & ",
tblAlternateDealerInfo.PMname = " & Me.TxtPmname & ",
tblAlternateDealerInfo.PMnumber = " & Me.TxtPMnumber & ",
WHERE tblAlternateDealerInfo.PDN = " & Me.txtpdn.Value & ";
Else
qstr = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst,
PrincLast,pmsection, pmname, PMnumber) values ( '" & Me.txtBC & "', '"
& Me.txtPDN & "', '" & Me.txtWSN & "', '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"
DoCmd.RunSQL (QrySQL)
End If


rstedit.Close
Set rstedit = Nothing
Exit Sub
error_handler:
MsgBox "Error " & Err.Number & "-" & Err.Description
Exit Sub
Resume 'debug purpose
End Sub

Hello,

I did what you suggested but now it comes up with VARIABLE NOT DEFINED

Goes to the debugger :
With tblAlternateDealerInfo.BC
highlighted

Did you fix the wrapping in this section?

QrySQL = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst,
PrincLast,pmsection, pmname, PMnumber) VALUES ( " & Me.txtBC & ", "
& Me.txtPDN & ", " & Me.txtWSN & ", '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"


Try replacing that with the following.
QrySQL = "INSERT INTO [tblAlternateDealerInfo] " & _
"(BC, PDN, WSN, Name,Address, City, State, Zip" & _
", Phone, Fax, PrincSal, PrincFirst, PrincLast, pmsection" & _
", pmname, PMnumber) " & _
" VALUES ( " & Me.txtBC & ", " & Me.txtPDN & _
", " & Me.txtWSN & ", '" & Me.txtName & _
"', '" & Me.txtAddress & "', '" & Me.txtCity & _
"', '" & Me.txtState & "', '" & Me.txtZIP & _
"', '" & Me.txtPhone & "', '" & Me.txtFax & _
"', '" & Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & _
"', '" & Me.TxtPrincLast & "', '" & Me.txtPM & _
"', '" & Me.txtPMName & "','" & Me.txtPMNumber & "')"



wrote in message
ups.com...

Hello,

I did what you suggested but now it comes up with VARIABLE NOT DEFINED

Goes to the debugger :
With tblAlternateDealerInfo.BC
highlighted

The INSERT INTO is working just fine it will insert a new record... The
problem
is that it will not update the record. Should i open a recordset first
to see if the record exists?

I need it to update the old record if one exists.

Thanks for all your help


John Spencer wrote:
Did you fix the wrapping in this section?

QrySQL = "insert into [tblAlternateDealerInfo] (BC, PDN, WSN, Name,
Address, City, State, Zip, Phone, Fax, PrincSal, PrincFirst,
PrincLast,pmsection, pmname, PMnumber) VALUES ( " & Me.txtBC & ", "
& Me.txtPDN & ", " & Me.txtWSN & ", '" & Me.txtName & "', '" &
Me.txtAddress & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" &
Me.txtZIP & "', '" & Me.txtPhone & "', '" & Me.txtFax & "', '" &
Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & "', '" & Me.TxtPrincLast &
"', '" & Me.txtPM & "', '" & Me.txtPMName & "','" & Me.txtPMNumber &
"');"


Try replacing that with the following.
QrySQL = "INSERT INTO [tblAlternateDealerInfo] " & _
"(BC, PDN, WSN, Name,Address, City, State, Zip" & _
", Phone, Fax, PrincSal, PrincFirst, PrincLast, pmsection" & _
", pmname, PMnumber) " & _
" VALUES ( " & Me.txtBC & ", " & Me.txtPDN & _
", " & Me.txtWSN & ", '" & Me.txtName & _
"', '" & Me.txtAddress & "', '" & Me.txtCity & _
"', '" & Me.txtState & "', '" & Me.txtZIP & _
"', '" & Me.txtPhone & "', '" & Me.txtFax & _
"', '" & Me.TxtPrincSal & "', '" & Me.TxtPrincFirst & _
"', '" & Me.TxtPrincLast & "', '" & Me.txtPM & _
"', '" & Me.txtPMName & "','" & Me.txtPMNumber & "')"



wrote in message
ups.com...

Hello,

I did what you suggested but now it comes up with VARIABLE NOT DEFINED

Goes to the debugger :
With tblAlternateDealerInfo.BC
highlighted