Problem with null values

[zfind]

Hi all,

I've worked out one of my other problems almost to completion - I've created the following code which creates records of all combinations of listboxes chosen on a form:

Private Sub cmdSave_Click()
'DoCmd.SetWarnings (0)
Dim Sql As String
Dim PName As String
Dim Channel As Integer
Dim Country As Integer
Dim HostingID As Integer
Dim RevShare As Double
If IsNull(cboPName.Value) Then
    MsgBox "Partner name cannot be left blank"
    Exit Sub
Else
    PName = Chr(34) & cboPName.Value & Chr(34)
End If

If IsNull(cboHostingID.Value) Then
    HostingID = 0
    Else
    HostingID = cboHostingID.Value
End If
If IsNull(txtRevShare.Value) Then
    RevShare = 0
    Else
    RevShare = txtRevShare.Value
End If

For y = 1 To lstChannel.ListCount
    If lstChannel.Selected(y) = True Then
    Channel = lstChannel.Column(0, y)
    Else
    Exit For
    End If
    
            For x = 0 To lstCountry.ListCount - 1
                If lstCountry.Selected(x) = True Then
                Country = lstCountry.Column(0, x)
                Sql = "INSERT INTO tblPartnersets ([Partner name], ChannelID, CountryID, HostingID, [Revenue share]) Values (" & PName & ", " & Channel & ", " & Country & ", " & HostingID & ", " & RevShare & ");"
                Debug.Print Sql
                DoCmd.RunSQL Sql
                End If
            Next x
Next y
DoCmd.SetWarnings (-1)
End Sub

However, I'm encountering problems when either cboHostingID or txtRevShare is left blank - I was getting "invalid use of null" to start with, so I tried using IsNull as above and now I get violation errors when Access tries to write the record - I know it's to do with the actual values that are trying to be written (or in this case, not written) to the fields in question. How do I get around this?

For example, the in the tblPartnersets table, HostingID can be empty, but if I try to write nothing to it (i.e. having an unfilled combobox) then I get errors.

 

[namliam]

When trying to determain if some field is blank allways check for empty strings OR null

i.e.
IsNull(cboHostingID) or cboHostingID = ""

P.s. .Value is default, so you dont need to use it.

 

[CyberLynx]

Instead of:

If IsNull(cboHostingID.Value) Then
    HostingID = 0
    Else
    HostingID = cboHostingID.Value
End If

You can shorten this up by using the Nz() function, like this:

HostingID = Nz(Me.cboHostingID, 0)

In Your INSERT INTO query, does the Table Field [Partner name] contain a Text DataType? If so, then the VALUE in the SQL string is missing apostrophes.

.

 

[zfind]

Thanks for the tips guys. I worked it out by doing the following (a bit unglamourous but it works and was easy):

Private Sub cmdSave_Click()
Dim Sql As String
Dim PName As String
Dim Channel As Integer
Dim Country As Integer
Dim HostingID As Integer
Dim RevShare As Double
Dim RecordCount As Integer
Dim blnHosting As Boolean
 
'DoCmd.SetWarnings (0)
 
'''halt if partner name is empty
If IsNull(cboPName.Value) Then
    MsgBox "Partner name cannot be left blank"
    Exit Sub
Else
    PName = Chr(34) & cboPName.Value & Chr(34)
End If
 
'''if hostingID is blank, set blnHosting to true so alternative query can be used
If IsNull(cboHostingID) Then
 [B]   blnHosting = True[/B]
Else
    HostingID = cboHostingID.Value
End If
 
'''if revshare is empty set it to zero so errors won't occur
If IsNull(txtRevShare.Value) Then
    RevShare = 0#
Else
    RevShare = txtRevShare.Value
End If
 
'''loop through channel listbox and if selected, store to channel variable
For y = 0 To lstChannel.ListCount
    If lstChannel.Selected(y) = True Then
    Channel = lstChannel.Column(0, y)
    Else
    Exit For
    End If
 
            '''loop through country box and store the selected values to country and
            '''write the query to sql using the appropriate query format
            For x = 0 To lstCountry.ListCount
                If lstCountry.Selected(x) = True Then
                Country = lstCountry.Column(0, x)
                [B]If blnHosting = True[/B] Then
                    Sql = "INSERT INTO tblPartnersets ([Partner name], ChannelID, CountryID, HostingID, [Revenue share]) Values (" & PName & ", " & Channel & ", " & Country & ", " & [B]"Null"[/B] & ", " & RevShare & ");"
                Else
                    Sql = "INSERT INTO tblPartnersets ([Partner name], ChannelID, CountryID, HostingID, [Revenue share]) Values (" & PName & ", " & Channel & ", " & Country & ", " & HostingID & ", " & RevShare & ");"
                End If
 
                Debug.Print Sql
                DoCmd.RunSQL Sql
                RecordCount = RecordCount + 1
                End If
            Next x
Next y
 
MsgBox ("Partnerset created. " & RecordCount & " records added")
DoCmd.SetWarnings (-1)
 
End Sub

With changes highlighted for clarity. It is putting out perfect strings now and was working perfectly (including the Partner name field - the chr(34) adds the quotes)

I say was because, after I imported the form from my test db to my production db, it just wouldn't do anything. It would get to the end and just say 0 records created in my msgbox. Closed down, restarted, played around and now it's kind of working again but it seems that code only works if the first value in lstChannel listbox is selected - what the??

EDIT: To clarify, it will only see the other four items in lstChannels as 'selected=true' if the first item is selected, even if the other four are selected.

 

[zfind]

Solved.

Went from this:

For x = 0 To lstCountry.ListCount

to this

For Each y In lstChannel.ItemsSelected

I don't know why it fixed it, I guess it's more precise. It works great now anyway.

 

[namliam]

The Listcount loops thru all the items in your list, while ItemsSelected only goes thru the ones you selected.

Using ItemsSelected should allow you to remove the "if selected = true" part