Referrencing a specific field in a table

[Jackpotgee]

Hi, How do you compare a field in your current table to a field in a different table?

I have:

If [OrganisationSendingReferral] = Tbl_HSOfficeLookup![Organisation] Then.....

blah blah blah

End If

It doesnt like that top line at all though! any suggestions?

 

[Guus2005]

You use a dlookup to retrieve the value of the other table or use a recordset to get the values from the other table.

 

[Jackpotgee]

Thanks for your quick response! I will investigate and come back if i have any more questions!

 

[Jackpotgee]

Right, ive got


If (DLookup("Organisation", "Tbl_HSOfficeLookup", "[Organisation] = Forms![Frm_Referrals]!OrgansiationSendingReferral") ) Then

DoCmd.OpenQuery "Qry_ReferralCodeUpdate"

Else

DoCmd.OpenQuery "Qry_ReferralCodeUpdateOther"

- I am aware of the mis-spelling of organisation on the second line.

This isnt working and having never used a dlookup before i am a little stumped, could you possibly shed any further light?

 

[Pat Hartman]

We need a little more context here. Is this something that you want to run in a batch mode to do something in all instances where there is a match or is this a one-off thing that you are doing for the current record in a form event?

DLookups() have limited usefulness and are very expensive resource-wise. In most cases a left join can be substited for a DLookup() with much more flexibility and much more efficiency.

 

[Jackpotgee]

Hi, cheers for your help here. What ive got basically is a combo box which has a list of companies in it. This is on a form called Frm_referrals. The combo box is called OrgansiationSendingReferral and runs off of a table called Tbl_HSOfficeLookup.

In the table Tbl_HSOfficeLookup each company has a corresponding code which i want to also capture.

The problem comes when the user has entered a different company, other than one in the list. The database then needs to add the appropriate code depending on where the company is based. - i have msaccess queries to do this.

so basically what i am trying to say is

If [OrganisationSendingReferral] = Tbl_HSOfficeLookup[Organisation]
i.e the company entered is in the list

then Run Query

Else

Run Another query

Im sure it cant be that difficult but for some reason i just cant get it to work

Thanks again

 

[Pat Hartman]

Try using the Not In List event.

 

[Jackpotgee]

Thanks for your help,

I couldnt actually use the not in List event in the end due to the fact that i did not want to limit the entries to the list.

I have however, after much frustration, finally got it working. It turned out to be quite simple as suspected and just involved running a few more queries.

Thanks again