Need help on DLookUp Function Please !!!!

[mickeync]

I am new to VB coding and not quite sure how to make this work.

I have two forms, Customer Status and Customer Profile. When changing customer status to complete on Customer Status form, behind the scene it needs to go to Customer Profile form and check to see if any empty field such as address, city, zip is blank then automatically open Customer Profile form and make them key all the blank field before the Customer Status form can be saved !!!

I would appreciated any help ... and thanks in advance.

 

[WildHare]

Well, the DLOOKUP function takes three parts:

1) what to look for
2) where (what table) to look for it in, and
3) a value to use to match against some other value in a field in the table of #2)

For example, if you wanted to look up a job description based on an employee's job number, here's the idea;


Dlookup("JobDesc", "JobsTable", "JobsTable.JobID = Employee.JobID&quot

In other words, get the Job Description out of the Jobs table, where the Jobs Table ID field is equal to the Employee's Job ID field.

The problem with Dlookup is the sometimes annoying need to unquote and requote around criteria strings. If Job ID is a TEXT field (e.g. "A1234&quot, then you want to substitute that value for the last part of the criteria string:

"JobsTable.JobID = "A1234"

but the quoting there will mess you up. So you need to add a SINGLE quote to the front half, right after the equal sign, and then shove another single quote at the very end.

"JobsTable.JobID = '[/red]" & Employee.JobID & "'[/red]"

To check whether or not a field is null , use the IsNull function, applied to the value that DLOOKUP returns.

IF IsNull(Dlookup("CAddr", "CustomerProfile", "CustomerNumber = '" & CustNo & "')) then
msgbox "Customer address is NULL..."
else
{ whatever...

Get the picture?








Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[mickeync]

Thank you for your quick response. However I am currently working on "Customer Status" form. The problem is which event I should use to make the Customer Status pause while I am checking for the "Customer Profile" if there is any NULL field....

Here is my code:

If [Forms]![Customer Status]![Status] = "Completed" Then
IsNull(Dlookup("CAddr", "CustomerProfile", "CustomerNumber = '" & CustNo & "')) ....
DoCmd.Openform "Customer Profile"
DoCmd.GotoControl "CAddr"
End If

It seems nothing going to happen !!! What did I do wrong here ??? Please help !!! Thank you.

 

[WildHare]

Forgot one part:

If [Forms]![Customer Status]![Status] = "Completed" Then
IF[/blue] IsNull(Dlookup("CAddr", "CustomerProfile", "CustomerNumber = '" & CustNo & "')) ....
DoCmd.Openform "Customer Profile"
DoCmd.GotoControl "CAddr" //* Not sure about this??
End If
End IF


//* as soon as a form opens, the code for that form (if there is any,) starts running. The GOTO CONTROL line will NOT execute until focus returns back to this form. To open a form and position the focus to a particular field, you might be better served with the TAG property, which you can query and use to place the focus.

You could put this on the form current event, or better yet , the "after update" event of the Status field.



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm