Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: OpenRecordSet Cmd

    Having trouble with the openrecordset cmd. I keep getting the error data mistype. This is the code

    Dim Row_count As Integer
    Dim Cur_DB As Database
    Dim Record_Set As Recordset
    Dim Count_of_records As String
    Dim Quote As String

    Quote = Chr$(34)

    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.OpenRecordset("select user_id from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    test.Value = Record_Set

    any Ideas?
    Jim

  2. #2
    Join Date
    Nov 2002
    Posts
    10

    Set Record_Set

    I am not sure what "test" is, but Record_Set is an object so you have to use
    Set test.Value = Record_Set

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    ok i have taken out the test.value thing and i put the virable in the query as follows

    Dim Row_count As Integer
    Dim Cur_DB As Database
    Dim Record_Set As Recordset
    Dim Count_of_records As String
    Dim Quote As String


    Quote = Chr$(34)

    Count_of_records = "select user_id from [Table:_PCRKMS_User_Defaults]"

    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.OpenRecordset("select user_id from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    'Set test.Value = Record_Set

    DoCmd.RunSQL "INSERT INTO dbo_DM_LINE_PCRKMS_USAGE_LOG_TBL " _
    & "( User_ID,User_Phone, User_E_Mail, Version, Start_DateTime, Stop_DateTime,Rows_Returned ) SELECT [Table:_PCRKMS_User_Defaults].User_Id,[Table:_PCRKMS_User_Defaults].User_Phone_Number,[Table:_PCRKMS_User_Defaults].User_MCS_Address, [Table:_PCRKMS_User_Defaults].Version_Number, #" & starttime & "#, #" & stoptime & "#" & Quote & Record_Set & Quote & " FROM [Table:_PCRKMS_User_Defaults];"


    Now i get the msg type mismatch and it highlights where the vairable is inserted into the sql statment. Does that mean that I cant imbed a recordset variable there and if so what can I do?
    Jim

  4. #4
    Join Date
    Nov 2002
    Posts
    10

    It begins to come clear

    Your variables: starttime, stoptime are not defined and no values are assigned to them.
    I also do not understand how you are using the variable: Record_Set.
    A recordset is an object that is much like a two dimensional array.
    With the criteria you supplied you should be able to referrence the value by:
    Record_Set.fields("user_id").value
    The shorter form of this is:
    Record_Set("user_id")
    fields is the default collection for a recordset and value is the default property for a field.

    One additional piece of information that might help you is that VB converts two successive quotes into one inside a string.
    i.e.
    "Jim''s quote ("")" would be interpreted "Jim's quote(")"

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: It begins to come clear

    First off this small pice of code is at the botom of a much larger on_click operation, so the start and stop variables are set above. Secondly are you saying that if i want to use whats in the recordset after I have set it I need to say
    Record_set.("User_ID").value ?
    so basicaly id put the above code into my query where I needed the variable?
    Thanx for all your help clearing this up
    Regards
    Jim
    Originally posted by Pelman
    Your variables: starttime, stoptime are not defined and no values are assigned to them.
    I also do not understand how you are using the variable: Record_Set.
    A recordset is an object that is much like a two dimensional array.
    With the criteria you supplied you should be able to referrence the value by:
    Record_Set.fields("user_id").value
    The shorter form of this is:
    Record_Set("user_id")
    fields is the default collection for a recordset and value is the default property for a field.

    One additional piece of information that might help you is that VB converts two successive quotes into one inside a string.
    i.e.
    "Jim''s quote ("")" would be interpreted "Jim's quote(")"

  6. #6
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    Does this SQL qeury work in a query screen(Not Form) if so there's no problem if so there is.

    I think its wrong anyway,because you didnt give it a database to relate too. There are 2 options.

    1: Set Record_Set = Cur_DB.OpenRecordset("select table.[user_id] from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    In which its in a table

    2: Set Record_Set = Cur_DB.OpenRecordset("select Table." & user_id & "from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    in which you use a text box
    Last edited by DeathWing; 11-22-02 at 09:52.

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thank you every one!!!!!!!!!
    here is the answer

    Dim Cur_DB As Database
    Dim Record_Set As Recordset


    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.OpenRecordset("select count(*) as count from [Table_PCRKMS_Local_Data]", dbOpenDynaset)

    DoCmd.RunSQL "INSERT INTO dbo_DM_LINE_PCRKMS_USAGE_LOG_TBL " _
    & "( User_ID,User_Phone, User_E_Mail, Version, Start_DateTime, Stop_DateTime,Rows_Returned ) SELECT [Table:_PCRKMS_User_Defaults].User_Id,[Table:_PCRKMS_User_Defaults].User_Phone_Number,[Table:_PCRKMS_User_Defaults].User_MCS_Address, [Table:_PCRKMS_User_Defaults].Version_Number, #" & starttime & "#, #" & stoptime & "#, " & Record_Set("count") & " FROM [Table:_PCRKMS_User_Defaults];"


    Once I knew how to referenct the recordset it was easy. Ill be damned if ms help has no info on that!!!!!!
    Thank you again for all of your help!!!!!!
    Regards
    Jim

    Originally posted by DeathWing
    Does this SQL qeury work in a query screen(Not Form) if so there's no problem if so there is.

    I think its wrong anyway,because you didnt give it a database to relate too. There are 2 options.

    1: Set Record_Set = Cur_DB.OpenRecordset("select table.[user_id] from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    In which its in a table

    2: Set Record_Set = Cur_DB.OpenRecordset("select Table." & user_id & "from [Table:_PCRKMS_User_Defaults]", dbOpenDynaset)

    in which you use a text box

Posting Permissions

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