using DLookup

[keybearer]

hi.. i need some help here.. i'm currently trying to insert/update data from a form into a table.. if the record exists in the table, it'll update the data and if not, it'll insert new data into the table..
i have no problem with the queries, but my problem is that i cannot do a lookup whether the record is in the table..

this is the current lookup code i'm using.. tell me where i'm going wrong.. i need it to return the MbrID if its in the MailingAddress table, and null if its not.. the MbrID value is taken from the textbox in the form..

Dim varID As Variant

varID = DLookup("[MbrID]", "MailingAddress", "[MbrID] = Forms!Data Entry!ID_NoTxt")

 

[WayneRyan]

keybearer,

Dim varID As Variant

varID = DLookup("[MbrID]", "MailingAddress", "[MbrID] = " & Forms![Data Entry]!ID_NoTxt)
varID = DLookup("[MbrID]", "MailingAddress", "[MbrID] = '" & Forms![Data Entry]!ID_NoTxt & "'")
varID = DLookup("[MbrID]", "MailingAddress", "[MbrID] = #" & Forms![Data Entry]!ID_NoTxt & "#")
[\code]

That works if they are numeric, string, or date (in that order)

You need the square brackets around "Data Entry" because of the space.  It is bettar to
just call it DataEntry.

Wayne

 

[keybearer]

hey wayne.. thanks a lot.. works like a charm.. though i'm a bit confused about the " and '
just a little more help here.. is it possible to turn off the warning that access gives before updating data in the table? if so, how can i do this?

 

[WayneRyan]

keybearer,

SQL wants to see things like:

Select SomeField 
From   SomeTable
Where  OtherField = 'text'

or ...

Select SomeField 
From   SomeTable
Where  DateField = #08/22/03#

To turn off the warning, Tools --> Options
Then select the Edit/Find tab and uncheck the ones under
confirm that you don't want.

Wayne

 

[keybearer]

thanks a heap.. now i'm understanding more n more.. thanks again..

 

[SforSoftware]

To turn off the warning, Tools --> Options
Then select the Edit/Find tab and uncheck the ones under
confirm that you don't want.

When you want te ignore the warning only for that command, use:

DoCmd.SetWarnings False
DoCmd.RunSQL ....
DoCmd.SetWarnings True

Personally I think that's better, because you then still have to confirm the actionquery when you start it (by abuse) from the db-window.

 

[keybearer]

thanks for the tip..