Form Flds to default based on other flds (1 Viewer)

[Rich_Lovina]

One of my forms has a textbox with control as follows:

=[POCITY] & " " & [STATE] & " " & [POCODE]...........(1)

Another text box has the control
=[FLNR] & " " & [STNR] & " " & [STREET] .......(2)and
=[CITY] & " " & [STATE] & " " & [PCODE].......(3)

Some records have data in (1) and others in (2) and (3)

I want to code the form such that:

If (1) has data, use (1) in lieu of (2) and (3), otherwise use (2) and (3).

I've gone rusty on whether I change textboxes to comboboxes and what code to use.

Im sure its easy....thanks in advance

 

[DALeffler]

What do you need your text in the text boxes to do?

 

[Rich_Lovina]

Hi there Doug, long time...is it snowing yet?

The text boxes are concatenated fields from within the same table, and when I run a query for selecting names for a mailing, of over 1000 addresses, some have postbox addresses and others don't. I want to set the QBE to display the MAILING ADDRESS.

I see what you mean, my question is not well presented.

What I probably need is two new derived fields called Mailing Address1 and MailingAddress2, such that:

If 1 is not blank, use 1; otherwise use 2.

Hope thats a little clearer.....

 

[DALeffler]

Hi, Rich - no snow, no rain, no tidal waves - no nothin'! (no lawn, either) - for 'bout two weeks, now... We're in the 3rd year of a bad drought. Hope it breaks soon!!!!!

I think I know what you want to do.

Why not stick a Label control on the form that's big enough to hold any combination of

[POCITY] & " " & [STATE] & " " & [POCODE]

or

[FLNR] & " " & [STNR] & " " & [STREET]
[CITY] & " " & [STATE] & " " & [PCODE]

Then in the OnCurrent event for the form, your code might look like :

Private Sub Form_Current()
If Me.NewRecord = True Then
Me.[MyLabel].Caption = "New Record"
Exit Sub
End If

If Len(Me.[POCITY] & " " & Me.[STATE] & " " & Me.[POCODE]) > 2 Then
Me.[MyLabel].Caption = Me.[POCITY] & " " & Me.[STATE] & " " & Me.[POCODE]
Else
Me![MyLabel].Caption = Me.[FLNR] & " " & Me.[STNR] & " " & Me.[STREET] & Chr(13) & Chr(10) & _
Me.[CITY] & " " & Me.[STATE] & " " & Me.[PCODE]
End If

End Sub


HTH,

Doug.

 

[Rich_Lovina]

Thanks for the suggestion but I dont produce the labels. I send the mailing house an email, usually as an Excel spreadsheet, and to eliminate confusion (Mailing House people need simplest possible options as they may be merging upwards of 20 lists from different sources for a single mailer.

Hence I'd like to be able to have one set of fields to export called MailAddr1 & MailAddr2.

To export I view the query with Tools/OfficeLinks/Excel.
I'll email you a three line data sample

 

[DALeffler]

So you're trying to code the query the form is based on?

In a blank Field field of the Query Design Grid:
Addrss: Iif(Len([POCITY] & " " & [STATE] & " " & [POCODE]) > 2, [POCITY] & " " & [STATE] & " " & [POCODE], [FLNR] & " " & [STNR] & " " & [STREET] & Chr(13) & Chr(10) & [CITY] & " " &[STATE] & " " & [PCODE])

Doug.

 

[Rich_Lovina]

No Doug, My Form, DEPTFORM, was created using the auto form creator wizard, as its not based on a query with specific criteria.
All it had was standard break-down fields of an address e.g.

Deptcode, Deptname, street suffix, streetnumber, street,city,state,postcode, and postboxaddress, postboxcity.

The DERIVED fields in the form are those shown in yr last post.

Using the above fld list I want to create a new set of derived fields MAIL ADDR1 and MAILADDR2 where

Iif postboxaddress=<>" " and postboxcity=<>" " use these fields respectively, otherwise use
street suffix+streetnumber+street as MAIL ADDR1, and use
city+state+postcode as MAILADDR2.

(where + is my abbreviated code for the concatenate joiner).

Does your equation therefore apply, as with Autoform, I dont know how to go back to a query for this form???


Sorry to sound a bit dumm