Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: need help with this code

    SQL Server Desktop Version as BE & Access mdb as FE environment.

    New bee at SQL Server db.

    I am trying to get the desired record set on the Form1 upon clicking a command button after entering RolNo in text0. But it returns first record set only. Please look into the below code and let me know how can I put where condition into it.
    The RolNo is not duplicate in the table T_RollMaster. Also there is no password for the server db. The fields on server table are set with following datatype.

    RolNo=nvarchar
    RollSize=nvarchar
    GSM=float

    I know the method thru ODBC linked table. But I am trying with ADODB.

    I would appreciate if somebody could help me.

    Private Sub Command2_Click()
    Dim conn As New ADODB.Connection
    Dim Rec1 As New ADODB.Recordset

    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseClient
    conn.Provider = "Microsoft Sql OlEDB 4.0"
    conn.ConnectionString = " Provider=MSDASQL.1;Persist Security Info=False;Data Source=GCF;Initial Catalog=PaperRollBE"
    conn.Open conn


    Set Rec1 = New ADODB.Recordset
    Rec1.CursorLocation = adUseClient
    Rec1.CursorType = adOpenDynamic
    Rec1.LockType = adLockOptimistic
    Rec1.ActiveConnection = conn

    Rec1.Open "Select T_RollMaster.RolNo, T_RollMaster.Rollsize, T_RollMaster.GSM from T_RollMaster"
    'Text0 = Rec1.Fields("ROLNO")
    Me.Text9 = Rec1.Fields("Rollsize")
    Me.Text11 = Rec1.Fields("gsm")
    Rec1.Close
    Set Rec1 = Nothing
    End Sub

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "WHERE" conditions are part of a SQL statement. You use a SQL statment when you open a recordset. Therefore the line you want is:

    Rec1.Open "Select T_RollMaster.RolNo, T_RollMaster.Rollsize, T_RollMaster.GSM from T_RollMaster WHERE <yourCriteria>"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks,

    When I made the code :
    Rec1.Open "Select T_RollMaster.rolNo, T_RollMaster.Rollsize, T_RollMaster.GSM " & _
    "from T_RollMaster Where rolNo= Forms!Form1!text0"

    It gave me syntax error

    I little bit then modified the code like

    Rec1.Open "Select T_RollMaster.RolNo, T_RollMaster.Rollsize, T_RollMaster.GSM " & _
    "from T_RollMaster Where RolNo = " & Forms!Form1!Text0

    It produced :

    Run-Time error '-2147217900 (80040e14)':
    [Microsoft][SQL Server Driver][SQL Server] Invalid column name 'FLD25992'

    FLD25992 is a RolNo which I am entering in Text0 on the form.

    Please extend your help
    Last edited by Ashfaque; 12-09-06 at 07:14.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm gonna move this to Access.

    Assuming rolno is numeric:
    Code:
    Rec1.Open "Select T_RollMaster.rolNo, T_RollMaster.Rollsize, T_RollMaster.GSM " & _
    "from T_RollMaster Where rolNo= " & Forms!Form1!text0
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - you edited after I replied

    Then:
    Code:
    Rec1.Open "Select T_RollMaster.rolNo, T_RollMaster.Rollsize, T_RollMaster.GSM " & _ "from T_RollMaster Where rolNo= '" & Forms!Form1!text0 & "'"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thats fine pootle.

    I appreciate it. Thanks.

    One more thing; incase if there are duplicate RolNo (other field's data are different) in same table and I need to list them out in a listbox or a combo box, should the following code work out?

    If Rec1.RecordCount >= 1 Then
    Do While Rec1.EOF = False
    Combo5 = Rec1.Fields("ROLNO")
    List7 = Rec1.Fields("GSM")
    Rec1.MoveNext
    Loop
    End If

    But show only single rec in combo and nothing in list box. Whats wrong here?

    Please correct me.

    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Still Waiting...

  8. #8
    Join Date
    Nov 2005
    Posts
    113
    I think you need to use RowSource on the combo and listbox:

    If Rec1.RecordCount >= 1 Then
    Do While Rec1.EOF = False
    Combo5.RowSource = Combo5.RowSource & ";" & Rec1.Fields("ROLNO")
    List7.RowSource = List7.RowSource & ";" & Rec1.Fields("GSM")
    Rec1.MoveNext
    Loop
    End If

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks, But did not work.

    I hope due to tables are not in the db itself. It is reading from server. And I think we can use RowSource trick when tables are local. is'nt it?
    Last edited by Ashfaque; 12-17-06 at 08:29.

  10. #10
    Join Date
    Nov 2005
    Posts
    113
    This would work!

    Dim strSql as String
    Dim strSql2 as String

    strSql = "Select rolNo" & _
    "from T_RollMaster Where rolNo= '" & Forms!Form1!text0 & "'"

    strSql2 = "Select GSM & _
    "from T_RollMaster Where rolNo= '" & Forms!Form1!text0 & "'"

    Combo5.RowSource = strSql

    List7.RowSource = strSql2

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Thumbs down

    Again thanks Julita for your valuable time.

    Tried with your code. Not working.

    I attached my db zip file for your ready ref.
    You may just have following data in a server table (5 records only) called T_RollMaster with following fields.

    RolNo : 6133, 6133, FLB 22111 TKL 15179
    RollSize: 224.5, 189.2, 225, 136.5, 115
    GSM : 150, 150, 140, 110, 175

    As I explained T_RollMaster may have duplicate records as it is a table where I am adding data daily.

    Regards,
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2005
    Posts
    113
    Hi, Sorry you have problem with your connection to the server and I'm not god at that. I thought it was with the List and Combobox problem.

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I believe that there is no problem at all with connection. Bcz it is reading value in first 2 textboxes.

    The only thing it is not generating list of the similar records.

    Anyhow thanks for your time.

    Anybody else there ?????

  14. #14
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Still waiting for the response...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •