Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10

    Load values from a text box into a recordset


    I have a use case where a user has a list of record identifiers which is available to copy/paste into something – right now, a text box. I need to do some reformatting of these IDs and then use them in a select query to retrieve data from another database and then return the result set back to a table in my database.

    My working plan is to enable the user to paste these record identifiers into a text box and then with a command button move those identifiers to a recordset, reformat them and then use them in a query.

    I know how to create a recordset but I don't know how to programmatically move the values pasted into the text box into the recordset. Can someone help me with this.

    Here is an example of the code I would use to create the recordset;

    Code:
    Dim rstADO As ADODB.Recordset
    Dim fld As ADODB.Field
    Set rstADO = New ADODB.Recordset
    With rstADO
        .Fields.Append "Disposition_Id", adVarChar, 10, adFldKeyColumn
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
    End With
    Here's an example of the record identifiers that the user would paste into a text box (One identifier per line, no comas).

    Code:
    LOC1768
    LOC4769
    LOC840127
    LOC840369
    LOC840757
    LOC841396
    LOC860794
    LOC870344
    LOC880796
    LOC942090
    MSL3454
    MSL3602
    MSL3794
    MSL4444
    MSL792262
    MSL831287
    MSL840203
    MSL841156
    Thanks in advance for any assistance that you can provide.

  2. #2
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    I assume this is an unbound form. If I am understanding your inquiry correctly, to place what is in a form control into a recordset you say something like:
    Code:
    Dim rs as DOA.RecordSet
    rs = CurrentDb.OpenRecordSet("YourTableName")
    rs.Fields("FieldName") = Me![formControlName].Value
    This copies whatever is placed in the form control that you reference and places it in the specified field of the recordset you define.

  3. #3
    richj is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    1
    Would a combo box populated from a table with your record identifiers be easier to get the value than copying and pasting? The table could easily be updated as data changes.

  4. #4
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    I suppose this might work if the user was after one identifier at a time. However, the user has a whole list of record identifiers (not just one) for which records must be retrieved (sometimes hundreds. Potentially up to 1000 at a time). They have already done work using another system to obtain these identifiers. The identifiers that the user is supplying will go into a temporary table (a recordset is what I was thinking of) which will then be used in an inner join or in the where clause of a select statement of a query which retrieves the records that the user is after.

  5. #5
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    So they will copy/paste a large number of these identifiers, and you want each identifier to go to a separate record of the database? So your example with the codes:
    Code:
    LOC1768  -> distinct record
    LOC4769  -> distinct record
    LOC840127  -> distinct record    (and so on down the list)
    LOC840369
    LOC840757
    LOC841396
    LOC860794
    LOC870344
    LOC880796
    LOC942090
    MSL3454
    MSL3602
    MSL3794
    MSL4444
    MSL792262
    MSL831287
    MSL840203
    MSL841156
    Is that what you mean?

  6. #6
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    nick404;

    1) Yes; this is an unbound form. It would be called from the main data entry form that the user is using as a means to supply the list of record identifiers and execute the query which would return it's results to a subform of the calling from.

    2) I have a couple of questions regarding the code that you show above.

    • Is it correct to assume that the dim statement should read "Dim rs as ADO.RecordSet" (instead of "Dim rs as DOA.RecordSet")?
    • I don't understand what I would supply as a value for "YourTableName" in the statement "rs = CurrentDb.OpenRecordSet("YourTableName")". I don't have a table per-ce. The only thing I have is the recordset I defined as per the code statement in my original post above. Is this the "YourTableName" to which you are referring?
    • Will the last statement - rs.Fields("FieldName") = Me![formControlName].Value, put each of the record id's pasted into the text box control (multiple values are pasted into the control) in separate rows of the recordset or will it just put the entire contents of the textbox control into one record?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You need a table that the recordset is based on. You have to parse the user input of a string (apparently values are separated by CrLf) and save each value to a record in table. This can done by appending to the recordset which then updates the table or by directly inserting to table with an INSERT sql action.

    I would parse the string to an array object then use looping code to save each array element to table.
    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.

  8. #8
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Is it correct to assume that the dim statement should read "Dim rs as ADO.RecordSet" (instead of "Dim rs as DOA.RecordSet")?

    Neither, it should read 'Dim rs as
    DAO.RecordSet' (my bad on the typo). Here is a good link on Data Access Objects and helpful information on what the library all includes that you may make use of.

    I don't understand what I would supply as a value for "YourTableName" in the statement "rs = CurrentDb.OpenRecordSet("YourTableName")". I don't have a table per-ce. The only thing I have is the recordset I defined as per the code statement in my original post above. Is this the "YourTableName" to which you are referring?
    As June said, you need a table to store the data.

    Will the last statement - rs.Fields("FieldName") = Me![formControlName].Value, put each of the record id's pasted into the text box control (multiple values are pasted into the control) in separate rows of the recordset or will it just put the entire contents of the textbox control into one record?
    No, this statement will not put each of the id's into a distinct record. In my first post, I wasn't aware that you needed each to be in a separate record. That makes things more complex...follow what June suggested. Here is a link for using the Split() function to perform this task. Your delimiter is the newline character. Depending on OS which newline you use will vary (sometimes is Chr(10) and other times is vbNewLine) can't remember which is for which OS. To insure against your program failing on different operating systems I suggest you just use "Environment.NewLine" as a catch-all.
    Last edited by nick404; 01-08-2016 at 10:09 AM. Reason: minor edits

  9. #9
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    nick404;

    Quote Originally Posted by nick404 View Post
    So they will copy/paste a large number of these identifiers, and you want each identifier to go to a separate record of the database? So your example with the codes:
    Code:
     LOC1768  -> distinct record
     LOC4769  -> distinct record
     LOC840127  -> distinct record    (and so on down the list)
     LOC840369
     LOC840757
     LOC841396
     LOC860794
     LOC870344
     LOC880796
     LOC942090
     MSL3454
     MSL3602
     MSL3794
     MSL4444
     MSL792262
     MSL831287
     MSL840203
     MSL841156
    Is that what you mean?
    Yes; this is correct.

  10. #10
    DrGUI is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2015
    Location
    Parts Unknown
    Posts
    23
    Try the following code to write directly into your recordset.

    Code:
        
        Dim rstADO As ADODB.Recordset
        Dim i As Integer
        Dim aDisp_ID As Variant
        
        Set rstADO = New ADODB.Recordset
        
        ' get content of text box and store in array
        aDisp_ID = Split(Me.Text3.value, vbCrLf)
        
        With rstADO
            .Fields.Append "Disposition_Id", adVarChar, 10, adFldKeyColumn
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open
        
            ' loop through array and load into recordset
            For i = 0 To UBound(aDisp_ID)
                .AddNew
                .Fields("Disposition_Id") = aDisp_ID(i)
                .Update
            Next i
        
            ' verify content of recordset in immediate window
            .MoveFirst
            Do Until rstADO.EOF
                Debug.Print .Fields("Disposition_ID")
                .MoveNext
            Loop
        
        End With
        
        rstADO.Close
        Set rstADO = Nothing

  11. #11
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    Quote Originally Posted by June7 View Post
    You need a table that the recordset is based on.
    Well. That's unfortunate! I think I might be taking the wrong approach. Can you please confirm; I can't write directly to a recordset like the one I've defined in my original post? I was hoping to leverage the idea presented in this post

    Quote Originally Posted by June7 View Post
    (apparently values are separated by CrLf)
    This is correct; the list of record IDs would be separated by CrLf. The user has built this list of record IDs by copying text from a PDF and manipulating it in word to eliminate all of the extraneous data (this is a process I have no control to change),

    Since this is an intermediate process i.e. pasting the list into a textbox control I don't need or want these values in a table (except, perhaps, as a means to do a query). Really all I need to do is to get these record IDs into a query. Since I need to reformat them I thought that the easiest way to do that would be to get them into some sort of table-like structure (i.e. a recordset) and manipulate them from there and then use that recordset in a join in a query of table which has the records that I'm interested in retrieving (or alternatively as a subquery in the WHERE clause).

    Quote Originally Posted by June7 View Post
    You have to parse the user input of a string... I would parse the string to an array object then use looping code to save each array element to table.
    I've never needed to do this. If you could point me to a resource which shows how to do this I would be ever so appreciative.

    Perhaps the right solution is to bypass the whole recordset idea and simply parse the string (including reformatting the IDs as required) and build up the SLQ statement for my query. I had initially thought of this but then, because I didn't know how parse the string, thought that putting the records into a "virtual table" (that's what I thought a recordset was) would be the easier route. Apparently not...

    The query would wind up looking something like this;

    Code:
    SELECT
    <Columns I want to return>
    FROM [The table which has the data I need to get] WHERE
    [Disposition_Id] IN (
    'LOC 1768', 'LOC 4769', 'LOC 840127', 'LOC 840369', 'LOC 840757', 'LOC 841396', 'LOC 860794', 'LOC 870344', 'LOC 880796', 'LOC 942090', 'MSL 3454', 'MSL 3602', 'MSL 3794', 'MSL 4444', 'MSL 792262', 'MSL 831287', 'MSL 840203', 'MSL 841156'
    )
    OR
    Code:
    SELECT
    <Columns I want to return>
    FROM [The table which has the data I need to get] T1 WHERE
    T1.[Disposition_Id] IN (SELECT * FROM [The table I've built which contains the user-supplied IDs])
    OR
    Code:
    SELECT
    <Columns I want to return>
    FROM [The table which has the data I need to get] T1 JOIN [The table I've built which contains the user-supplied IDs] T2 ON T1.[Disposition_Id] = [T2.Disposition_Id]
    Thanks again to all who have weighed in on this.

    Finally; I'm still looking for an easy solution which will allow me to take a list of record IDs pasted into a text box and to build an SQL query statement.

    Any assistance is very much appreciated.

  12. #12
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    Quote Originally Posted by DrGUI View Post
    Try the following code to write directly into your recordset.

    Code:
        
         Dim rstADO As ADODB.Recordset
         Dim i As Integer
         Dim aDisp_ID As Variant
         
         Set rstADO = New ADODB.Recordset
         
         ' get content of text box and store in array
         aDisp_ID = Split(Me.Text3.value, vbCrLf)
         
         With rstADO
             .Fields.Append "Disposition_Id", adVarChar, 10, adFldKeyColumn
             .CursorType = adOpenDynamic
             .LockType = adLockOptimistic
             .Open
         
             ' loop through array and load into recordset
             For i = 0 To UBound(aDisp_ID)
                 .AddNew
                 .Fields("Disposition_Id") = aDisp_ID(i)
                 .Update
             Next i
         
             ' verify content of recordset in immediate window
             .MoveFirst
             Do Until rstADO.EOF
                 Debug.Print .Fields("Disposition_ID")
                 .MoveNext
             Loop
         
         End With
         
         rstADO.Close
         Set rstADO = Nothing
    This is EXACTY what I was looking for. Now on to the next step. Can I use this recordset in a query like this?

    Code:
    SELECT
    <Columns I want to return>
    FROM [The table which has the data I need to get] T1 WHERE
    T1.[Disposition_Id] IN (SELECT * FROM [rstADO])
    OR
    Code:
    SELECT
    <Columns I want to return>
    FROM [The table which has the data I need to get] T1 JOIN [rstADO] T2 ON T1.[Disposition_Id] = [T2.Disposition_Id]
    My MS access application (MS Access 2010) is actually an Access Data Project (a .adp file). My database is a SQL Server2008 R2 database. The database which has "[The table which has the data I need to get]" is a separate SQL Server2008 R2 database on a different server.

    No one said this would be easy... :-)

    Again, thank you for the excellent support!

  13. #13
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    If you could point me to a resource which shows how to do this I would be ever so appreciative.

    Concerning your confusion on splitting up a string, I put a link in my post above (post #8) you must not have seen. This site gives some information on what Split() is and does if you have not used it before. And here is another I found more on how to use the function correctly. Please read them, they will address your concerns with using the Split() function to convert your input string to a 1D array which can then be manipulated to create distinct records for your db.

  14. #14
    CanuckBuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    10
    Thanks for the links nick404. I'm scrambling a bit here (meetings, interruptions and such) and haven't been able to follow up on everything. I will do that. I appreciate the support.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Recordsets are virtual objects in VBA. They cannot be referenced anywhere else.

    You must save the data to table and use table in query.
    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.

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

Similar Threads

  1. Replies: 12
    Last Post: 03-27-2014, 04:49 AM
  2. Clear text box on form load
    By RoyLittle0 in forum Access
    Replies: 3
    Last Post: 03-05-2013, 05:40 AM
  3. Set Combo box values on form load to Please Select
    By Richie27 in forum Programming
    Replies: 7
    Last Post: 06-12-2012, 07:36 AM
  4. Getting Values out of a recordset
    By theracer06 in forum Programming
    Replies: 6
    Last Post: 09-24-2010, 03:44 PM
  5. Change the text box color on form load
    By mikec in forum Programming
    Replies: 2
    Last Post: 03-01-2010, 11:47 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