Clear Button does not work

A

accclt718

hello All
programming is not my cup of tea, but someone has do it
I have a submit job form, with has 4 command buttons,
1) submit job
2) clear job ( which clear all the text on form while its open)
3) print job
4) return main SB (also having a problem Yes or NO option when he/she
clicks return to main SB

here is the code for clear command

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub
----------------------------------------------------------------------------------------
here is what my subjob form looks like, part of it

Option Compare Database

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub

Private Sub FormExit_Click()

DoCmd.Close
DoCmd.SelectObject acForm, "Switchboard"

End Sub

Private Sub Print_Button_Click()
If DLookup("ProjectNumber", "XtecProjects", "ProjectNumber=" & Me!ProjNum) =
Me!ProjNum Then
DoCmd.PrintOut acSelection
Else
MsgBox "You Must Submit job First, Before Printing"
End If

End Sub

Private Sub ProjNum_GotFocus()
Dim X As Integer
X = DMax("ProjectNumber", "nameProjects")
Me!ProjNum = X + 1
Me!TbxDateIn = format(Now(), "mm/dd/yyyy")
End Sub

Private Sub SubJob_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [nameProjects] (ProjectNumber,
SpecialInstructions, ProjectDescription, Consultant, DateIn, FinalDue,
PrimaryFormat, ContactName, Presenter, Company, Floor, Street, CityStateZip,
ExpenseCode, ProjectCode, DepartmentCode, Telephone, email, Fax) SELECT
Forms!JobEFrm!ProjNum AS ProjectNumber, Forms!JobEFrm!TbxNotes AS
SpecialInstructions, Forms!JobEFrm!TbxDescr AS ProjectDescription,
Forms!JobEFrm!cboConsultant AS Consultant, Forms!JobEFrm!TbxDateIn AS DateIn,
Forms!JobEFrm!TbxFnlDue AS FinalDue, Forms!JobEFrm!TbxPF AS PrimaryFormat,
Forms!JobEFrm!cboCntct AS ContactName, Forms!JobEFrm!TbxPres AS Presenter,
Forms!JobEFrm!TbxComp AS Company, Forms!JobEFrm!TbxFloor AS Floor,
Forms!JobEFrm!TbxStreet AS Street, Forms!JobEFrm!TbxCityStateZip AS
CityStateZip, Forms!JobEFrm!TbxExpCo AS ExpenseCode, Forms!JobEFrm!TbxProCode
AS ProjectCode, Forms!JobEFrm!TbxDeptCo AS DepartmentCode,
Forms!JobEFrm!TbxPhone AS Telephone, Forms!JobEFrm!TbxEmail AS email,
Forms!JobEFrm!TbxFax AS Fax"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Job was Submitted"

End Sub
 
A

Allen Browne

Instead of setting all controls to a zero-length string, undo the form:
Private Sub cmdClearJob_Click()
If Me.Dirty Then
Me.Undo
End If
End Sub

Simarly, there is no need to write the record manually by editing the
recordset (which won't work anyway, as you have no .Edit and .Update). Just
bind the form to the table, and Access will write it for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

accclt718 said:
hello All
programming is not my cup of tea, but someone has do it
I have a submit job form, with has 4 command buttons,
1) submit job
2) clear job ( which clear all the text on form while its open)
3) print job
4) return main SB (also having a problem Yes or NO option when he/she
clicks return to main SB

here is the code for clear command

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub
----------------------------------------------------------------------------------------
here is what my subjob form looks like, part of it

Option Compare Database

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub

Private Sub FormExit_Click()

DoCmd.Close
DoCmd.SelectObject acForm, "Switchboard"

End Sub

Private Sub Print_Button_Click()
If DLookup("ProjectNumber", "XtecProjects", "ProjectNumber=" & Me!ProjNum)
=
Me!ProjNum Then
DoCmd.PrintOut acSelection
Else
MsgBox "You Must Submit job First, Before Printing"
End If

End Sub

Private Sub ProjNum_GotFocus()
Dim X As Integer
X = DMax("ProjectNumber", "nameProjects")
Me!ProjNum = X + 1
Me!TbxDateIn = format(Now(), "mm/dd/yyyy")
End Sub

Private Sub SubJob_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [nameProjects] (ProjectNumber,
SpecialInstructions, ProjectDescription, Consultant, DateIn, FinalDue,
PrimaryFormat, ContactName, Presenter, Company, Floor, Street,
CityStateZip,
ExpenseCode, ProjectCode, DepartmentCode, Telephone, email, Fax) SELECT
Forms!JobEFrm!ProjNum AS ProjectNumber, Forms!JobEFrm!TbxNotes AS
SpecialInstructions, Forms!JobEFrm!TbxDescr AS ProjectDescription,
Forms!JobEFrm!cboConsultant AS Consultant, Forms!JobEFrm!TbxDateIn AS
DateIn,
Forms!JobEFrm!TbxFnlDue AS FinalDue, Forms!JobEFrm!TbxPF AS PrimaryFormat,
Forms!JobEFrm!cboCntct AS ContactName, Forms!JobEFrm!TbxPres AS Presenter,
Forms!JobEFrm!TbxComp AS Company, Forms!JobEFrm!TbxFloor AS Floor,
Forms!JobEFrm!TbxStreet AS Street, Forms!JobEFrm!TbxCityStateZip AS
CityStateZip, Forms!JobEFrm!TbxExpCo AS ExpenseCode,
Forms!JobEFrm!TbxProCode
AS ProjectCode, Forms!JobEFrm!TbxDeptCo AS DepartmentCode,
Forms!JobEFrm!TbxPhone AS Telephone, Forms!JobEFrm!TbxEmail AS email,
Forms!JobEFrm!TbxFax AS Fax"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Job was Submitted"

End Sub
Click to expand...
 
A

accclt718

here is update code i think not sure, the database was done by another IT
person who left the comapny

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

thank you

accclt718 said:
Allen Browne

i tried your code, when click on it nothing happens ?
unless i'm something wrong
any suggestions on YEs or NO statement "return to main Switchboard" command


Allen Browne said:
Instead of setting all controls to a zero-length string, undo the form:
Private Sub cmdClearJob_Click()
If Me.Dirty Then
Me.Undo
End If
End Sub

Simarly, there is no need to write the record manually by editing the
recordset (which won't work anyway, as you have no .Edit and .Update). Just
bind the form to the table, and Access will write it for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

accclt718 said:
hello All
programming is not my cup of tea, but someone has do it
I have a submit job form, with has 4 command buttons,
1) submit job
2) clear job ( which clear all the text on form while its open)
3) print job
4) return main SB (also having a problem Yes or NO option when he/she
clicks return to main SB

here is the code for clear command

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub
----------------------------------------------------------------------------------------
here is what my subjob form looks like, part of it

Option Compare Database

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub

Private Sub FormExit_Click()

DoCmd.Close
DoCmd.SelectObject acForm, "Switchboard"

End Sub

Private Sub Print_Button_Click()
If DLookup("ProjectNumber", "XtecProjects", "ProjectNumber=" & Me!ProjNum)
=
Me!ProjNum Then
DoCmd.PrintOut acSelection
Else
MsgBox "You Must Submit job First, Before Printing"
End If

End Sub

Private Sub ProjNum_GotFocus()
Dim X As Integer
X = DMax("ProjectNumber", "nameProjects")
Me!ProjNum = X + 1
Me!TbxDateIn = format(Now(), "mm/dd/yyyy")
End Sub

Private Sub SubJob_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [nameProjects] (ProjectNumber,
SpecialInstructions, ProjectDescription, Consultant, DateIn, FinalDue,
PrimaryFormat, ContactName, Presenter, Company, Floor, Street,
CityStateZip,
ExpenseCode, ProjectCode, DepartmentCode, Telephone, email, Fax) SELECT
Forms!JobEFrm!ProjNum AS ProjectNumber, Forms!JobEFrm!TbxNotes AS
SpecialInstructions, Forms!JobEFrm!TbxDescr AS ProjectDescription,
Forms!JobEFrm!cboConsultant AS Consultant, Forms!JobEFrm!TbxDateIn AS
DateIn,
Forms!JobEFrm!TbxFnlDue AS FinalDue, Forms!JobEFrm!TbxPF AS PrimaryFormat,
Forms!JobEFrm!cboCntct AS ContactName, Forms!JobEFrm!TbxPres AS Presenter,
Forms!JobEFrm!TbxComp AS Company, Forms!JobEFrm!TbxFloor AS Floor,
Forms!JobEFrm!TbxStreet AS Street, Forms!JobEFrm!TbxCityStateZip AS
CityStateZip, Forms!JobEFrm!TbxExpCo AS ExpenseCode,
Forms!JobEFrm!TbxProCode
AS ProjectCode, Forms!JobEFrm!TbxDeptCo AS DepartmentCode,
Forms!JobEFrm!TbxPhone AS Telephone, Forms!JobEFrm!TbxEmail AS email,
Forms!JobEFrm!TbxFax AS Fax"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Job was Submitted"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

accclt718

Allen Browne

i tried your code, when click on it nothing happens ?
unless i'm something wrong
any suggestions on YEs or NO statement "return to main Switchboard" command


Allen Browne said:
Instead of setting all controls to a zero-length string, undo the form:
Private Sub cmdClearJob_Click()
If Me.Dirty Then
Me.Undo
End If
End Sub

Simarly, there is no need to write the record manually by editing the
recordset (which won't work anyway, as you have no .Edit and .Update). Just
bind the form to the table, and Access will write it for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

accclt718 said:
hello All
programming is not my cup of tea, but someone has do it
I have a submit job form, with has 4 command buttons,
1) submit job
2) clear job ( which clear all the text on form while its open)
3) print job
4) return main SB (also having a problem Yes or NO option when he/she
clicks return to main SB

here is the code for clear command

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub
----------------------------------------------------------------------------------------
here is what my subjob form looks like, part of it

Option Compare Database

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub

Private Sub FormExit_Click()

DoCmd.Close
DoCmd.SelectObject acForm, "Switchboard"

End Sub

Private Sub Print_Button_Click()
If DLookup("ProjectNumber", "XtecProjects", "ProjectNumber=" & Me!ProjNum)
=
Me!ProjNum Then
DoCmd.PrintOut acSelection
Else
MsgBox "You Must Submit job First, Before Printing"
End If

End Sub

Private Sub ProjNum_GotFocus()
Dim X As Integer
X = DMax("ProjectNumber", "nameProjects")
Me!ProjNum = X + 1
Me!TbxDateIn = format(Now(), "mm/dd/yyyy")
End Sub

Private Sub SubJob_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [nameProjects] (ProjectNumber,
SpecialInstructions, ProjectDescription, Consultant, DateIn, FinalDue,
PrimaryFormat, ContactName, Presenter, Company, Floor, Street,
CityStateZip,
ExpenseCode, ProjectCode, DepartmentCode, Telephone, email, Fax) SELECT
Forms!JobEFrm!ProjNum AS ProjectNumber, Forms!JobEFrm!TbxNotes AS
SpecialInstructions, Forms!JobEFrm!TbxDescr AS ProjectDescription,
Forms!JobEFrm!cboConsultant AS Consultant, Forms!JobEFrm!TbxDateIn AS
DateIn,
Forms!JobEFrm!TbxFnlDue AS FinalDue, Forms!JobEFrm!TbxPF AS PrimaryFormat,
Forms!JobEFrm!cboCntct AS ContactName, Forms!JobEFrm!TbxPres AS Presenter,
Forms!JobEFrm!TbxComp AS Company, Forms!JobEFrm!TbxFloor AS Floor,
Forms!JobEFrm!TbxStreet AS Street, Forms!JobEFrm!TbxCityStateZip AS
CityStateZip, Forms!JobEFrm!TbxExpCo AS ExpenseCode,
Forms!JobEFrm!TbxProCode
AS ProjectCode, Forms!JobEFrm!TbxDeptCo AS DepartmentCode,
Forms!JobEFrm!TbxPhone AS Telephone, Forms!JobEFrm!TbxEmail AS email,
Forms!JobEFrm!TbxFax AS Fax"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Job was Submitted"

End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

As explained last reply that code you posted will do nothing at all because
there is no Edit/Addnew and Update

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

accclt718 said:
here is update code i think not sure, the database was done by another IT
person who left the comapny

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '" &
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

thank you

accclt718 said:
Allen Browne

i tried your code, when click on it nothing happens ?
unless i'm something wrong
any suggestions on YEs or NO statement "return to main Switchboard"
command


Allen Browne said:
Instead of setting all controls to a zero-length string, undo the form:
Private Sub cmdClearJob_Click()
If Me.Dirty Then
Me.Undo
End If
End Sub

Simarly, there is no need to write the record manually by editing the
recordset (which won't work anyway, as you have no .Edit and .Update).
Just
bind the form to the table, and Access will write it for you.


hello All
programming is not my cup of tea, but someone has do it
I have a submit job form, with has 4 command buttons,
1) submit job
2) clear job ( which clear all the text on form while its open)
3) print job
4) return main SB (also having a problem Yes or NO option when
he/she
clicks return to main SB

here is the code for clear command

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub
----------------------------------------------------------------------------------------
here is what my subjob form looks like, part of it

Option Compare Database

Private Sub cboCntct_AfterUpdate()

Dim ContactInf As String
Dim rst As DAO.Recordset
ContactInf = "SELECT * FROM Contacts " & " Where ContactName = '"
&
Me!cboCntct.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactInf, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!TbxPhone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!email
End If

Set rst = Nothing

End Sub

Private Sub cmdClearJob_Click()
Dim clt As Control
If Me.Control Then
For Each clt In Me
If clt.ControlType = acTextBox Then
clt = ""
End If
Next clt

End If
End Sub

Private Sub FormExit_Click()

DoCmd.Close
DoCmd.SelectObject acForm, "Switchboard"

End Sub

Private Sub Print_Button_Click()
If DLookup("ProjectNumber", "XtecProjects", "ProjectNumber=" &
Me!ProjNum)
=
Me!ProjNum Then
DoCmd.PrintOut acSelection
Else
MsgBox "You Must Submit job First, Before Printing"
End If

End Sub

Private Sub ProjNum_GotFocus()
Dim X As Integer
X = DMax("ProjectNumber", "nameProjects")
Me!ProjNum = X + 1
Me!TbxDateIn = format(Now(), "mm/dd/yyyy")
End Sub

Private Sub SubJob_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [nameProjects] (ProjectNumber,
SpecialInstructions, ProjectDescription, Consultant, DateIn,
FinalDue,
PrimaryFormat, ContactName, Presenter, Company, Floor, Street,
CityStateZip,
ExpenseCode, ProjectCode, DepartmentCode, Telephone, email, Fax)
SELECT
Forms!JobEFrm!ProjNum AS ProjectNumber, Forms!JobEFrm!TbxNotes AS
SpecialInstructions, Forms!JobEFrm!TbxDescr AS ProjectDescription,
Forms!JobEFrm!cboConsultant AS Consultant, Forms!JobEFrm!TbxDateIn AS
DateIn,
Forms!JobEFrm!TbxFnlDue AS FinalDue, Forms!JobEFrm!TbxPF AS
PrimaryFormat,
Forms!JobEFrm!cboCntct AS ContactName, Forms!JobEFrm!TbxPres AS
Presenter,
Forms!JobEFrm!TbxComp AS Company, Forms!JobEFrm!TbxFloor AS Floor,
Forms!JobEFrm!TbxStreet AS Street, Forms!JobEFrm!TbxCityStateZip AS
CityStateZip, Forms!JobEFrm!TbxExpCo AS ExpenseCode,
Forms!JobEFrm!TbxProCode
AS ProjectCode, Forms!JobEFrm!TbxDeptCo AS DepartmentCode,
Forms!JobEFrm!TbxPhone AS Telephone, Forms!JobEFrm!TbxEmail AS email,
Forms!JobEFrm!TbxFax AS Fax"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Job was Submitted"

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with conditional statements 1
update records, client contact infomation 0
VBA works in orignal but fails in copy of database 1
SQL update record not working 4
advice on how to update excisting contact on open form 2
Pls help, my VBA does not work 3
Works in WinXP, not in Vista 13
How to: update existing contact info on form 1

Top