Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Sheffield U.K.
    Posts
    2

    Unanswered: Updating tables from a Module

    Hi there,

    I'm building a simple data entry form for putting values into my database and I have a Module in one of my forms to control all the buttons and lables etc.

    I wish to have a button that inserts values into a new record in a different table, not the one the form is based on.

    I'm sure this should be relatively simple, but for some reason I just can't seem to figure quite how to do it.

    In the code for the form MediaItem (based on the table MediaItem) I tried this code:

    Forms![publicationrelationship].Item = ID.Value
    Forms![publicationrelationship].relationship = Combo62.Value
    Forms![publicationrelationship].Organisation = List50.Value

    And although a form called publicationrelationship exists (based on a thable publicationrelationship) I get a form not found error...

    ... so I tried....

    Dim dataadd As New Recordset
    dataadd.DataSource = publicationrelationship
    dataadd.AddNew
    dataadd.Fields("item") = ID.Value
    dataadd.Fields("organisation") = List50.Value
    dataadd.Fields("relationship") = Combo62.Value
    dataadd.Update

    Obviously I have an error here too as I get "method or data member not found"


    I think, if I could get it to work, I would prefer to use the latter method of data entry, as with a recordset type control I think I'd feel I was more in control of the table.


    Alternativly, is there a way to execute an SQL command like INSERT from the script and manipulate data this way?

    Any pointers in the right direction would be useful...

    Many thanks

    -K-

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    Code:
    Dim dataadd As New Recordset
    dataadd.Open "publicationrelationship",Codeproject.Connection,adOpenDynamic, adLockOptimistic
    dataadd.AddNew
    dataadd.Fields("item") = ID.Value
    dataadd.Fields("organisation") = List50.Value
    dataadd.Fields("relationship") = Combo62.Value
    dataadd.Update
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Posts
    199
    by the way which data model do you use, ADO or DAO?
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I generally use ADO as it needs less lines of code.

    Originally posted by Kakha
    by the way which data model do you use, ADO or DAO?

  5. #5
    Join Date
    Apr 2004
    Location
    Sheffield U.K.
    Posts
    2

    Talking

    Thanks very much for the swift response Kakha!

    I'm not sure if I'm using ADO or DAO... I'm working entirely in Access on tables that have been linked from a remote MySQL database MySQL

    The code you provided has solved the problem.. and now I can connect to the recordset I should be able to have all sorts of fun with it!


    Thanks again,

    -K-

Posting Permissions

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