Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38

    Arrow Separate Active & In Active Account Numbers in to Text Box

    Hi Forum,



    I have a table tblAccounts, with TransID (Autonumber,PK), TransAccount (text), TransDate, TransTime (Text with 4 chars 0000 - 2300 format), TransAmount, TransStatus (Active or In Active).

    In an unbound form I have created a text box (Text1) with multi line enabled for entering Account Numbers. What i am trying to achieve is;

    1. Look Account Numbers entered in Text1 in tblAccount[TransAccount]
    2. If found, look for LAST entry (with TransDate+TransTime), not for DLast
    3. If Last Entry Status field is "In Active", then copy ONLY those account numbers form Text1 to another text box (Text2)
    4. If possible Delete Text2 Account numbers from Text1, so only Active accounts will be in Text1.

    Thank you for your help.

  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,815
    You are saving multiple values into a single field? Why?

    This seems to be a poor database design.

    What you want will probably involve some complex VBA code.
    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
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    Hi June7,

    I am sorry. I am not saving multiple values into a field. I meant with multi line is, Enter Key Behavior: New Line in Field

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Even if separated by new line, still looks like multiple values in one field with attendant complications for data management.

    What purpose is served by saving to an unbound textbox on an unbound form?

    As stated, the code will probably be complicated.

    Need better understanding of what and why you are doing this. There is probably a better approach.
    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.

  5. #5
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    OK June7, here is what I am trying to do.

    When users enters multiple values in Text1, look for each line account number in tblAccount[TransAccount]. then look for matchable account numbers MAX entry in TransDate+TransTime field (may be we could do this with CDate?) If MAX entry of matchable accounts TransStatus is "Active" then copy ONLY those accounts with MAX entry tblAccounts * fields to a temporary table which is a bounded form with tblTempAccounts, so they can fill related fields, then doing Append in tblMaster.

    So firstly I like to separate matchable & non matchable Accounts in Text1 & Text2 respectively. So they can re-investigate for in-active accounts & proceed with Active Accounts.

    I hope this make you clear June7

  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,815
    Why do users type account numbers? Significant chances for typos. Why not select from combobox or listbox? The combobox or listbox RowSource could be restricted to either active or inactive account numbers. Don't understand the 'matchable' criteria.

    If combobox or listbox not practical for selecting accounts, using textbox as you described would probably involve:

    1. parsing the string to an array

    2. comparing array element to the 'matchable' and active/inactive criteria

    3. writing record to temp table if criteria met

    4. repeat a loop until all elements of array dealt with


    A temp table just for simple data entry? I have never done something like that.
    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
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    Quote Originally Posted by June7 View Post
    Why do users type account numbers? Significant chances for typos...
    We get account numbers in Notepad or excel, so just copy & paste into a text box

    Why not select from combobox or listbox?
    Sometime will have up to 50 accounts to check, so i think copy & paste is more convenient.

    The combobox or listbox RowSource could be restricted to either active or inactive account numbers.
    The Account numbers flexible, Active, In-Active or both in previously. Only important is MAX Entry of it's account. If particular account numbers MAX entry status is 'Active', then copy

    Don't understand the 'matchable' criteria
    Matchable Account meant, Text1 may have multiple numbers, so account numbers copied in Text1 & it's MAX entry status is 'Active'

    If combobox or listbox not practical for selecting accounts, using textbox would probably be best to have code execute after each entry of account number, not parsing a string.

    And a temp table just for simple data entry? I have never done something like that
    Temp table may be a bad idea, but....

  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,815
    Ooops, I edited my previous post as you were replying.
    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
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    Take this way:

    I have entered 5 account numbers in Text1,

    MADA123
    LADA234
    POPU123
    FADA009
    KADA100

    So split these entries with vbCrLf then,

    1. Do a Dcount in tblAccount[TransAccount], If >0 then
    2. Check for it's LATEST entry (with Date & Time, TransDate+TransTime) Very rarely accounts may occur multiple times, so including Time also will get latest entry.
    3. Check what is the status of this account number in TransStatus field, If it is 'Active' copy account number to tblTemp with it's latest Date & Time.
    4. Loop

    Thank you for your help.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you know how to code this? Do you know how to work with arrays? Are you familiar with Split() function?
    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.

  11. #11
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    I am sorry June7,

    No, I don't know any of those three. I just posted, may be get some more clear about it.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here is some for a start:

    Private Sub btnCheckAccts_Click()
    Dim aryNums As Variant
    Dim i As Integer
    aryNums = Split(Me.tbxAccts, vbCrLf)
    For i = 0 To UBound(aryNums)
    Debug.Print aryNums(i) 'instead of Debug.Print, your code to check criteria
    Next
    End Sub
    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.

  13. #13
    Only4Access is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    38
    Thank you June7 for your help.

    I do not know the coding. I have re-arrange my DB a little. I have created a new thread about INSERT INTO & DELETE.

    Could you please look on that?

    Also, I am marking this thread as SOLVED.

    https://www.accessforums.net/forms/i...ion-40832.html

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The other thread described the same issue and duplicated topic. I deleted it to continue discussion here.

    Instead of the Debug.Print in the example code provided, need code to check for account number in tblAccounts.TransAccounts and create record in tblVerification if appropriate. That code could be:

    If Not IsNull(DLookup("TransAccounts", "tblAccounts", "TransAccounts='" & aryNums(i) & "'")) Then
    CurrentDb.Execute "INSERT INTO tblVerification(TransAccounts) VALUES('" & aryNums(i) & "')"
    CurrentDb.Execute "DELETE FROM tblAccounts WHERE TransAccounts='" & aryNums(i) & "'"
    End If
    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.

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was interested in your project, so I whipped up an example dB (A2000 format, but should still work)

    It only adds the matches to the temp table... no deletes from the Account table - could be added if you want.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-11-2014, 12:39 PM
  2. Replies: 1
    Last Post: 07-30-2013, 08:15 AM
  3. MS Active Directory
    By pkelly in forum Access
    Replies: 9
    Last Post: 10-21-2011, 08:26 AM
  4. Active only Editing
    By Dalagrath in forum Forms
    Replies: 2
    Last Post: 03-07-2011, 01:44 PM
  5. help with queries active (yes/no)
    By islandboy in forum Access
    Replies: 4
    Last Post: 09-04-2009, 12:55 PM

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