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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Editing null values from Dlookup result in Form 1
- Thread starter EPBoone
- Start date
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
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
-
1
- #3
- Thread starter
- #4
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?
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?
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
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
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
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
- Thread starter
- #7
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.
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.
- Thread starter
- #8
- Status
