Change data before save

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi: Can any one please tell me how do i convert data from (1,2) to
("1","2","3") before i save it into table. I mean when user enters
value in a textbox like 1,2 i want to change it into above format
before i save it.

Thanks.
 
I can't imagine why you would want to save multiple values in a table but
you can use the After Update of the control to:

Private Sub textbox_AfterUpdate()
Me.textbox = """" & _
Replace(Me.textbox, ",", """,""") & """"
End Sub
 
Hi: Why i want to do that because of that:

WHERE ((table1.field1) In (SELECT Cstr(Nz([field2], "")) FROM
[table2];)));

table1.field1 is type text and values are store in like 1,2,3

table2.field2 is type number

The SQL "in" will not be work until i put the values in field1 like
'1','2','3'. So i want when user done with entering values i will be
change them into '1','2','3','4'. If i use a listbox, does listbox save
values like that i mean in quotations automatically?
 
Anna said:
Hi: Why i want to do that because of that:

WHERE ((table1.field1) In (SELECT Cstr(Nz([field2], "")) FROM
[table2];)));

table1.field1 is type text and values are store in like 1,2,3

table2.field2 is type number

The SQL "in" will not be work until i put the values in field1 like
'1','2','3'. So i want when user done with entering values i will be
change them into '1','2','3','4'. If i use a listbox, does listbox
save values like that i mean in quotations automatically?
Click to expand...

Saving multiple values in a single field like that is a violation of proper
database design rules. Each field should contain exactly one piece of data.

What you should have is a one-to-many relationship between your table and a
second table where each record in the second table holds the 1, the 2, and the 3
in separate rows.

Have you tried the IN clause with quotes around your values? I'd be suprised if
that worked either and no, a Multiselect ListBox doesn't save values at all,
much less in the format you require. Multi-Select ListBoxes always have a value
of Null. There use is limited to coded routines where the ItemsSelected
collection can be looped through to obtain the values.
 
Back
Top