FORM :Transform field1 to field2 in row line

  • Thread starter Thread starter syedalna
  • Start date Start date
S

syedalna

I have data in "query1" and field1 name is "number" in form-datasheet view
like this

number (<<This is in Access Form)
601366
112121
654545
013554

I need to transfer these number to other Field2 in the same Form area to be
like this with saperated with commas ","> 601366,112121,654545,013554

Basically there are 2 field involved, (1) Field1= where it store several
number (2) field2= where these number converted to row orderly, saperated
with commas and in text line.

And also a command button, where when the button click, it will transform
data in field1 to field2.

I need help to do this in Form.

Thank you
 
If I understand correctly, you have a field in a main form and you want to
populate it with the concatenated results of another field in a subform
datasheet via a button. One way to do this is to create button with
something like the following procedure:

Private Sub Command1_Click()

Dim string1 As String

Me.Subform.Form.RecordsetClone.MoveFirst
While Me.Subform.Form.RecordsetClone.EOF = False
string1 = string1 & "," & Me.Subform.Form.RecordsetClone!Field1
Me.Subform.Form.RecordsetClone.MoveNext
Wend
Me.Field2 = Mid(string1,2)

End Sub
 
Wow, it really works

Thank you very much kingston.

Anyway,
1- IF record is null, its show debug. How to get rid of this problem?
... i was thinking like, IF record is null.. noting will be happening.

2- Why in some MS Access (version 2003) it show "complie error.. Can't find
project or library" in Microsof visual basic. obviously the code point to
this "Mid(string1, 2)".Is There away to avoid this thing.

Appreciate if you could assist me on this question.

Thanks
If I understand correctly, you have a field in a main form and you want to
populate it with the concatenated results of another field in a subform
datasheet via a button. One way to do this is to create button with
something like the following procedure:

Private Sub Command1_Click()

Dim string1 As String

Me.Subform.Form.RecordsetClone.MoveFirst
While Me.Subform.Form.RecordsetClone.EOF = False
string1 = string1 & "," & Me.Subform.Form.RecordsetClone!Field1
Me.Subform.Form.RecordsetClone.MoveNext
Wend
Me.Field2 = Mid(string1,2)

End Sub
I have data in "query1" and field1 name is "number" in form-datasheet view
like this
Click to expand...
[quoted text clipped - 18 lines]
Thank you
Click to expand...
Click to expand...
 
Use the function Nz() to avoid problems with Nulls.
Go into the VB window and from the Tools menu open the References window.
Add any libraries that are missing from the computer that gives you the error
by comparing what's checked to the list from the computer that works.
Wow, it really works

Thank you very much kingston.

Anyway,
1- IF record is null, its show debug. How to get rid of this problem?
... i was thinking like, IF record is null.. noting will be happening.

2- Why in some MS Access (version 2003) it show "complie error.. Can't find
project or library" in Microsof visual basic. obviously the code point to
this "Mid(string1, 2)".Is There away to avoid this thing.

Appreciate if you could assist me on this question.

Thanks
If I understand correctly, you have a field in a main form and you want to
populate it with the concatenated results of another field in a subform
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
could you show me the complete code for function Nz()... i am a newbies on
this stuff.


Use the function Nz() to avoid problems with Nulls.
Go into the VB window and from the Tools menu open the References window.
Add any libraries that are missing from the computer that gives you the error
by comparing what's checked to the list from the computer that works.
Wow, it really works
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
Instead of simply Me.Subform.Form.RecordsetClone!Field1, use:
Nz(Me.Subform.Form.RecordsetClone!Field1,"")
so if Me.Subform.Form.RecordsetClone!Field1 is null, it'll insert a zero-
length string rather than try to add a null which would result in an error.
You can use IsNull(Me.Subform.Form.RecordsetClone!Field1) in a conditional
(If Then) to see if you want to add a comma too.
could you show me the complete code for function Nz()... i am a newbies on
this stuff.
Use the function Nz() to avoid problems with Nulls.
Go into the VB window and from the Tools menu open the References window.
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
Back
Top