Populating Address fields in form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

richard - 20 Jan 2008 13:48 GMT

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

Reply to this Message

Rick Brandt - 20 Jan 2008 13:53 GMT

> Hi
>
[quoted text clipped - 14 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 20 Jan 2008 13:58 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi
>
[quoted text clipped - 35 lines]
>
> Richard

Reply to this Message

june - 15 Feb 2008 21:59 GMT

> Do the columns have spaces for the missing data or Nulls? The calculation
> you've got for ClientTotalAddress will only remove spaces for Nulls.
[quoted text clipped - 9 lines]
>
> etc.

>> Hi
>>
[quoted text clipped - 38 lines]
>>
>> Richard

Reply to this Message