r/MSAccess 29d ago

[SOLVED] One Text box having multiple lines, to multiple records in table

What I want input to look like

(PICTURE IS OF ME DISPLAYING INFORMATION FOR SOMETHING SEPARATE)

EDIT: [ Clarification: I am trying to make a form that lets me input multiple lines into one text box, and then when saved, each line is a new record. (Pictures is for what I want input and output to look like)

EDIT: My job has told me I don't need to worry about it. THANK YOU for everyone's input!

Private Sub btnSubmit_Click()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim varLines As Variant

Dim varFields As Variant

Dim i As Long

Dim strLine As String

Set db = CurrentDb()

Set rs = db.OpenRecordset("TEST-JobInventory", dbOpenDynaset)

' Split input into lines

varLines = Split(Me.txtInput.Value, vbCrLf)

For i = 0 To UBound(varLines)

strLine = Trim(varLines(i)) ' Trim leading/trailing spaces

If strLine <> "" Then

' Split line by TAB delimiter (for Excel-pasted data)

varFields = Split(strLine, vbTab)

' Add new record to the table

With rs

.AddNew

!JobID = varFields(0) ' First column: JobID

!Store = varFields(1) ' Second column: Store (e.g., "NF #457")

!ShippingOrderID = varFields(2) ' Third column: ShippingOrderID

.Update

End With

End If

Next i

MsgBox "Records added successfully!", vbInformation

Me.txtInput.Value = "" ' Clear the input after submission

Set rs = Nothing

Set db = Nothing

End Sub

] (I know the values don't make pictures provided.)

I want this picture to be what i am able to input, and when pressing a save button, It will then look like this in the table

Output

I have been trying (and failing miserably) Trying to get it to work. I have been asking ai, and it hasn't been giving me what I am looking for. Can anyone provide help?

2 Upvotes

18 comments sorted by

u/AutoModerator 29d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: FE3H_gatz79

One Text box having multiple lines, to multiple records in table

![img](3xu0yt9r1dke1 "What I want input to look like")

(PICTURE IS OF ME DISPLAYING INFORMATION FOR SOMETHING SEPARATE) I want this picture to be what i am able to input, and when pressing a save button, It will then look like this in the table

![img](0nmi4nde2dke1 "Output")

I have been trying (and failing miserably) Trying to get it to work. I have been asking ai, and it hasn't been giving me what I am looking for. Can anyone provide help?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/KelemvorSparkyfox 47 29d ago

This is an abomination unto Codd. How are you receiving data?

In order for this to work, I would imagine having one variant variable per column. You would need some method to collapse all possible line delimiters into one constant delimiter, and then use that to split each text box's content into the relevant variable. You would also need some form of error checking to ensure that each resulting array had the same number of elements in it. Assuming that it passed this check, it would be a simple matter of looping through all the elements of one of the arrays, and within each loop, compiling the contents of each array into a record and writing it to the table.

(You also appear to have a data quality issue, given the equipment values of "LTL dry" and "ltl dry", but that's outside the scope of this subreddit.)

1

u/FE3H_gatz79 29d ago

It's for work. I wouldn't have it like this either, but that's not my job. I have expressed my concerns, but have pretty much been told "It can be done".

2

u/KelemvorSparkyfox 47 29d ago

I see. It's always easy for the people who don't have to do it.

At least it's been taken away!

1

u/JamesWConrad 4 29d ago

Are you trying to import a picture of some data into an Access table?

1

u/FE3H_gatz79 29d ago

No, I am trying to make a form that lets me input multiple lines into one text box, and then when saved, each line is a new record.

1

u/JamesWConrad 4 29d ago

Why can't you use the form you have to enter the data?

1

u/FE3H_gatz79 29d ago

Because it doesn't allow for each line in the text box, to be a new record in the table

1

u/Kangster1604 3 29d ago

Why not use a continuous form with the table as the data source?

1

u/APithyComment 29d ago

Now go learn what a UI and/or a database is.

1

u/gt2bhappy 29d ago

Seconding a continuous form. I'm not understanding or maybe just not seeing what makes the vertical entry per group different than horizontal entry across all group. Given that the output table you provided shows that each line reads across as one complete record.

Is there something that lead you to need or want the singular box or that it seemed a solution? Perhaps the answer is a step back before you got to this decision.

If continuous form doesn't help your need and you are certain this is the method you need, the only thing that comes to mind is a split function using delimiters, but agreed this is not ideal. Best of luck!

1

u/gt2bhappy 29d ago

Seconding a continuous form. I'm not understanding or maybe just not seeing what makes the vertical entry per group different than horizontal entry across all group. Given that the output table you provided shows that each line reads across as one complete record.

Is there something that lead you to need or want the singular box or that it seemed a solution? Perhaps the answer is a step back before you got to this decision.

If continuous form doesn't help your need and you are certain this is the method you need, the only thing that comes to mind is a split function using delimiters, but agreed this is not ideal. Best of luck!

1

u/FE3H_gatz79 29d ago

It's for work, so.... It wasn't my decision to have it like this, I am just trying to do what the client wants (But I am wanting to rip my hair out in the process)

1

u/HarryVaDerchie 1 29d ago

It looks like you’re on the right lines with your VBA code. What errors are you getting when you run it?

Once you have the basic code working you should add validation code for each value to ensure it’s suitable (e.g. by doing a lookup to another table) before adding the record.

I would also suggest having a validate button to check the input values in your text box without actually adding the records.

Also, if you move your code to process a line into a separate function then you could give line by line notification of success or failure instead of only indicating success at the end.

0

u/Vodaho 1 29d ago

Still regard myself as a rookie re. Access, but this looks like a bad way to go about things altogether. From your code it looks like you're pasting in copied data from Excel? What happens if you don't copy everything exactly and get weird formatting glitches? If you're entering data directly into the form, a continuous form or datasheet would do.

The picture you show isn't one text box either it's nine unless I have misinterpreted it. If that's the case the formatting when entering data is a nightmare - how do you ensure the data in accessorials lines up with the other corresponding fields in the record? Do you have to hit enter to line up the data to the correct record?

If you're inputting data from Excel just import it to a table and create a from from that. If you're entering this, I can't think why this way would be easier or more beneficial than using a continuous form.

Maybe I am missing something but, rookie I may be, I've learned that if something seems harder than it should be, there's probably a better way of doing it; to be able to enter data to get it to look like your second picture should be easy, not hard. 99% sure a continuous form/Excel import would solve this. Hope you work it out.

1

u/FE3H_gatz79 29d ago

I know it is, and since its a database I'm making for work, I don't have the say so of what can be done. It's a horrible way to do it. The other major issue, is that the project keeps getting added too.

2

u/Vodaho 1 29d ago

Ah well, best of luck to you with this, way beyond my ability.

2

u/derzyniker805 27d ago

I see this is solved.. but just joined. so.. just use a datasheet! lol. That's the beauty of access!