Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I ? (2000)

    I am new to Access - please bear with me !
    I have a field called REC_NO (Number) and another called REC_ID (Auto number). How do I have the REC_NO field copy the unique number of the REC_ID field id if the value in the REC_NO field is NULL ?
    Many thanks
    Maurice Hutton <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    You want to place the value of REC_ID in REC_NO if REC_NO is Null? I wonder why you would want to duplicate a data value within the record, since that is typically discouraged. Could you explain what you are trying to accomplish?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How do I ? (2000)

    Maurice,

    I'm not sure I follow what you are trying to do. You want to make the Rec_No field equal to the Rec_Id field? If so, then my first question is: Why do you need 2 identical fields in the same record?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    The data was imported from a dbf file supplies by a marketing bureau and contained the field REC_NO in which the contents were a unique number but not in any numeric sequence. I created a primary field (autonumber) called REC_ID and added several tables and linked them all via this primary filed. Everything works fine until the user adds a new record and then data in some of the forms do not display though they are in the main table.
    Hope this all amkes sense.
    Thanks for your prompt repsonse
    Maurice Hutton

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    The data was imported from a dbf file supplied by a marketing bureau and contained the field REC_NO in which the contents were a unique number but not in any numeric sequence. I created a primary field (autonumber) called REC_ID and added several tables and linked them all via this primary filed. Everything works fine until the user adds a new record and then data in some of the forms do not display though they are in the main table.
    Hope this all amkes sense.
    Thanks for your prompt repsonse

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    Is having the records in numerical sequence really important? None the less, if you want to update the REC_NO in a query, create an update query. Place the field REC_NO in the query. Place Is Null in the criteria row for this field and place [REC_ID] in the Update To row for this field. That will update all the Null REC_NO fields with the REC_ID value.

    If you want to do it in a form, the logic is:
    If IsNull([REC_NO]) Then
    [Forms]![YourFormName]![REC_NO] = [Forms]![YourFormName]![REC_ID]
    End If

    Be sure to make a copy of the table first, in case something goes amiss.

    I didn't understand how "Everything works fine until the user adds a new record and then data in some of the forms do not display though they are in the main table." related to your original question.

  7. #7
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    Thank you for your tips. I have tried both with no luck !
    The update query gives me the following error message "Data type mismatch in criteria expression" (REC_NO is number field and REC_ID is autonumber field)
    I did as you suggested in the form with the following code:
    Private Sub REC_NO_BeforeUpdate(Cancel As Integer)
    If IsNull ( [REC_NO] ) Then
    [Forms] ! [citywest]![REC_NO] = [Forms] ! [citywest] ! [REC_ID]
    End If
    End Sub

    It does not appear to do anything.

    Any other suggestions would be appreciated.
    Maurice Hutton

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    Is REC_NO a long integer data type?

  9. #9
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    Yes
    Regards
    Maurice Hutton

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    I created and attached a small sample db that I believe demonstrates what you are trying to accomplish.
    If you run the query, it will update the Null fields. If you open the form, as you view records, it will update the Null field. Both the query and the form use the methods I described earlier. I did it both ways, since I wasn't sure which one would best suit your purpose. I hope you find this sample helpful.
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    Many thanks for your time and effort in putting together the sample database to illustrate the code you suggested using. However, everything works fine on the data you have entered but when I enter a new record in the form (in your sample database) it does not update the UserRecNo field and if I run the query I get the message

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    The attached sample db now also updates the RecNo field for new records entered in the form. The query only updates the RecNo field when it is Null, so you will need to modify it if you want to update a RecNo field which contains other values. HTH
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Mar 2001
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I ? (2000)

    It worked ! Many thanks
    Maurice

Posting Permissions

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