Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2008
    Posts
    15

    Unanswered: if fields not null other fields insert into

    Hi;

    I have a table name is "customers"

    id: Auto number
    Customer : Text
    1smny: Number
    2smny : Number
    3smny:Number
    ....
    .....
    23smny: Number

    _____________________

    And i have a field and a save button on the form, field name is smny;
    I want insert into smny value in the customers table but if 1smny not null then 2smny or i can tell i want insert into at first null smny fileds on the customers table..

    Thank you..

    Edit: Sorry not insert into, i want update set... Because not new record only update null fields.
    Last edited by leexlee; 04-27-08 at 09:20.

  2. #2
    Join Date
    Mar 2008
    Posts
    15
    Im sorry, not insert into, will be update set..

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should abandon this design, it is not in first normal form

    instead, create another table with 2 columns: customer_id and smny

    then you can have multiple smnys per customer using multiple rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2008
    Posts
    15
    Yes i know and i understand you , you're right but i can't.... and i did mistake my first question.. So how can i update null field, for example if not null 1. field then i will update 2. field
    or
    1 and 2. field is not null then i will update 3. fileds. Only null field..
    etc..
    etc..
    Last edited by leexlee; 04-27-08 at 09:47.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The answer is: don't.

    I'd say the only way to do that is with one huge VBA statement, but I agree that the only 'right' thing to do is get the design fixed.
    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

  6. #6
    Join Date
    Mar 2008
    Posts
    15
    Code:
    Dim rs As DAO.Recordset
    Dim bbsql As String
    
    On Error Resume Next
    
        bbsql = "SELECT * FROM customers"
    
        Set rs = CurrentDb.OpenRecordset(bbsql)
       For i = 1 To 23  'for 1spny, 2spny,....,23spny
        rs.MoveFirst
        
        Do Until rs.EOF
      If IsNull(i & "spny") Then
       rs.Edit
            rs.Fields(i & "spny") = Me.text10 ' text10 is field name on the form
            
            rs.Update
            rs.MoveNext
      Next i
      End If
    Loop
    But i can't .. Maybe u don't understand to me because my English very bad

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by leexlee
    Yes i know and i understand you , you're right but i can't....
    Do you understand what R937 means by First Normal Form violation?

    We understand what you want to do. What we are saying is that what you want to do is wrong because you have not designed your table correctly.

    If you understand what R937 means please could you then just explain why you cannot correct the design?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2008
    Posts
    15
    Yes i did and i will create new table for multiple rows. Be sure.. I did understand better..
    And thank you so much...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Our pleasure. If you have a spare hour or so read and absorb this - it will be time well spent:
    http://www.tonymarston.net/php-mysql...se-design.html

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was waiting to see how long it would take poots to link dump Tony Marston for his commission
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Damn you George V - damn you to hell!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, go read the Corral; I'm already there
    George
    Home | Blog

Posting Permissions

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