Results 1 to 6 of 6

Thread: Please Help

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Please Help

    I am really new and green to Access and VBA, My experience amounts to about 6 weeks. I've benn building an application for our merchandise billing. I've found quiet a few answers to questions by browsing the dbforums sites, however this is my first post. I keep receiving the object or variable not set error and it sends me to the attached line. I have A specialinstructions table a one to many relation to my customer transactions table with a one to many relation to my products table. the only way I've found to get the data to relate like i want it to is to add the special instructions identifier field to the customers table and a customer identifier to the products table. I've then wrote code to automatically populate these fields on their respective forms. I transfer and I'm able to save from the special instructions form to the customer form, and I am able to transfer the customer info to the product form but when i try to save the product details I get the attached error and it shoots me to this line of code. Any answers as to why would be greatly appreciated.

    Thanks,
    Product error 1.bmp

    product error 2.bmp

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You have not defined or assigned a value to rsProduct. I assume it's a record set. You should have a Dim statement to tell Access what type of variable it is (ADO.Recordset or DAO.Recordset) and an open record set statement (look up pen record set in VBA help).

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Dim rsProduct As ADODB.Recordset


    I have the above dim in the general declarations,
    the below code runs after the disconnected savecurrentrecord is called

    Sub SaveAllRecords()

    'Save current Record to local disconnected recordset
    Call SaveCurrentRecord

    'create a new connection instance and open it using the connection string
    Set cnBilling = New ADODB.Connection
    cnBilling.Open strConnection

    'set the disconnected recordset to the reopened connection
    Set rsProduct.ActiveConnection = cnBilling

    'save all changes in the local disconnected recordset back
    'to the database
    rsProduct.UpdateBatch

    'disconnect the recordset again
    Set rsProduct.ActiveConnection = Nothing

    'close the database connection and release it from memory
    cnBilling.Close
    Set cnBilling = Nothing

    End Sub

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without seeing all the code my guess is that rsproduct should be defined as a global variable for that form
    it should be set somewhere
    it should be used wherever required
    and it should be unset set to nothing when the form exits
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    Now I am getting Item cannot be found in the collection corresponding to the requested name or ordinal. When I debug it takes me to the line after the error from earlier. I end that and then try to save and it gives me the object block error.
    I am not familiar with global variables. How do I set that would it be in form of a class module?

    Sub SaveCurrentRecord()

    'save the values in the controls on the form to the current record
    'in the local disconnected recordset.
    If Not rsProduct.BOF And Not rsProduct.EOF Then
    rsProduct!CostCenter = Me.CostCenter
    rsProduct!VendorStockNumber = Me.VendorStockNumber
    rsProduct!Description = Me.Description
    rsProduct!UOM = Me.UOM
    rsProduct!UnitPrice = Me.UnitPrice

    End If

    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so
    I'd suggest you find out what is in the collection
    as the code has stopped type in the immediate/code window and make sure the recordset contains the data you expect it to have
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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