Field's Default from table other than Source

[Sonny Jim]

On a form, is there a way to make a default to a field that is from a table other than the record source's table?

I am making an insurance claim form whose loss address is "typically", but not always, the address of the insured. The Field's record source is from a table called tblClaim, and the default value is from a table called tblInsured. The record source for the Form is a Select query that includes both tables. I defined the default value as:

=[strInsAddr]

...this is a field from tblInsured. But this does nothing, that is, it offers no default when I create a new claim entry by choosing an Insured's name listed in drop down box whose record source is the tblInsured table. Now, as I type, I realize that a default value would be dependant on knowing who the Insured would be. Is there a way to do this?

 

[Kiwiman]

Howzit

You can put a Dlookup in the After Update procedure of the Insured name

me.yourlossadfieldinform = dlookup("[Addressfieldintable]","Yourtable","[YourNamefieldintable]= '" & me.yournamefieldinform & "'")

if YourNamefieldintable is an integer, use

me.yourlossadfieldinform = dlookup("[Addressfieldintable]","yourtable","[YourNamefieldintable]= " & me.yournamefieldinform)

 

[Sonny Jim]

I typed the following for my namefieldinform After Update click event:

me.strLossAddr = dlookup("[strInsAddr]","tblInsured","[sngInsuredID]= '" & me.sngInsuredID & "'")

and I as I filled in my namefieldinform and tabbed to the next field I received the following Microsoft Visual Basic error (and mystrLossAddr didn't fill in):

Run-time error '3464':
Data type mismatch in criteria expression.

What do I need to do differently?

 

[MSAccessRookie]

I typed the following for my namefieldinform After Update click event:

me.strLossAddr = dlookup("[strInsAddr]","tblInsured","[sngInsuredID]= '" & me.sngInsuredID & "'")

and I as I filled in my namefieldinform and tabbed to the next field I received the following Microsoft Visual Basic error (and mystrLossAddr didn't fill in):

Run-time error '3464':
Data type mismatch in criteria expression.

What do I need to do differently?

The statement equates to the following:

me.strLossAddr = dlookup("[strInsAddr]", "tblInsured", "[sngInsuredID] = '{ Value of me.sngInsuredID } ')

This is OK of sngInsuredID is a Text Field. If it is a Number field, then I think that it should be

me.strLossAddr = dlookup("[strInsAddr]", "tblInsured", "[sngInsuredID] = " & cstr(me.sngInsuredID))

 

[gemma-the-husky]

you need to think about normalisation, I think

ie how are you storing

a) the claim
b) the insured
c) the correspondence address?

you shouldnt have to manually look this up from another table

the system and queries should be linking together to automate this information - at worse you should have say a checkbox to say "use different address", and an address field in which to enter the variant correspondence address if necessary.

 

[Sonny Jim]

Actually, I just got it to work by making a small change to the syntax:

Me.strLossAddr = DLookup("[strInsAddr]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

This seemed to do the trick! I am storing my information as follows: a) the Claim table, which specifies the Loss Address, b) the Insured table, which specifies the Address of the Insured. For this claims company the claimants losses often occur at their residence as they deal with a lot of homeowners insurance claims.

 

[Sonny Jim]

Wow! That code works great!!!! I will definitely have to remember the Dlookup function! Thank you very much Kiwiman, MSAccessRookie, and gemma-the-husky!!! I added a refresh command to refresh a subform I have at the bottom of my form. Here is all my code for this event in case it helps someone else out in the future:

• Private Sub sngInsuredID_AfterUpdate()

• Me.strLossAddr = DLookup("[strInsAddr]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• Me.strLossAddr = DLookup("[strInsAddr]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• Me.strLossCity = DLookup("[strInsCity]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• Me.strLossState = DLookup("[strInsState]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• Me.strLossZip = DLookup("[strInsZip]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• Me.strLossCountry = DLookup("[strInsCountry]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID)

• On Error GoTo Err_sngInsuredID_AfterUpdate

• DoCmd.RunCommand acCmdRefresh

• Exit_sngInsuredID_AfterUpdate:

• Exit Sub

• Err_sngInsuredID_AfterUpdate:

• MsgBox Err.Description

• Resume Exit_sngInsuredID_AfterUpdate

• End Sub

 

[MSAccessRookie]

Wow! That code works great!!!! I will definitely have to remember the Dlookup function! Thank you very much Kiwiman, MSAccessRookie, and gemma-the-husky!!! I added a refresh command to refresh a subform I have at the bottom of my form. Here is all my code for this event in case it helps someone else out in the future: Private Sub sngInsuredID_AfterUpdate() Me.strLossAddr = DLookup("[strInsAddr]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) Me.strLossCity = DLookup("[strInsCity]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) Me.strLossState = DLookup("[strInsState]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) Me.strLossZip = DLookup("[strInsZip]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) Me.strLossCountry = DLookup("[strInsCountry]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) On Error GoTo Err_sngInsuredID_AfterUpdate DoCmd.RunCommand acCmdRefresh Exit_sngInsuredID_AfterUpdate: Exit Sub Err_sngInsuredID_AfterUpdate: MsgBox Err.Description Resume Exit_sngInsuredID_AfterUpdate End Sub

 Private Sub sngInsuredID_AfterUpdate() 
[B][COLOR=red]On Error GoTo Err_sngInsuredID_AfterUpdate [/COLOR]{SHOULDN'T THIS BE HERE?}[/B]
    Me.strLossAddr = DLookup("[strInsAddr]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) 
    Me.strLossCity = DLookup("[strInsCity]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) 
    Me.strLossState = DLookup("[strInsState]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) 
    Me.strLossZip = DLookup("[strInsZip]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) 
    Me.strLossCountry = DLookup("[strInsCountry]", "tblInsured", "[sngInsuredID]=" & Me.sngInsuredID) 
[B][COLOR=green]On Error GoTo Err_sngInsuredID_AfterUpdate[/COLOR] [/B]
    DoCmd.RunCommand 
    acCmdRefresh 
Exit_sngInsuredID_AfterUpdate: 
    Exit Sub 
Err_sngInsuredID_AfterUpdate: 
    MsgBox Err.Description 
    Resume Exit_sngInsuredID_AfterUpdate 
    End Sub

I reformatted your code to take a look at it, and I wonder of the "On Error Statement needs to be moved (See Above). I am sure that it will run, but shouldn't the DLookup Statements also be protected by the On Error Trap? For future reference, using [ code ] [ /code ] around code sections preserves the formatting.

 

[Sonny Jim]

Yes, that's better! Thanks again!!

 

[MSAccessRookie]

Yes, that's better! Thanks again!!

No problems. I think I made my first 100 posts or so before I learned how to preserve the code formatting.