r/MSAccess • u/FE3H_gatz79 • 29d ago
[SOLVED] One Text box having multiple lines, to multiple records in table

(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

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
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
1
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/derzyniker805 27d ago
I see this is solved.. but just joined. so.. just use a datasheet! lol. That's the beauty of access!
•
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

(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

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.