Comma separated values on form textbox into table

[hardrock]

Hi All,

If I have a text box (Text1) on my form with comma separated values entered i.e. 100,120,250,300 what would be the easiest way to enter these into a table column with a button click event. I would like to enter these values into my table tb_test under column Values. Thanks

 

[boblarson]

First off let me ask one quick question - Why are you putting multiple values in a single field? This is one major violation of the rules of normalization (all data must be atomic - one piece in its place).

 

[hardrock]

Hi Bob, good question. Please allow me to explain. Think of line numbering on a Sales order. I am trying to add a feature onto a current database I have that will delete sales order lines in a table based on the line number(s) I input in textbox (text1). The deliminator comma just seperates the lines to be deleted. Probably not the best way of doing it, but its simple enough way for the user to do it this way.

 

[boblarson]

Okay, a couple of other questions.

You say, "that will delete sales order lines in a table based on the line numbers you input in the textbox." What do you mean by line numbers and how do you identify them? Also, do you mean that someone will look at your input and delete each one manually?

I think more on the process is necessary before I can give better suggestions. I still am thinking that this may not be something to store in the table.

 

[hardrock]

Hi Bob, On the sales order, each unique line is represented as a line number etc line 10,20,30 etc. On my sales order report, there are occasions where i do not wish to display a certain line number(s). So easiest way to deal with that is to delete the line out of the table which is bound to the report.

Basically, i have the idea that the user will enter in a text box the lines they do not wish to display on the report, thus entering 10,20,30 in the textbox, and the hitting the button "Delete"would remove the unwanted lines from the table.

 

[boblarson]

So, if you delete the record, then you can't get it back. Is that okay?

Second, you can use code like this for deletion if you have the comma separated values:

Dim strSQL As String
 
strSQL = "DELETE * From TablNameHere WHERE [FieldName] In(" & Me.Text1 & ")"
CurrentDb.Execute strSQL, dbFailOnError

 

[jdraw]

Just curious from a business perspective -- What sort of line items would people not want to report? An auditor might be interested in the Order,even if someone doesn't want it on a report.

As Bob says - much easier to create a query to delete the records from the table, or at least mark them as deleted. You could have a DeletedFlag Y/N field that set to yes /no where N means not to be reported.

 

[hardrock]

Bob, Thankyou ever so much! This was doing my head in.. your bit of code worked perfect...