Access update VBA 1

[jeffwest2]

On the close of a form I have a series of codes that 'should' update a table with in the database with data from the fields on the form, the issue i have is that the update sql doesn't seem to work. Code is below, the highlighted bit is what doesn't seem to want to work.

Private Sub Command496_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
DoCmd.SetWarnings False
Dim Number As String

Number = Calls.Value

If Number = "1" Then Me.Call1_Outcome = CurrOutcome.Value Else
If Number = "2" Then Me.Call2_Outcome = CurrOutcome.Value Else
If Number = "3" Then Me.Call3_Outcome = CurrOutcome.Value Else
If Number = "4" Then Me.Call4_Outcome = CurrOutcome.Value Else
If Number = "5" Then Me.Call5_Outcome = CurrOutcome.Value Else
If Number = "6" Then Me.Call6_Outcome = CurrOutcome.Value Else
If Number = "7" Then Me.Call7_Outcome = CurrOutcome.Value Else
If Number = "8" Then Me.Call8_Outcome = CurrOutcome.Value Else

Dim change As String

If Me.CurrOutcome.Value = "1" Then change = "Accepted" Else
If Me.CurrOutcome.Value = "2" Then change = "Accepted in Store" Else
If Me.CurrOutcome.Value = "3" Then change = "Appointment" Else
If Me.CurrOutcome.Value = "4" Then change = "Appointment in store" Else
If Me.CurrOutcome.Value = "5" Then change = "Callback" Else
If Me.CurrOutcome.Value = "6" Then change = "Declined" Else
If Me.CurrOutcome.Value = "7" Then change = "Not Available" Else
If Me.CurrOutcome.Value = "8" Then change = "Wrong Number" Else
If Me.CurrOutcome.Value = "10" Then change = "Withdrawn" Else
If Me.CurrOutcome.Value = "11" Then change = "Contacting" Else
If Me.CurrOutcome.Value = "12" Then change = "Uncontactable" Else

Dim Adder As String

If IsNull(Me.Add_Out.Value) Then Adder = ""
If Me.Add_Out.Value = "1" Then Adder = "Voicemail" Else
If Me.Add_Out.Value = "2" Then Adder = "Engaged" Else
If Me.Add_Out.Value = "3" Then Adder = "No Answer" Else
If Me.Add_Out.Value = "4" Then Adder = "Callback - DMC" Else
If Me.Add_Out.Value = "5" Then Adder = "Callback - Non DMC" Else

If CurrOutcome <> "" And IsNull(Calls) Then
Msg = "You must add call attemps if you are adding an outcome"

Style = vbOKOnly + vbInformation + vbDefaultButton2
Title = "Brighthouse Data Added"
Help = ""
Ctxt = 1000

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Else

If IsNull(CurrOutcome.Value) Then

Msg = "Do you want to exit without an outcome"

Style = vbYesNo + vbInformation + vbDefaultButton2
Title = "Brighthouse Data Added"
Help = ""
Ctxt = 1000

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then

'Forms!frmpersonContact.RecordSource = ""
'DoCmd.Close acForm, "frmPersonContact"
   DoCmd.OpenQuery "Details_Temp Query"
   DoCmd.RunSQL "Delete from Details_Temp where  User = '" & fOSUserName & "';"
   
      DoCmd.OpenQuery "Details_Temp_Update_2"
   DoCmd.RunSQL "Delete from Details_Temp2 where  User = '" & fOSUserName & "';"
   
'DoCmd.OpenForm "View_Data"
Forms!View_Data.Visible = True
   Forms!View_Data!lstSearch.Requery
    Forms!View_Data!Call_Att.Requery
    Forms!View_Data!UserOnly.Requery

End If
Else

 DoCmd.OpenQuery "Details_Temp Query"
   DoCmd.RunSQL "Delete from Details_Temp where  User = '" & fOSUserName & "';"
   
      DoCmd.OpenQuery "Details_Temp_Update_2"
   DoCmd.RunSQL "Delete from Details_Temp2 where  User = '" & fOSUserName & "';"
   
   Forms!View_Data.Visible = True
   Forms!View_Data!lstSearch.Requery
    Forms!View_Data!Call_Att.Requery
    Forms!View_Data!UserOnly.Requery
   

