yonastecle
New member
- Local time
- Today, 15:30
- Joined
- Apr 8, 2013
- Messages
- 1
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("customerid")
'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
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("customerid")
'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