ACCESS FEED: RE: [MS_AccessPros] Dlookup issue

Minggu, 07 September 2014

RE: [MS_AccessPros] Dlookup issue

That's the point...data entry starts with searching. Very difficult to stop duplicates after the data has been entered.

From: toukey1
Sent: ‎9/‎7/‎2014 11:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Dlookup issue

Thanks Jack but need it to work when the user is entering the info.

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

How about using a Search form to start with, then if not found open a detail form in add mode?

From: toukey1
Sent: ‎9/‎7/‎2014 7:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Dlookup issue

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