The code goes like this -

Private Sub ImportCmdbut_Click()

    Dim rsS As ADODB.Recordset
    Dim rsD As ADODB.Recordset
    Dim sSQLS As String
    Dim sSQLD As String
    Dim sCol1 As String
    Dim sCol2 As String

    sCol1 = Me.Combo0
    sCol2 = Me.Combo1

    If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = ""
    Else
        sCol1 = Me.Combo0
    End If

then goes the code to open source and destination recordset and then

    rsS.MoveFirst
    Do Until rsS.EOF
        rsD.AddNew
        rsD("Plan_ID") = rsS(sCol1)
        rsD("Plan_Name") = rsS(sCol2)
        rsS.MoveNext
    Loop

    rsD.UpdateBatch

    rsS.Close
    rsD.Close

in the table, both Plan_ID and Plan_name are of type text and are not required.
on the form i do not select a value for the first combobox (combo0) and i get the error "invalid use of null" at sCol1 = Me.Combo0

This is wrong:

 Dim rsS As ADODB.Recordset
    Dim rsD As ADODB.Recordset
    Dim sSQLS As String
    Dim sSQLD As String
    Dim sCol1 As String
    Dim sCol2 As String

    sCol1 = Me.Combo0
    sCol2 = Me.Combo1

    If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = ""
    Else
        sCol1 = Me.Combo0
    End If


The reason is that you are taking the sCol1 value twice. You need to eliminate this code:

    sCol1 = Me.Combo0
    sCol2 = Me.Combo1

since this code takes care of it while checking for Nulls:

    If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = ""
    Else
        sCol1 = Me.Combo0
    End If

Right? In the sCol1 = Me.Combo0 you are taking a value without business rules, or data integrity rules. So I gave you additional code to replace that line with the If IsNull()... which checks for data integrity. So by taking the value once without rules and then taking it again with rules, you may be throwing an error (from the first set of code).

Try that.


mmcdonal

I made some changes to the code and now i'm getting error 3265 "item cannot be found in the collection corresponding to the requested name or ordinal"

Any clues on this error ?

Can you post all of the code?

mmcdonal

here it is -

Private Sub ImportCmdbut_Click()

    Dim rsS As ADODB.Recordset
    Dim rsD As ADODB.Recordset
    Dim sSQLS As String
    Dim sSQLD As String
    Dim sCol1 As String
    Dim sCol2 As String

' to allow for null selection
    If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = ""
    Else
        sCol1 = Me.Combo0
    End If

    sCol2 = Me.Combo1

    ' Open source recordset
    Set rsS = New ADODB.Recordset
    sSQLS = "SELECT * FROM TempTbl"
    rsS.Open sSQLS, CurrentProject.Connection, adOpenDynamic, adLockReadOnly

    ' Open destination recordset
    Set rsD = New ADODB.Recordset
    sSQLD = "SELECT * FROM Consolidated"
' rsD.Open sSQLD, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    rsD.Open sSQLD, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    rsS.MoveFirst
    Do Until rsS.EOF
        rsD.AddNew
        rsD("Plan_ID") = rsS(sCol1)
        rsD("Plan_Name") = rsS(sCol2)

        rsS.MoveNext
    Loop

    rsD.UpdateBatch

    rsS.Close
    rsD.Close

End Sub

when i do not select anything for combo0 then i get the error 3265 at rsD("Plan_ID") = rsS(sCol1)

Then you will need to do this:

    If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        MsgBox "Please select Column 1", vbCritical
        Exit Sub
    Else
        sCol1 = Me.Combo0
    End If

Or, you will need to add this code:

If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = 'select some column name for the user instead.
    Else
        sCol1 = Me.Combo0
    End If


Alternatively, in the combo boxes, you can choose the default value to display if the user does not select a value. That way there will always bo some selection.

The real problem is, suppose they select Column1 to match up with "CustomerName" for example, and then choose Column2 to match up with "CustomerName" as well. You will need to check for dupes.

Did that help?

mmcdonal

but i want the selection for combo0 is be 'not required'.
so at times there will be no appropriate selection for combo0.

the destination table has a column "Plan_Id" and on the form i have a combo box combo0 (with a caption "Plan_Id") which populates all the fields of source table.
what i am trying to do is allow user to select a column of source table (from combo box) which corresponds to the destination table.
but there may not always be a match so the user would not select anything for "Plan_Id" from the combo box in which case no data would be transferred to the Plan_Id column in destination.

so i want the user to be able to select nothing in combo0 if he wants that. Is there a way to do that ?

Yes, that is how we had this here:

If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = ""
    Else
        sCol1 = Me.Combo0
    End If


But let's assume your user does not make a choice, and the value for sCol1 is "" or empty.

Then when you run this part of the code, it looks like this:

Do Until rsS.EOF
        rsD.AddNew
        rsD("Plan_ID") = rsS("")
        ...

        rsS.MoveNext
    Loop

Since there is no Column in the recordset rsS that is called "", the database tells you it couldn't find the column called "", or there isn't one. So it stops processing.

What you will need to do in that case is to provide a default value, OR you will need to remove it from processing if it is "". That might look like this:

Do Until rsS.EOF
        rsD.AddNew
        If sCol1 <> "" Then
           rsD("Plan_ID") = rsS(sCol1)
        End If
        If sCol2 <> "" Then
           rsD("Plan_Name") = rsS(sCol2)
        End If
        ...
        rsS.MoveNext
    Loop

If you were doing this, then if the user choses only 3 of the 5 columns, for example, then only those 3 columns will be moved to the destination table, not all of the columns. So if you source record was:

Col1 Col2 Col3 Col4 Col5
001 User Sample Dog Cat

And your user only chooses the first three columns, then in the destination table you will get this record:

Col1 Col2 Col3 Col4 Col5
001 User Sample <Null> <Null>

That may not be what you want, which is why I suggested either default values, OR make the user choose every column.

HTH



mmcdonal

That works.

Thank you so much for all the help. I really appreciate it.
You are the best.

Hi rashi,

Please follow this link to read about your error code

http://support.microsoft.com/kb/223212/en-us

mmcdonel is your man for ADO problems

Martin

Tell them they will hear,
Show them they will see,
Let them experience and they will learn