Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 3rd, 2007, 02:19 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 4th, 2007, 10:55 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 4th, 2007, 11:52 AM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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 ?

 
Old October 4th, 2007, 11:58 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Can you post all of the code?

mmcdonal
 
Old October 4th, 2007, 12:06 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)




 
Old October 4th, 2007, 12:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 4th, 2007, 12:41 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ?


 
Old October 4th, 2007, 12:59 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 4th, 2007, 01:11 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That works.

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

 
Old October 5th, 2007, 08:43 AM
Registered User
 
Join Date: Oct 2006
Location: Leigh, Lancashire, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
Ch 4: Parse error: syntax error, unexpected T_SL hanizar77 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 June 23rd, 2008 09:17 PM
[Resolved] Error calling a sp - parameter error snufse .NET Framework 2.0 2 February 12th, 2008 04:46 PM
Parse error: syntax error, unexpected T_STRING ginost7 Beginning PHP 1 November 9th, 2007 02:51 AM
Phile Page error, visual studio error reps BOOK: ASP.NET Website Programming Problem-Design-Solution 0 September 27th, 2003 10:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.