Results 1 to 15 of 15

Thread: VBA Code

  1. #1
    Join Date
    Aug 2003
    Posts
    160

    Unanswered: VBA Code

    Could anybody say what is wrong with the code I have written to send information from Form to a table using VBA code.

    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    rs.AddNew
    rs("Lastname") = Me.Lastname
    rs("Firstname") = Me.Firstname
    rs("Address1") = Me.Address1
    rs("Address2") = Me.Address2
    rs("Zipcode") = Me.Zipcode
    rs("State") = Me.State
    rs("Country") = Me.Country

    End Sub

    Thanks
    Vad

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What is the error you're getting?

  3. #3
    Join Date
    Aug 2003
    Posts
    160
    It is giving me error like "User-Defined Type Not Defined"

    Dim db As DAO.Database pointing towards this statement

    Thanks


    Originally posted by M Owen
    What is the error you're getting?
    Vad

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    rs.AddNew
    rs!Lastname = Me.Lastname
    rs!Firstname = Me.Firstname
    rs!Address1 = Me.Address1
    rs!Address2 = Me.Address2
    rs!Zipcode = Me.Zipcode
    rs!State = Me.State
    rs!Country = Me.Country
    rs.Update
    Close rs: Close db

    End Sub


    OR TRY THIS:
    ============


    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    With rs
    .AddNew
    !Lastname = Me.Lastname
    !Firstname = Me.Firstname
    !Address1 = Me.Address1
    !Address2 = Me.Address2
    !Zipcode = Me.Zipcode
    !State = Me.State
    !Country = Me.Country
    .Update String)
    End With
    Close rs: Close db

    End Sub
    Last edited by CyberLynx; 11-17-03 at 16:19.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ah ... Make sure you have the appropriate references ... What version of Access is this?

  6. #6
    Join Date
    Aug 2003
    Posts
    160
    I am using Access2000. How about references Just now I included DAO and it is working fine. How to include ADODB. I have several like Microsoft Activex Data Objects 2.7 Library,Microsoft Activex Data Objects 2.1 Library,Microsoft Activex Data Objects 2.3 Library. Which one I have to choose.

    I have choosen 2.7 and is not working. I declared the variables like

    Dim db as ADODB.Database
    Dim rs as ADODB.Recordset

    It is giving error "User Defined Type Not Defined"

    What are the general things I have to Include in the reference library.

    Thanks

    Originally posted by M Owen
    Ah ... Make sure you have the appropriate references ... What version of Access is this?
    Vad

  7. #7
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Prube that CyberLynx say

    Originally posted by CyberLynx
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    rs.AddNew
    rs!Lastname = Me.Lastname
    rs!Firstname = Me.Firstname
    rs!Address1 = Me.Address1
    rs!Address2 = Me.Address2
    rs!Zipcode = Me.Zipcode
    rs!State = Me.State
    rs!Country = Me.Country
    rs.Update
    Close rs: Close db

    End Sub


    OR TRY THIS:
    ============


    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    With rs
    .AddNew
    !Lastname = Me.Lastname
    !Firstname = Me.Firstname
    !Address1 = Me.Address1
    !Address2 = Me.Address2
    !Zipcode = Me.Zipcode
    !State = Me.State
    !Country = Me.Country
    .Update String)
    End With
    Close rs: Close db

    End Sub
    Saludos
    Norberto

  8. #8
    Join Date
    Aug 2003
    Posts
    160
    I am sorry! I didn't get u what u r trying to say.

    Thanks!


    Originally posted by Norberto
    Prube that CyberLynx say
    Vad

  9. #9
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Do like CyberLynx say
    Saludos
    Norberto

  10. #10
    Join Date
    Aug 2003
    Posts
    160
    Hi! right now I am getting the information with DAO.

    But if I want to try with ADODB I am not getting.

    How to include ADODB. I have several like Microsoft Activex Data Objects 2.7 Library,Microsoft Activex Data Objects 2.1 Library,Microsoft Activex Data Objects 2.3 Library. Which one I have to choose.

    I have choosen 2.7 and is not working. I declared the variables like

    Dim db as ADODB.Database
    Dim rs as ADODB.Recordset

    It is giving error "User Defined Type Not Defined"



    Originally posted by Norberto
    Do like CyberLynx say
    Vad

  11. #11
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry but i use Access 97
    Saludos
    Norberto

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Vad,

    Choose the library that was installed with the MDAC.

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by vad
    Hi! right now I am getting the information with DAO.

    But if I want to try with ADODB I am not getting.

    How to include ADODB. I have several like Microsoft Activex Data Objects 2.7 Library,Microsoft Activex Data Objects 2.1 Library,Microsoft Activex Data Objects 2.3 Library. Which one I have to choose.

    I have choosen 2.7 and is not working. I declared the variables like

    Dim db as ADODB.Database
    Dim rs as ADODB.Recordset

    It is giving error "User Defined Type Not Defined"

    Its the ADODB.Database line that is getting you.

    No such thing.

    Use a connection object and open your recordset using the connection for the recordset's active connection property.

    You can use CurrentProject.Connection for the argument if you like.

    Gregg

  14. #14
    Join Date
    Aug 2003
    Posts
    160
    Hi! Greg,

    Could explain me the connection object based on the code I provided in the firstpost.

    Thank U !

    Originally posted by basicmek
    Its the ADODB.Database line that is getting you.

    No such thing.

    Use a connection object and open your recordset using the connection for the recordset's active connection property.

    You can use CurrentProject.Connection for the argument if you like.

    Gregg
    Vad

  15. #15
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by vad
    Hi! Greg,

    Could explain me the connection object based on the code I provided in the firstpost.

    Thank U !

    Vad,

    Use the debug window or a message box to display the CurrentProject.Connection. This is how the connection information is formatted. When you open a new connection you will need to provide at least the Provider and Data Source arguments.

    You can set a variable = to a new connection and then provide the information in the recordsets Open method also.

    If you want to get going quick and it is not a remote database though, use the CurrentProject.Connection to open a recordset with. You are basically using an existing connection. There is no need to open a connection object necessarily.

    Dim rs as ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Open "TableName", CurrentProject.Connection, etc.....

    Try it and see if it works.

    Try the help system for more information on the recordset and connection objects.

    As far as the code from your first post, just ad the Update method after the field assignments.

    Gregg
    Last edited by basicmek; 11-18-03 at 15:23.

Posting Permissions

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