End If
End If

Dim Result As String
Dim Result2 As String
Dim Result3 As String
Dim Result4 As String

If IsNull(Forms!frmpersonContact!Add_Out.Value) Then
Result3 = change 'Me.CurrOutcome.Value
Result2 = fOSUserName

Result = Result3 + " " + Result2
Else
Result3 = change 'Me.CurrOutcome.Value
Result4 = Adder 'Me.Add_Out.Value
Result2 = fOSUserName

Result = Result3 + " " + Result4 + " " + Result2
End If

Dim strSQL As String
Dim Column As String
Dim Date_Column As String

If Me.Calls.Value = "1" Then Column = "Call1_Comments" Else
If Me.Calls.Value = "1" Then Date_Column = "DandT_Call1" Else
If Me.Calls.Value = "1" Then Result = "Call 1: " & Result Else

If Me.Calls.Value = "2" Then Column = "Call2_Comments" Else
If Me.Calls.Value = "2" Then Date_Column = "DandT_Call2" Else
If Me.Calls.Value = "2" Then Result = "Call 2: " & Result Else

If Me.Calls.Value = "3" Then Column = "Call3_Comments" Else
If Me.Calls.Value = "3" Then Date_Column = "DandT_Call3" Else
If Me.Calls.Value = "3" Then Result = "Call 3: " & Result Else

If Me.Calls.Value = "4" Then Column = "Call4_Comments" Else
If Me.Calls.Value = "4" Then Date_Column = "DandT_Call4" Else
If Me.Calls.Value = "4" Then Result = "Call 4: " & Result Else

If Me.Calls.Value = "5" Then Column = "Call5_Comments" Else
If Me.Calls.Value = "5" Then Date_Column = "DandT_Call5" Else
If Me.Calls.Value = "5" Then Result = "Call 5: " & Result Else

If Me.Calls.Value = "6" Then Column = "Call6_Comments" Else
If Me.Calls.Value = "6" Then Date_Column = "DandT_Call6" Else
If Me.Calls.Value = "6" Then Result = "Call 6: " & Result Else

If Me.Calls.Value = "7" Then Column = "Call7_Comments" Else
If Me.Calls.Value = "7" Then Date_Column = "DandT_Call7" Else
If Me.Calls.Value = "7" Then Result = "Call 7: " & Result Else

If Me.Calls.Value = "8" Then Column = "Call8_Comments" Else
If Me.Calls.Value = "8" Then Date_Column = "DandT_Call8" Else
If Me.Calls.Value = "8" Then Result = "Call 8: " & Result Else

