VBA Help needed

[Sunglow08]

For Each item In lst.ItemsSelected
CurrentDb.Execute "UPDATE tbl_Staffing SET Term_Heat_Ticket_Num = '" & _
Me.TxtTerm_Heat_Ticket_Num & "', Inactive_Status_Dt = #" & lst.Column(7, item) & _
"#, Termination_Reason_FK = " & lst.Column(9, item) & " WHERE ID = " & lst.Column(8, item), dbFailOnError

I have this query that writes to a list on my form. I am trying to use this code to take the Term_Heat_Ticket number, the Inactive_Status_dt and the Termination_Reason_FK to write to my other table where the ID for the other table equals to the column 8 in my query. All is returning values except the Termination_Reason_FK which if you see my query above there is a value. I am not sure what I am doing wrong in my code syntax that would cause the Termination_Reason_FK to return a null value. Any help would be appreciated.

 

[Gasman]

I *never* just construct a bunch of code like this, without putting it into a string variable.

Then I can Debug.Print that variable and check syntax and content.?

That quickly gets to the bottom of problems like this.

 

[Sunglow08]

Private Sub ProcessApproved()
Dim item As Variant
Dim lst As ListBox
Dim rstNew As DAO.Recordset
Dim rstEmp As DAO.Recordset
Dim fld As DAO.Field
Dim lngFldCounter As Long
Dim rst As DAO.Recordset
Dim lngNewID As Long

Set lst = Me.lstTermEmpl

For Each item In lst.ItemsSelected




CurrentDb.Execute "UPDATE tbl_Staffing SET Term_Heat_Ticket_Num = '" & _
Me.TxtTerm_Heat_Ticket_Num & "', Inactive_Status_Dt = #" & lst.Column(7, item) & _
"#, Termination_Reason_FK = " & lst.Column(9, item) & " WHERE ID = " & lst.Column(8, item), dbFailOnError

CurrentDb.Execute "DELETE * from Tbl_Term_Employees WHERE Term_ID = " & lst.Column(0, item)

Next item

lst.Requery


End Sub
This is my entire sub for this. Above is my query. It is returning all the values I need but the Termination_Reason_FK value which it is returning as null but as you will see in the query there is a value. I am trying to figure out why this is not returning the value in my query for my list.

 

[Sunglow08]

Any suggestions would be appreciated? Please provide examples --I am still new to VBA coding in Access and need all the help I can get.

 

[Gasman]

You are showng me the results of a query, not what *might* be in the listbox.?

That is why I use the Debug.Print method. Generally people *assume* the values are correct, but do not check.?

Put that UPDATE statement into a string (I normally use strSQL) then Debug.Print it. Even do the same for the DELETE statement.

Put the Debug.Print before the execution of the statement, not after as one other member did.

 

[pbaldy]

I would also use a variable so it can be debugged:

Debugging

If the other listbox values are being retrieved and not that one, make sure it's the 10th field in the listbox and that the column count of the listbox is at least 10.

 

[Sunglow08]

Can you provide me an example of how to do that code so I can do the check?

 

[Cronk]

And also explicitly format your date data
...& format(lst.Column(7, item) ,"mm/dd/yyyy") & ...

 

[Sunglow08]

The debugging will only work if the entire statement gets executed which because that one thing is coming up null it is not executing the entire line of code. I checked the columns in the query and it is the 9th column since the first column always starts with 0. The code syntax is correct, it is just not returning the value in the query.

 

[Sunglow08]

Thank you for the date tip, although the date is not my issue. I did apply your date tip to the code.

 

[Gasman]

The debugging will only work if the entire statement gets executed which because that one thing is coming up null it is not executing the entire line of code. I checked the columns in the query and it is the 9th column since the first column always starts with 0. The code syntax is correct, it is just not returning the value in the query.

BS. the debugging will work, even if you comment out the Execute statement. However I'll let you get to the bottom of it your way.

 

[pbaldy]

I checked the columns in the query and it is the 9th column since the first column always starts with 0.

You're referring to column 9, which would be the 10th column in the listbox. Did you check the column count property of the listbox? As to debugging, you put the SQL into a variable before the execution, so you can see the SQL that is going to be executed. You can check that whether or not it gets executed.

 

[Cronk]

Sunglow08 wrote Can you provide me an example of how to do that code so I can do the check?

Dim strSQL as string
strSQL="UPDATE tbl_Staffing SET Term_Heat_Ticket_Num = '" & _
Me.TxtTerm_Heat_Ticket_Num & "', Inactive_Status_Dt = #" & lst.Column(7, item) & _
"#, Termination_Reason_FK = " & lst.Column(9, item) & " WHERE ID = " & lst.Column(8, item), dbFailOnError
debug.print strSQL

 

[Sunglow08]

You're referring to column 9, which would be the 10th column in the listbox. Did you check the column count property of the listbox? As to debugging, you put the SQL into a variable before the execution, so you can see the SQL that is going to be executed. You can check that whether or not it gets executed.

Thank you, I think I just fixed my problem when you said check the column count property in the listbox. Thank you so much

 

[pbaldy]

Happy to help!