Results 1 to 8 of 8
  1. #1
    Drake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    4

    Parsing data from form across multiple fields and records

    Hello!



    I am attempting to take data from one text box within a form and parse it to its corresponding fields in my table. I have achieved this function using a button tied to a macro made up of Select/Case statements. The issue I am having now is that if I have multiple records of data within the text box in the form, what VBA code would allow me to start a new record once I populate the final field within the record?

    The data within my text box is separated by semicolons so that I can differentiate.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What might the contents look like? You'd need some way of knowing there was a new record. Perhaps showing your existing code would help too.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Drake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    4
    The contents of the data being parsed is just plain text. It's a combination of numbers and words, with each section of data separated by a semicolon as I mentioned. I have attached some of my code below. In fact there are more cases and fields that are being populated, but the total amount doesn't really matter because it operates just the same.

    Code:
    sLongCode = txtScan
    iFld = 1
    i = InStr(sLongCode, ";")
    While i > 0
         sWord = Left(sLongCode, i -1) ' Get next fld in string
         sLongCode = Mid(sLongCode, i+1) ' Reduce string
    
         Select Case iFld
           Case 1
                txtA = sWord
           Case 2
                txtB = sWord
         End Select
         
         i = InStr(sLongCode, ";")
         iFld = iFld +1
    Wend
    I am new to Access, but I was trying to start a new record once my "iFld" reached a certain value. That made sense to me, but it didn't work. I am familiar with Excel VBA, but determining the functions for Access VBA has been a bit of a struggle. OR... I guess I could use another delimiter such as an asterisk within the dataset and search for that?

    Hope this helps!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How are you creating a record? If you're populating text boxes within the Select/Case, you could use

    DoCmd.GoToRecord , , acNewRec

    to move the form to a new record at the appropriate time.

    I personally would have used the Split() function and looped the resulting array. If you decided to use a different delimiter to signify a new record, you could use the Split() function twice. First to split on the asterisk, which would give you an array of records. Within a loop of that, the second Split() on the ; to give you the fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Drake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    4
    I actually had another button on my form tied to the command to create a new record. Really was clueless with my whole set up, but I definitely have a better understanding now.

    Thank you for your suggestion! I do like the Split() function better than my current Select/Case method and I believe I will use this solution. The only issue I am having trouble understanding is how to easily loop through the array and assign the strings to the correct fields. Within my form, I have a text box associated with each field in my table. Those text boxes/fields are being assigned their values using "sWord" in my example code. If I use the Split() function, utilizing an array, I will now set those text boxes/fields by using fldArray(i). Is there a general way to reference the text boxes/fields without having to type all of them out, sort of how I loop through the array using the integer "i"?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you're saying the order is predictable, sure. You can name the textboxes with a consistent prefix and then a number, like "txt1" and then in code:

    Me.Controls("txt" & i).Value = Whatever

    or more simply

    Me("txt" & i)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Drake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    4
    Awesome! Yeah I have done something similar in Excel before, but was not sure if there were different/better methods available through Access.

    Thanks again for all your help, Paul!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 06-27-2019, 03:18 PM
  2. Replies: 1
    Last Post: 06-12-2016, 07:00 AM
  3. Replies: 4
    Last Post: 09-01-2015, 11:22 AM
  4. Parsing Data Into Multiple Fields
    By JeffGeorge in forum Access
    Replies: 3
    Last Post: 07-25-2013, 10:11 AM
  5. Parsing field into two fields
    By Lewis in forum Queries
    Replies: 5
    Last Post: 11-14-2012, 04:22 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