[highlight #CC0000]DoCmd.RunSQL "UPDATE Data_File SET " & Date_Column & "= Now() where ID = " & Me.Clientid & ""
DoCmd.RunSQL "UPDATE Data_File SET Last_Updated_Date = Now() where ID = " & Me.Clientid & ""
DoCmd.RunSQL "UPDATE Data_File SET Last_Updated_by = '" & fOSUserName & "' where ID = " & Me.Clientid & ""[/highlight]
    
    If IsNull(Me.Comments_List) Then
    Me.Comments_List = "" & Column & ": " & Result & "" & vbNewLine
    Else
    Me.Comments_List = Me.Comments_List + "" & Column & ": " & Result & "" & vbNewLine
    End If
    
    DoCmd.RunSQL "INSERT INTO Log_Table (User,Record_Id,What_Updated,Details,Updated_Date) Values ('" & fOSUserName & "','" & Me.Clientid & "','Comments','" & Result & "',Now())"

    DoCmd.Close acForm, "frmPersonContact"
    Forms!View_Data.Visible = True
    Forms!View_Data!lstSearch.Requery
    Forms!View_Data!Call_Att.Requery
    Forms!View_Data!UserOnly.Requery
    Forms!View_Data!Last5.Requery
    Forms!View_Data!Callback.Requery
    Forms!View_Data!PastCB.Requery
    Forms!View_Data.Requery

Any help with this would be appreciated.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[madonnac]

The Else at the end of each If ... Then ... Else single line construct is unnecessary, and can be confusing to some people.

The only reason I can see for the SQL statement not working is if the ID is text, and not numeric, or Me.Clientid does not contain a value.

for the first case, change the queries to read

... where ID = """ & Me.Clientid & """"

for the second case, test for numeric with

If Not IsNumeric(Me.clientid) Then
    msgbox "ClientID should be numberic"
    exit sub 'or exit function
end if

 

[Andrzejek]

Why have 3 Updates, why not just one?
(If your Date_Column and Last_Updated_Date are defined as Dates, use #)

Dim strSQL As String
...
strSQL = "UPDATE Data_File SET " & Date_Column & " = #" & Now() & "#" _
    & ", Last_Updated_Date = #" & Now() & "#" _
    & ", Last_Updated_by = '" & fOSUserName & "'" _
    & " WHERE ID = " & Me.Clientid

Debug.Print strSQL

DoCmd.RunSQL strSQL

And show what you have in Immediate Window (your Update SQL)

Have fun.

---- Andy

 

[jeffwest2]

3 updates was only because i couldn't get them to work originally, had a just got mixed up so included here as it was.

Thanks for the code, it seems to work again now, so hopefully i will be able to move this on a bit.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

You probably could change all the:
[tt]
If Number = "1" Then Me.Call1_Outcome = CurrOutcome.Value Else
If Number = "2" Then Me.Call2_Outcome = CurrOutcome.Value Else
If Number = "3" Then Me.Call3_Outcome = CurrOutcome.Value Else
...
[/tt]
to be just one line:
[tt]
Me.Controls("Call" & Number & "_Outcome") = CurrOutcome.Value
[/tt]
Insetad of:[tt]
If Me.CurrOutcome.Value = "1" Then change = "Accepted" Else
If Me.CurrOutcome.Value = "2" Then change = "Accepted in Store" Else
If Me.CurrOutcome.Value = "3" Then change = "Appointment" Else
...
[/tt]
Have this data in a table, that's where it belongs you know...

And all of the
[tt]
If Me.Calls.Value = "1" Then Column = "Call1_Comments" Else
If Me.Calls.Value = "1" Then Date_Column = "DandT_Call1" Else
If Me.Calls.Value = "1" Then Result = "Call 1: " & Result Else
...
[/tt]
could be just:
[tt]
Column = "Call" & Me.Calls.Value & "_Comments"
Date_Column = "DandT_Call" " & Me.Calls.Value
Result = "Call " & Me.Calls.Value & ": " & Result
[/tt]

Just my $0.02

Have fun.

---- Andy

 

[jeffwest2]

Thanks for the additional stuff, alot of this was trying to get things working quickly as I had little time to put something in place, I now have a bit of time to go back and tidy it up, so this gives me a bit more to think about,

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

Couple more 'hints'...

Private Sub Command496_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
DoCmd.SetWarnings False
Dim Number As String

Command496 is pretty 'non-discriptive' name for what this command button does.
Variables: Msg, Style, Title, Help, Ctxt, Response, MyString - are all Variants
Dim Number As String, well... what is it: a number or a string?

Think of it this way: would you like to take over somebody else's code like that to work with? Would you be happy with it?

How about:

Private Sub cmdUpdateStuff_Click()
Dim strMsg As String
Dim intStyle  As Integer
Dim strTitle As String
Dim lngHelp As Long
Dim strCtxt As String
Dim blnResponse As Boolean
Dim strMyString As String

DoCmd.SetWarnings False
Dim strNumber As String

I know it is a lot longer, and you probably wrote this quick, but seems nobody goes gack to the code to fix it later. So why not do it right the first time around? it is not that much more work.

BTW, I hope you do have [tt]Option Explicit[/tt] at the top, too.



Have fun.

---- Andy

 

[jeffwest2]

You are right, I did do a little bit of lazy coding with this, some of it was due to time constraints(not really an excuse) and some was trying to get things working different ways and not going back and correcting them but leaving it.

Thankfully I do have a little time to tidy things up now, which is what I am doing, already changed the name of the button, and yes, I do have option explicit at the top.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.