Populating Address fields in form

[richard]

Hi

I have a form with a combo box clientname, if the user selects a client from
the combo box the rest of the address fields are populated using the after
update event of the combo box. The user can also enter manually all the
required name and address fields.
I then have a report whose qry takes the address fields and removes any
blank entries, code below

ClientTotalAddress: [Clientname] & (Chr(13)+Chr(10)+[address1]) &
(Chr(13)+Chr(10)+[address2]) & (Chr(13)+Chr(10)+[address3]) &
(Chr(13)+Chr(10)+[town]) & (Chr(13)+Chr(10)+[city]) &
(Chr(13)+Chr(10)+[county]) & (Chr(13)+Chr(10)+[postcode])

If the user has entered the clients details in the form using the combo box
then the code above does not work in the report and blank spaces are left in
the address field, however if the user enters the details manually the above
code works perfectly.

The code for the afterupdate event of the combo box is below.

Me!clientname.Value = StrConv(Me!clientname.Value, vbProperCase)
Me!address1.Value = clientname.Column(1)
Me!address2.Value = clientname.Column(2)
Me!address3.Value = clientname.Column(3)
Me!town.Value = clientname.Column(4)
Me!city.Value = clientname.Column(5)
Me!county.Value = clientname.Column(6)
Me!postcode.Value = clientname.Column(7)
Me!telno.Value = clientname.Column(8)
Me!mobileno.Value = clientname.Column(9)

any thoughts would be appreciated to fix the problem of the blank lines

Richard

 

[Rick Brandt]

Hi

I have a form with a combo box clientname, if the user selects a client from
the combo box the rest of the address fields are populated using the after
update event of the combo box. The user can also enter manually all the
required name and address fields.
I then have a report whose qry takes the address fields and removes any
blank entries, code below

ClientTotalAddress: [Clientname] & (Chr(13)+Chr(10)+[address1]) &
(Chr(13)+Chr(10)+[address2]) & (Chr(13)+Chr(10)+[address3]) &
(Chr(13)+Chr(10)+[town]) & (Chr(13)+Chr(10)+[city]) &
(Chr(13)+Chr(10)+[county]) & (Chr(13)+Chr(10)+[postcode])

If the user has entered the clients details in the form using the combo box
then the code above does not work in the report and blank spaces are left in
the address field, however if the user enters the details manually the above
code works perfectly.

Are you sure the record is being saved before opening the report? *How* the
TextBoxes get filled in should not make any difference at all, but perhaps when
they do it manually they are also doing something that triggers a save and that
is not occurring when they use the ComboBox.

 

[Douglas J. Steele]

Do the columns have spaces for the missing data or Nulls? The calculation
you've got for ClientTotalAddress will only remove spaces for Nulls.

You may have to do something like:

Me!address1.Value = _
IIf(Len(clientname.Column(1) & vbNullString) = 0, Null,
clientname.Column(1))
Me!address2.Value = _
IIf(Len(clientname.Column(2) & vbNullString) = 0, Null,
clientname.Column(2))

etc.

 

[june]

Do the columns have spaces for the missing data or Nulls? The calculation
you've got for ClientTotalAddress will only remove spaces for Nulls.

You may have to do something like:

Me!address1.Value = _
IIf(Len(clientname.Column(1) & vbNullString) = 0, Null,
clientname.Column(1))
Me!address2.Value = _
IIf(Len(clientname.Column(2) & vbNullString) = 0, Null,
clientname.Column(2))

etc.