Results 1 to 9 of 9
  1. #1
    MunroeM is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    55

    Forms not working correctly after changing data type to number

    Hello



    I have included a slimmed down version of my database in case you need to see for yourself what I'm about to explain.

    I have 3 tables and 3 forms (U/S, Temmis, and deployed) all three tables and forms are basically the same except for wording and tag numbers. They were all working as intended untill i noticed that the tag numbers were sorting in a fasion i did not like. it was going from 1000's to 1100's to 1200's and then 300's (1000... ,1100...,1200..., 300..) so I changed the data type from Text to Number of the field USTagNum in USTagT. It is now sorting how i would like (300..,1000..,1100...,1200...) But now there is a new issue. I verified by changing the datatype from text to number in the TemmisTagT and the problem is now happening on the temmis form also.

    I will describe how the TEMMIS form should work normally and just remember that the other two forms work the same way. When you select the Sign in button Temmis Tag In combo box becomes usable. you then select the tag number you are signing back in and it will populate the boxes below with the info for that tag.

    Now after changing the datatype to number you select sign in and then select a tag number and no info shows up in the boxes below and if you change the tag number you selected three times a microsoft access box pops up saying "Update or Cancel update without AddNew or Edit."

    I could just change the datatype back to text but then my sorting will be all off again so i was wondering if there is a way to get it working the way it used to but still keeping the datatype as number. Or if you know of another way to sort so its will not come out 1000, 1100, 1200, 300.

    If you want to play around with the database i uploaded then the Deployed form is the one that is still working the way i want it to. The Temmis and US are not working

    Thanks
    Attached Thumbnails Attached Thumbnails Form.jpg   Fields.jpg  
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As text, the values would need place holder leading zeros to properly sort: 0001, 0300, 1000, 2000, etc.

    You can construct a field in query with an expression: Format([DeployedTagNum], "0000")

    Now use that field for sorting and filtering.


    Note misspelled 'Dectription' in user instructions on your form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MunroeM is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    55
    Thanks for picking up the spelling error.

    Now if i us the Format([DeployedTagNum], "0000") is that going to change the way the numbers will show up in the combo box? Will tag 30 show up as 0030 in the drop down box?

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    June has explained how to get the sorting you want, with the Field being defined as Text.

    But FYI, the reason for the problem, when changing the Field to a Number Datatype, is how different Datatypes are delimited. Below is typical code for retrieving a Record using a Combobox.

    If the identifying Field is defined as Text, as yours was:
    Code:
    Private Sub ComboName_AfterUpdate()
      
     Dim rs As Object
    
     Set rs = Me.Recordset.Clone
     rs.FindFirst "[ID] = '" & Me![ComboName] & "'"
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    End Sub

    If defined as a Number:
    Code:
    Private Sub ComboName_AfterUpdate()
        
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Me![ ComboName]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    End Sub


    The same general syntax is used elsewhere for 'where' clauses and 'criteria' clauses.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    MunroeM is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    55
    Quote Originally Posted by June7 View Post
    As text, the values would need place holder leading zeros to properly sort: 0001, 0300, 1000, 2000, etc.

    You can construct a field in query with an expression: Format([DeployedTagNum], "0000")

    Now use that field for sorting and filtering.
    How would I go about doing this? I still have very basic knowledge in access.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It can show as 0030 if you want it to. If you build the combobox RowSource sql with the calculated field.

    What do you not understand? How to create a field in query with expression? Basic Access functionality, review http://ms-access-tips.blogspot.com/2...n-queries.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    MunroeM is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    55
    I would prefer that they only show 30 from 0030 or 340 from 0340.

    I will have a read over that link you posted and see if it helps me understand queries better.

    Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    But do you want the combobox values to sort properly? Try RowSource:

    SELECT DeployedTagNum FROM DeployedTagT ORDER BY Format([DeployedTagNum], "0000");

    or

    SELECT DeployedTagNum FROM DeployedTagT ORDER BY Val([DeployedTagNum]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    MunroeM is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    55
    Quote Originally Posted by June7 View Post
    But do you want the combobox values to sort properly? Try RowSource:

    SELECT DeployedTagNum FROM DeployedTagT ORDER BY Format([DeployedTagNum], "0000");

    or

    SELECT DeployedTagNum FROM DeployedTagT ORDER BY Val([DeployedTagNum]);
    That link you sent me worked well. I did not know you could enter all that in the field section.

    Both of the options you gave me worked. But the Format one did not work in the end as i use an append query to copy the record to a history table and it prevented the append query from working. But the Val one works like a charm

    Thanks again for helping me out

    Munroe

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-14-2015, 07:02 AM
  2. Changing Data Type of Imported Field Name
    By rhubarb in forum Import/Export Data
    Replies: 12
    Last Post: 06-16-2014, 08:20 AM
  3. Replies: 6
    Last Post: 02-12-2014, 05:16 PM
  4. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  5. Replies: 1
    Last Post: 04-24-2013, 11:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums