If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dlookup
I have a form to add new info. and want a field "empcode" to be auto
filled base on the Name selected. I have the code on the After Update Event Procedure below, but it didn't do anything. Could someone help me what was wrong on the code or should I put the code somewhere else? Thanks a lot!! Private Sub TerrCode_AfterUpdate() Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '") Me.Requery End Sub |
#2
|
|||
|
|||
Dlookup
Orchid,
There is an error in the DLookupo expression, in that you need ""s around the first argument. It also assumes that the ID field is Text data type, is that correct? Also, the Requery will not achieve anything, and can be removed. Private Sub TerrCode_AfterUpdate() Me.empCode = DLookup("[code]","DTNEmp","[ID]='" & Me![Emp] & "'") End Sub Having said that, there is a question about the validity of doing this in the first place. One of the fundamental rules of good database design is that each value should be stored once only on one field in one table. It looks like you might be flouting this rule here. The Code corresponding with the ID is already defined within your database in the other table, so if I am understanding correctly, all you really need here is to *display* the value on the form, not to write it to a field. In that sense, what you are doing is very unusual. There are a number of approaches that may be nore applicable. This article may help: http://accesstips.datamanagementsolutions.biz/lookup.htm -- Steve Schapel, Microsoft Access MVP Orchid wrote: I have a form to add new info. and want a field "empcode" to be auto filled base on the Name selected. I have the code on the After Update Event Procedure below, but it didn't do anything. Could someone help me what was wrong on the code or should I put the code somewhere else? Thanks a lot!! Private Sub TerrCode_AfterUpdate() Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '") Me.Requery End Sub |
#3
|
|||
|
|||
Dlookup
On Apr 16, 6:21*pm, Steve Schapel wrote:
Orchid, There is an error in the DLookupo expression, in that you need ""s around the first argument. *It also assumes that the ID field is Text data type, is that correct? Also, the Requery will not achieve anything, and can be removed. Private Sub TerrCode_AfterUpdate() * *Me.empCode = DLookup("[code]","DTNEmp","[ID]='" & Me![Emp] & "'") End Sub Having said that, there is a question about the validity of doing this in the first place. *One of the fundamental rules of good database design is that each value should be stored once only on one field in one table. *It looks like you might be flouting this rule here. *The Code corresponding with the ID is already defined within your database in the other table, so if I am understanding correctly, all you really need here is to *display* the value on the form, not to write it to a field. * In that sense, what you are doing is very unusual. There are a number of approaches that may be nore applicable. *This article may help:http://accesstips.datamanagementsolutions.biz/lookup.htm -- Steve Schapel, Microsoft Access MVP Orchid wrote: I have a form to add new info. and want a field "empcode" to be auto filled base on the Name selected. I have the code on the After Update Event Procedure below, but it didn't do anything. Could someone help me what was wrong on the code or should I put the code somewhere else? Thanks a lot!! Private Sub TerrCode_AfterUpdate() Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '") Me.Requery End Sub- Hide quoted text - - Show quoted text - Thanks for your reply, Steve! I tried your code, but still nothing show for the field "empcode". To make sure what I have is correct on the Dlookup formula: -- a table called "DTNEmp" with columns: ID, Code,... JD 001 JT 002 GA 003 -- a form to input new info. with fields: Emp, empCode,... so if I have JD on Emp column, 001 should be auto filled on empCode column. By the logic I had, what did I do wrong? By the way, it is possible to Dlookup form a query instead of a table? I see what you are saying and thanks for pointing out. I am just using the field name as example. In fact on my database, I may have some info for an empCode without ID. However, for the ones with ID, I don’t want to input but automatically fill in for me. Your help is greatly appreciated! |
#4
|
|||
|
|||
Dlookup
Orchid,
Yes, you can use the DLookup function against a query. From what you have said, your logic seems to be correct. As I mentioned before, your original expression was missing the ""s. The only thing I noticed is that the code you gave is running on the After Update event of "TerrCode" and you haven't so far indicated what TerrCode is, and how its After Update event gets triggered. The other thing that has crossed my mind is that you may be entering the Emp via a combobox, in which case we need to check that you have this set up correctly. -- Steve Schapel, Microsoft Access MVP Orchid wrote: I tried your code, but still nothing show for the field "empcode". To make sure what I have is correct on the Dlookup formula: -- a table called "DTNEmp" with columns: ID, Code,... JD 001 JT 002 GA 003 -- a form to input new info. with fields: Emp, empCode,... so if I have JD on Emp column, 001 should be auto filled on empCode column. By the logic I had, what did I do wrong? By the way, it is possible to Dlookup form a query instead of a table? |
Thread Tools | |
Display Modes | |
|
|