×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Editing null values from Dlookup result in Form

Editing null values from Dlookup result in Form

Editing null values from Dlookup result in Form

(OP)
On my form, I have several fields that are updated automatically with fields from two other tables by using Dlookup in the control source of the fields in the form.  The problem is, when a null value is returned to these fields, I need to be able to manually input the information, but I can't do so because the control source is set to "Dlookupwhatever".  How can I fix this so that I am able to update these fields.

RE: Editing null values from Dlookup result in Form

DLookup is very probably not the best way to do what you want, but what technique you should use depends on several other things:
1. Is the DLookup criteria string built from values in the form's Record Source? If not, where do they come from?
2. Do you want to limit the values your users input into the text box? If so, would a combo box be better?
3. If you don't want to limit the input values, do you mean to update the other tables to add the values?
4. Are there fields that relate your Record Source to these other tables?
5. Give a sample of one of the DLookup expressions.

I might be wrong, but it sounds like you probably have a simple code or number that you want to translate into a user-friendly description. There are much better ways to do that than DLookup.

Rick Sprague

RE: Editing null values from Dlookup result in Form

I think you can use the "Nz" function with dlookup.

RE: Editing null values from Dlookup result in Form

(OP)
Well, the form is for a shipping document.  I have for example, two fields (Document Number and Part Number), Three tables: 1. (DCU Log)  A log with all of our Document Numbers, with fields part number and serial number.
2. (1348-1AS1) The table for the shipping documents. (Also the form name.) 3.  (Q-ICRL) A list of all the part numbers.  
When I type in the Document Number in the shipping document, Dlookup gets the part number from table 3 by what the part number is for that document number in table 1  and inserts it into the part number field on the shipping document form.  (The part number field on the form contains the Dlookup control)  Does that make sense?  
However, not all the part numbers that are in table 1, are contained in table 3 and if it isn't, I get a blank part number field in the form.  When this happens, I need to be able to manually input the part number.  I do not want any limits on what is inputted in these fields.  I would like to update table 3 upon entry of a new part number (There are several other fields that need to be updated in the same table with the part number).  Here is an example of the control:

=DLookUp("[PART_NO]","Q-ICRL","[DCU LOG]![DOC#]=FORMS![1348-1AS1]![DDSN]")
(DDSN is the name of the Document Number field on the report.)

I hope that all makes sense.  What do you think?  Is there a better way to do this?

RE: Editing null values from Dlookup result in Form

As given, your DLookup function would execute this query:
   SELECT [PART_NO] FROM Q-ICRL WHERE [DCU LOG]![DOC#] = FORMS![1348-1AS1]![DDSN]
The WHERE clause doesn't refer to a field within Q-ICRL, so this doesn't make sense. Your selection criteria must specify a value to match a field in the Q-ICRL table to work right.

But I think I get the gist of what you need to do. When you type in a Document Number, you want to look up the part number in DCU LOG, then use that to look up the part number and additional info in Q-ICRL. If it doesn't exist there, you want the user to be able to add a new record to Q-ICRL, including the additional info.

I would suggest making some minor changes to eliminate the DLookups and let Access do some of the work for you. But first I need to know: Are the users to be allowed to change any of the part number information when it is found? My guess is that they aren't, because changing it for this shipping document automatically changes it for all shipping documents this part number occurs on, past and future. But I need to know for sure.

Rick Sprague

RE: Editing null values from Dlookup result in Form

After thinking about this some more, I've come to the conclusion that Q-ICRL isn't really a table, it's a query that probably joins the DCU LOG table and some other table with part number data. The join is probably an outer join, so that you always get the DCU LOG data and get the other table's data if there is any.

That changes things a little bit. Now I need to know more about the Q-ICRL query and its underlying table with the part data. The easiest thing would be to copy and paste the SQL statement (open the query, switch to SQL View, and copy).

I'd also like to know the primary key fields, if any, for the two tables in Q-ICRL.

Rick Sprague

RE: Editing null values from Dlookup result in Form

(OP)
You are right.  Q-ICRL is a query not a table.  Sorry about that.  You are correct also about adding information.  I don't want users to be able to change existing part numbers, but I do want them to be able to add new ones.

SELECT DISTINCTROW [DCU LOG].[Doc#], [DCU LOG].SERNO, [DCU LOG].MCN, [DCU LOG].STATUS, [DCU LOG].REMARKS, ICRL.FSCM, ICRL.PART_NO, ICRL.NOMEN, ICRL.COG, ICRL.MCC, ICRL.FSC, ICRL.NIIN, ICRL.SMIC, ICRL2.[UNIT PRICE $], ICRL2.[UNIT PRICE]
FROM ICRL2 INNER JOIN (ICRL INNER JOIN [DCU LOG] ON ICRL.PART_NO = [DCU LOG].[PART NO]) ON ICRL2.NIIN = ICRL.NIIN;

That is the SQL statement for Q-ICRL. As you can see there are three tables in Q-ICRL.  DOC# is the primary field in Table DCU Log.  Tables ICRL and ICRL2 do not have any primary fieds assigned.  

RE: Editing null values from Dlookup result in Form

(OP)
Never mind.  I figured it out.  Thanks for your help anyway.  It is greatly appreciated!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close