FORM :Transform field1 to field2 in row line

[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

 

[kingston via AccessMonster.com]

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

 

[syedalna via AccessMonster.com]

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

[quoted text clipped - 18 lines]

Thank you

 

[kingston via AccessMonster.com]

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

[quoted text clipped - 19 lines]

 

[syedalna via AccessMonster.com]

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

[quoted text clipped - 17 lines]

 

[kingston via AccessMonster.com]

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.

[quoted text clipped - 6 lines]