Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-08-2013, 12:44 PM   #1
yonastecle
Newly Registered User
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
yonastecle is on a distinguished road
Add button won't add info to table

hey guys i am having a lot of trouble. i created an add, delete, edit, clear, and close button. i am able to update my table but i am having trouble adding to it. here are the codes i used. i am so lost as to where the problem is. the error it gives me is runtime erro 3075:
suntac error in string in query expression 'customerId=".

Option Compare Database

Private Sub CmdAdd_Click()
'when we click on button add there are two options
'1 for insert
'2 for update
If Me.txtid.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO customer(customerid, custfirstname, custlastname, custphone, custdob, custgender, allergies, balance, houseId, planID)" & _
" VALUES (" & Me.txtid & ", '" & Me.txtname & "', '" & Me.txtlastname & "', '" & Me.txtphone & "','" & Me.txtdob & "', '" & Me.txtgender & "','" & Me.txtallergy & "', " & Me.txtbalance & ", '" & Me.txthouseid & "', '" & Me.txtplanid & "')"


Else
'otherwise (tag of txtid store the id of customer to be modified)
CurrentDb.Execute "UPDATE customer " & _
" SET customerid=" & Me.txtid & _
", custfirstname='" & Me.txtname & "'" & _
", custlastname='" & Me.txtlastname & "'" & _
", custphone='" & Me.txtphone & "'" & _
", custdob='" & Me.txtdob & "'" & _
", custgender='" & Me.txtgender & "'" & _
", allergies='" & Me.txtallergy & "'" & _
", balance=" & Me.txtbalance & _
", HouseID='" & Me.txthouseid & "'" & _
", planID='" & Me.txtplanid & "'" & _
" WHERE customerId=" & Me.txtid.Tag
End If




'clear form
CmdClear_Click
'refresh datat in list form
formcustomersub.Form.Requery
End Sub


Private Sub CmdClear_Click()
Me.txtid = ""
Me.txtname = ""
Me.txtlastname = ""
Me.txtphone = ""
Me.txtdob = ""
Me.txtallergy = ""
Me.txtbalance = ""
Me.txthouseid = ""
Me.txtplanid = ""
Me.txtgender = ""

'focus on Id Text box
Me.txtid.SetFocus
'set button edit to enable
Me.CmdEdit.Enabled = True
'change option of button add to Add
Me.CmdAdd.Caption = "Add"
'clear tag on txtid for reset new
Me.txtid.Tag = "'"





End Sub

Private Sub CmdClose_Click()
DoCmd.Close
End Sub

Private Sub CmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.formcustomersub.Form.Recordset.EOF And Me.formcustomersub.Form.Recordset.BOF) Then
'confirm Delete
If MsgBox("Are you sure to delete?", vbYesNo) = vbYes Then
'delete now
CurrentDb.Execute "DELETE FROM customer" & _
" WHERE customerid=" & Me.formcustomersub.Form.Recordset.Fields("customer id")
'refresh data in list
Me.formcustomersub.Requery
End If
End If

End Sub

Private Sub CmdEdit_Click()
'check whether there exists data in list
If Not (Me.formcustomersub.Form.Recordset.EOF And Me.formcustomersub.Form.Recordset.BOF) Then
'get data to text box control
With Me.formcustomersub.Form.Recordset
Me.txtid = .Fields("customerID")
Me.txtname = .Fields("CustFirstName")
Me.txtlastname = .Fields("CustLastName")
Me.txtphone = .Fields("CustPhone")
Me.txtdob = .Fields("CustDob")
Me.txtgender = .Fields("CustGender")
Me.txtallergy = .Fields("Allergies")
Me.txtbalance = .Fields("Balance")
Me.txthouseid = .Fields("HouseId")
Me.txtplanid = .Fields("PlanID")
'store id of customer in Tag of txtid in case id is modified
Me.txtid.Tag = .Fields("customerid")
'change caption of button add to update
Me.CmdAdd.Caption = "Update"
'disable button edit
Me.CmdEdit.Enabled = False
End With
End If
End Sub

yonastecle is offline   Reply With Quote
Old 04-08-2013, 01:14 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,571
Thanks: 57
Thanked 1,873 Times in 1,823 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Add button won't add info to table

I don't see anything obvious with your code re the 3075 which seems to be a generic error based on a quick google search.

My suggestions:

- put your SQL into variables, then do a debug.print to see what's actually being rendered. If you do this before executing the Action queries, you will have eliminated the syntax errors.
- use Option Explicit
- put an error routine in every procedure
- for debugging techniques see http://www.cpearson.com/excel/debug.htm
- Get a copy of the free Utility MZTools for VBA -- very helpful for documentation, error handler, code checking...

Why are you using the Tag? If you are Updating an existing record, and that record has an autonumber id, you don't have to mention the autonumber field (also true with an Insert into).

Good luck

Last edited by jdraw; 04-08-2013 at 01:17 PM. Reason: spelling
jdraw is offline   Reply With Quote
Reply

Tags
access , add , delete , forms , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
button on report to change info displayed jsehring3 Reports 4 06-19-2012 03:01 PM
changing table info with a button Rudeman76 Forms 2 06-11-2007 05:57 PM
Pull info from table to display basic project info comptechbranden General 0 06-08-2006 04:38 AM
transferring info at the click of a button! capn beanfart General 3 08-13-2003 04:08 AM
lookup info in 1 table gives info out of second, but how vice versa ? lesaint Tables 4 04-04-2003 11:27 PM




All times are GMT -8. The time now is 06:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World