My add button stopped working. I'm trying to trouble shoot what could have happend. Pasted code

[GinaFlan]

Private Sub CmdAdd_Click()
If IsNull(Me.ComboFiscal) Then ' No business unit


MsgBox ("Please add Fiscal Year!"), vbOKCancel ' Tell user
Me.ComboFiscal.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test
If IsNull(Me.txtContact) Then ' No business unit
MsgBox ("Please add Contact!"), vbOKCancel ' Tell user
Me.txtContact.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

If IsNull(Me.ComboMonth) Then ' No business unit
MsgBox ("Please add Month!"), vbOKCancel ' Tell user
Me.ComboMonth.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

If Nz(Me.TxtActualBenchmark, "") = "" And Me.Comboyesorno.Visible = False Then ' No business unit
MsgBox ("Please add Results!"), vbOKCancel ' Tell user
Me.txtContact.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

If (Me.ComboGoals.Value = "") Then ' No business unit
MsgBox ("Please add Goals!"), vbOKCancel ' Tell user
Me.ComboGoals.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test
If Nz(Me.Comboyesorno, "") = "" And Me.Comboyesorno.Visible = True Then ' No business unit
MsgBox ("Please add Yes or No!"), vbOKCancel ' Tell user
Me.txtContact.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test


Select Case TxtActualBenchmark
Case "N/A"

Case "At least one program every 6 months"
Select Case Me.ComboMonth
Case "January", "February", "March", "April", "May", "July", "August", "September", "October", "November"
MsgBox "Please put N/A", vbOKCancel
Case "June", "December"
'do nothing
End Select

Case Else 'or NOT "N/A"
'SOMETHIN
End Select
Select Case TxtActualBenchmark
Case "N/A"

Case "25% Implemented every 4 months"
Select Case Me.ComboMonth
Case "January", "February", "March", "May", "July", "September", "October", "November"
MsgBox "Please put N/A", vbOKCancel
Case "April", "August", "December"
'do nothing
End Select

Case Else 'or NOT "N/A"
'SOMETHIN
End Select





If IsNumeric(Me.TxtActualBenchmark) = "False" Then
Select Case Me.ComboGoals.Column(2)
Case 1, 4, 6, 10, 11, 12, 13, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 46, 47, 48, 50, 51, 56, 59, 61, 74, 75, 79, 80, 84, 85, 86, 87, 88, 89, 90, 91, 96, 99, 100, 108, 110, 112, 115, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 142, 143, 144, 145, 146, 147, 148, 149, 152, 153, 154, 156, 158, 159, 160, 161, 162, 163, 164, 165, 166, 171, 172, 173, 174, 184, 185, 186, 187, 188, 190, 191, 192, 193, 195, 196, 197, 200, 204, 205, 208, 209, 210, 211, 213, 214, 216, 219
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End Select
Exit Sub
End If
If InStr(Me.TxtActualBenchmark, ".") = 0 Then
Select Case Me.ComboGoals.Column(2)
Case 2, 3, 9, 20, 43, 45, 54, 55, 58, 60, 97, 103, 116, 117, 150, 167, 168, 169, 182, 194, 199, 201, 207
MsgBox "Please enter a decimal", vbInformation, "Missing Information"
Cancel = True
End Select
Exit Sub
End If


'Add data to form
CurrentDb.Execute "INSERT INTO Scorecards (ForTheMonth,FiscalYear,Groups,Contact,Goals,Bench marks,Results,Comments,MetGoal)" & _
"VALUES('" & Me.ComboMonth & "','" & Me.ComboFiscal & "','" & Me.ComboDepartment & "','" & Me.txtContact & "','" & Me.ComboGoals & "','" & Me.txtBenchmarks & "','" & Me.TxtActualBenchmark & "','" & Me.txtComments & "','" & Me.txtMetGoal & "');"
'message box

'Requery Form
frmscorecardssub.Form.Requery
'Clear Form
CmdClear_Click
End Sub

[ranman256]

Does it give an error? If so , where?
Does it NOT do something at a certain point?

lil help.

1st
If IsNumeric(Me.TxtActualBenchmark) = "False" Then
FALSE is a numerica value .... NO QUOTES.

IF not IsNumeric(Me.TxtActualBenchmark)
or
if IsNumeric(Me.TxtActualBenchmark) = false

[orange]

It would be helpful to readers if you:
a)provided some context as to the purpose of the database
b)showed some of your debugging steps with some results/error messages...
c) asked specific question

