Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Form/Fields/Macro problem

    I have this form for purchases
    It has
    TransactionID(autonumber)
    Customer
    (From a table)

    Then underneath it has
    LISTBOX (of all parts)
    Quantity (Textbox)

    What i want to do is transit the purchaseID as a plain number, this listbox and quantity to another table. However, this doesnt work!

    INSERT INTO Transaction_Parts (Transaction, PartID, Quantity) Values ([Forms]![frmPurchases]![TransactionID],[Forms]![frmPurchases]![PartID], [Forms]![frmPurchases]![Quantity]);
    Thats my insert into query.

    It asks me to append. I hit yes and the following error comes up
    http://img296.imageshack.us/img296/7828/problemxd9.png

    What would the problem be?
    Last edited by brett_lee; 10-28-08 at 07:03.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it says you have a conversion error and a null value error

    the most likely candidates are going to be
    either a typo (ie the contrl doenst' actuall exist as typed
    the control you are referenceing as doesn't have a value
    or you need to do a conversion or explicit cast from satring to a number
    eg clng([Forms]![frmPurchases]![TransactionID])

    id want to see what the SQL that is actually beign sent to SQL engine is (ie the forms references expressed as actual values). if you cannot do that with the SQL directgly then examine the values by settign a break point and looking at the values in the immediate window
    eg "?[Forms]![frmPurchases]![TransactionID]"

    OR put a message box displaying the vlaues

    eg
    msgbox( "TX ID:" & [Forms]![frmPurchases]![TransactionID] & vbcrlf & .....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    it says you have a conversion error and a null value error
    No it doesn't. It says you have 6 validation rule violations. The destination table has a validation rule in place that is violated by 6 records when you are trying to transfer your data.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2008
    Posts
    2
    its a problem to do with the listbox as it pops up with a enter parameter value box which says
    Forms!frmPurchases!PartID

    its obviously having trouble recognising the listbox because when i type in the selected value of the listbox into the paramater box, it actually puts it into the table. So how can i get it to recognise teh listbox

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the list box called?
    is it called PartID?
    or is it called something else?

    have you checked what values are in the listboxes that you are referring to
    are you certain that you are using EXACTLY the same name as the controls on the form

    the fact its asking for a parameter suggests to me that there is a typo

    using the message box technique is very effective to make sure that what values exist, especially if you aren't to up to speed on debugging steps.

    displaying the actual SQL sent to the SQL engine is another vital debugging technique
    quite often what you see in code isn't actually what is sent to the SQL.

    always use Option Explicit
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    always use Option Explicit
    Definitely agreed there... I can't remember how many times this has saved me from having to find "silent" typos that would create some very weird bugs.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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