Remove selected item from table via listbox

[tmyers]

I have the following code:

Private Sub Removecontractorbtn_Click()

Dim conid As Long
Dim strsql As String

    If IsNull(lstContractorsOnJob) Then
        Exit Sub
    End If
    
    With Me.lstContractorsOnJob
        condid = lstContractorsOnJob.Value
    
        strsql = "DELETE FROM tblContractorJob WHERE ContractorID = " & conid
        CurrentDb.Execute strsql
    
        lstContractorsOnJob.Requery
        lstContractors.Requery
    
    End With
    
End Sub

I am trying to remove/delete an item from a table based on a selection in a list box. This works up until the strsql line, then my value goes to 0. I think my mistake is I need to pass conID as a variant, but am unsure how to restructure it.

Could anyone offer some insight on this one?

 

[Gasman]

So what is conid after being set from the listbox value
use F8 and step through the code.

 

[theDBguy]

What do you mean? When does the value becomes 0? How do you know?

 

[tmyers]

conid is 3 (which is the correct ID in my test based off selection) but conid at the end of the SQL statement is 0. Thats just me hovering over it and seeing what its value is.

 

[tmyers]

Nevermind. I pulled a hard stupid. I misspelled conid when setting its value and spelled it as condid.

I need more coffee. Sorry for wasting your guys time

 

[theDBguy]

conid is 3 (which is the correct ID in my test based off selection) but conid at the end of the SQL statement is 0. Thats just me hovering over it and seeing what its value is.

And why would that matter? It did its job. It's back to 0 waiting for a new id to delete.

 

[theDBguy]

Actually, correction. lstContractorsOnJob has a value of 3.
Conid is just 0.

Is 3 the first one on the Listbox? Remember, the Requery will do that.

 

[Gasman]

Nevermind. I pulled a hard stupid. I misspelled conid when setting its value and spelled it as condid.

I need more coffee. Sorry for wasting your guys time

I never spotted that either

You really should have Option Explicit at the top of every module.

 

[theDBguy]

Nevermind. I pulled a hard stupid. I misspelled conid when setting its value and spelled it as condid.

I need more coffee. Sorry for wasting your guys time

Best way to avoid that is to always have Option Explicit at the top of your modules.

 

[tmyers]

I do have it at the very top of my page. This particular form has quite a few events. clicks, before/after updates on loads etc. Should I have that before each sub? Or should break my mess of coding into more individual modules?

Correction, this only had compare database and not option explicit. I have added it.

 

[theDBguy]

I do have it at the very top of my page. This particular form has quite a few events. clicks, before/after updates on loads etc. Should I have that before each sub? Or should break my mess of coding into more individual modules?

Correction, this only had compare database and not option explicit. I have added it.

I think you're only allowed to enter it once per module. Are you saying you're actually using both spelling of the variable in your code? Otherwise, if you didn't declare one of them, you should get an error.

 

[tmyers]

No no. It was supposed to conid and I fudged it into condid.

Sometimes my think can brain.

 

[Gasman]

I do have it at the very top of my page. This particular form has quite a few events. clicks, before/after updates on loads etc. Should I have that before each sub? Or should break my mess of coding into more individual modules?

Correction, this only had compare database and not option explicit. I have added it.

In the VBA window
Choose Tools/Options and ensure 'Require Variable Declaration' is ticked.

Then it will be added automatically when a new form/report/module etc is created.

 

[Isaac]

FYI: being "0" is what data type Long defaults to (starts out as), if never actually assigned a value which is why you got zero.