I recommend you research the Access Smart Indenter ( a free utility to indent code) and
MZTools for VBA (another great free utility) and
use Code tags when publishing/posting code.

For debugging guidelines/technques see http://www.cpearson.com/excel/DebuggingVBA.aspx

[GinaFlan]

Thank I will take the quotes off of False. When I get home after work I will upload the database. I can't do it from work.

[GinaFlan]

I don't get any error messages. Nothing happens when I hit add, instead of the new record going into to the table/subform

[GinaFlan]

I think I narrowed it down to this code below, maybe those are "too many" maybe it is slowing it down. I could be wrong.


My "Please add results!" pop up works


When I try to add a result that is a "decimal" answer not numeric it works my "numeric" and yes or no questions are the ones giving me trouble or that appears to be the case, going to keep working on it, I will post it tonight, if I can't figure it out

I have an earlier copy of it with almost the same code, just not as many goal numbers entered, that is working









If IsNumeric(Me.TxtActualBenchmark) = "False" Then
Select Case Me.ComboGoals.Column(2)
Case 1, 4, 6, 10, 11, 12, 13, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 46, 47, 48, 50, 51, 56, 59, 61, 74, 75, 79, 80, 84, 85, 86, 87, 88, 89, 90, 91, 96, 99, 100, 108, 110, 112, 115, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 142, 143, 144, 145, 146, 147, 148, 149, 152, 153, 154, 156, 158, 159, 160, 161, 162, 163, 164, 165, 166, 171, 172, 173, 174, 184, 185, 186, 187, 188, 190, 191, 192, 193, 195, 196, 197, 200, 204, 205, 208, 209, 210, 211, 213, 214, 216, 219
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End Select
Exit Sub
End If

[John_G]

Another thing you should always do is use the dbfailonerror option in the Currentdb.Execute:

Currentdb.Execute "....", dbFailonerror

This causes Access to generate a runtime error if there is a problem with the SQL; sometimes Access won't tell you the command failed - it just doesn't do anything.

[GinaFlan]

Ok I have the error now. I think I know what happened. Some of the employees entered data today. So I appended their records into my Scorecards table. Maybe when I did that it messed something up but should not have. The error I am getting is Run Time error '3078' The microsoft access databse engine cannot find the input table or query. Make sure it exists and the name is spelled correctly.


Sooooo I'm goning to do an easy fix and copy and paste my code into a saved older copy that doesn't have the appended records. That's the only thing I can think do. When I appended it didn't change the name so I am not sure why it would give me the error, anyway.

[GinaFlan]

Nevermind that was not the error, I got that error by adding this just now
DoCmd.SetWarnings True
CurrentDb.Execute strSQL, dbFailOnError

[GinaFlan]

This was messing it up. I think it works now but I only did one test


If InStr(Me.TxtActualBenchmark, ".") = 0 Then
Select Case Me.ComboGoals.Column(2)
Case 2, 3, 9, 20, 43, 45, 54, 55, 58, 60, 97, 103, 116, 117, 150, 167, 168, 169, 182, 194, 199, 201, 207
MsgBox "Please enter a decimal", vbInformation, "Missing Information"
Cancel = True
End Select
Exit Sub
End If

[GinaFlan]

I really don't where to put dbFailonerror? before? after? I'm kinda new at this

'Add data to form
CurrentDb.Execute "INSERT INTO Scorecards (ForTheMonth,FiscalYear,Groups,Contact,Goals,Bench marks,Results,Comments,MetGoal)" & _
"VALUES('" & Me.ComboMonth & "','" & Me.ComboFiscal & "','" & Me.ComboDepartment & "','" & Me.txtContact & "','" & Me.ComboGoals & "','" & Me.txtBenchmarks & "','" & Me.TxtActualBenchmark & "','" & Me.txtComments & "','" & Me.txtMetGoal & "');"

[John_G]

You put it right after the SQL statement:

CurrentDb.Execute "INSERT INTO Scorecards (ForTheMonth,FiscalYear,Groups,Contact,Goals,Bench marks,Results,Comments,MetGoal)" & _
"VALUES('" & Me.ComboMonth & "','" & Me.ComboFiscal & "','" & Me.ComboDepartment & "','" & Me.txtContact & "','" & Me.ComboGoals & "','" & Me.txtBenchmarks & "','" & Me.TxtActualBenchmark & "','" & Me.txtComments & "','" & Me.txtMetGoal & "');", dbfailonerror