Re: [MS_AccessPros] Dlookup issue

 

Perhaps placing:

nz (  expression  ,0)

will convert that NULL to a numeric value of zero. 

7ony V Meece

Sent from my Verizon Wireless 4G LTE smartphone

-------- Original message --------

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]"

Date:09/07/2014 11:50 AM (GMT-05:00)

To: MS_Access_Professionals@yahoogroups.com

Subject: Re: [MS_AccessPros] Dlookup issue

 

Toukey-

Oh, well.  I assumed clientno is a number - you know, a field name ending in "no".

Try this:

varX = DLookup("clientno", "tblentrylog", "mabwno = " & Me.mabwno & " AND clientno = '" & Me.clientno & "'")

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Sep 7, 2014, at 5:31 PM, toukey1 <no_reply@yahoogroups.com> wrote:

Ok - I think the assignment of varx to string was the main culprit!

 

However I tried your code below and got the following:

 

Run-time error 2471:

The expression you entered as a query parameter produced this error: 'A022'

 

where A022 is actually the clientno

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Toukey-

Me.Parent.mabwno should work as long as there's a value in the outer form (not on a new record).  What do you see if you Debug.Print Me.mabwno or Debug.Print Me.Parent.mabwno ??

Oh, wait a minute!  If the DLookup returns a Null (no record found at all), then your assignment to a String variable will raise that error!  You should Dim varX as Variant.

Note also that DLookup as currently coded will return the value for clientno for the the first record it finds that matches the current mabwno.  If there are multiple records, you might not find out if the client has already been added.  For example, let's say there are records for clients 1, 2, and 3.  The user attempts to add 2 again - but the clientno returned by the DLookup will probably be 1, so you'll not get a match.  I would do:

varX = DLookup("clientno", "tblentrylog", "mabwno = " & Me.mabwno & " AND clientno = " & Me.clientno)

If varX is Null, then you can be sure that the client hasn't been added for the current mabwno.

 

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Sep 7, 2014, at 4:28 PM, toukey1 <no_reply@yahoogroups.com> wrote:

Thanks for your response John.

 

As you mentioned below, once I "dirty" the subform I can see that mawbno is in the tblentrylog.  With regards to the primary key of tblentrylog, yes, it is an artificial AutoNumber. 

 

The user wants when entering the data on the subform to be see if he has already entered info for a client.  If the client already exists, he wants a message displayed to say so and show the hawbno (field from tblentrylog) so that he can reuse the hawbno.  That's the reason I was thinking of using the dlookup.  I tried using Me.Parent.mawbno but still getting the invalid use of null.   

 

Toukey

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Toukey-

If you have the Link Master Fields / Link Child Fields of the subform control set properly, the value for mawbno should be there as soon as the user "dirties" the subform by selecting a value in the combo box.  As long as the outer form isn't on a new record, you should also be able to get a value for mawbno by using:

Me.Parent.mawbno

What is the Primary Key of tblentrylog?  If it is the combination of mawbno and clientno, then you don't need to perform a check at all.  The unique index will ensure that a client gets entered only once for each value of mawbno.  I would bet you have an artificial AutoNumber as the Pkey - this is just another example of why I don't like to use AutoNumber as an artificial Primary Key.  The combo of mawbno and clientno will suffice.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Sep 7, 2014, at 6:08 AM, toukey1 <no_reply@yahoogroups.com> wrote:

Hi,

 

I have a main form (tblmanifest) and a sub-form (tblentrylog).  After populating the main form which includes a field called mawbno I then go to the sub-form to enter the client via a combo box.  After I enter the client I want to be able to check to see if I already entered data for that client and display a message that the client already exist.  I was attempting to use the code below in the After Update property but getting "invalid use of null".

 

Dim varx As String

varx = DLookup("[clientno]", "tblentrylog", "[mawbno] = " & Me!mawbno)

 

Note that the mawbno is also saved in the tblentrylog.

 

Any assistance would be appreciated.

 

Thanks

Toukey

__._,_.___

---

Posted by: Tony V Meece <mr_7ony@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (16)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___