|
| 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
February 17th, 2005, 11:28 PM
|
|||
|
|||
LOOKUP FOR A VALUE ON A TABLE
Good evening everyone
I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora 
|
|
#2
February 19th, 2005, 08:14 AM
|
|||
|
|||
|
Samora,
You could try code something like this (warning: untested!) on the After Update event of Field 2... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Good evening everyone I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora
|
|
#3
February 19th, 2005, 05:23 PM
|
|||
|
|||
|
hi Steve what do you mean by field 3 and field_3 ?? the same thing with Field 2 and field_2 and field 1 and field_1 ? where are the differences ??? i have to replace them by my variables but you appear with another variables that i didn't understand quiet clear... According to this , my variables name are Field 1 NumUniBen String Field 2 Sigla String Field 3 NumDoc String do you think the code you sent to me is still OK??? Thanks for your help Samora -----Original Message----- Samora, You could try code something like this (warning: untested!) on the After Update event of Field 2... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Good evening everyone I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora .
|
|
#4
February 19th, 2005, 06:18 PM
|
|||
|
|||
|
Hello Steve
Once again i think the code is working, but with one problem. it retrieves for me the higher value on Field 3. it is not what i want. what i really want is: if Field 1 concatenated with field 2 exists then it puts on my field 3 the text that is on field 3 . the first one it mets. as you can see on my example, it was supposed to be the number 0004. but as field 1 and field 2 allready exists on my table i have to put there 0001 as you can see on my first line. i think now i explained everything for you Once again, thanks for your help -----Original Message----- hi Steve what do you mean by field 3 and field_3 ?? the same thing with Field 2 and field_2 and field 1 and field_1 ? where are the differences ??? i have to replace them by my variables but you appear with another variables that i didn't understand quiet clear... According to this , my variables name are Field 1 NumUniBen String Field 2 Sigla String Field 3 NumDoc String do you think the code you sent to me is still OK??? Thanks for your help Samora -----Original Message----- Samora, You could try code something like this (warning: untested!) on the After Update event of Field 2... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Good evening everyone I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora . .
|
|
#5
February 19th, 2005, 06:20 PM
|
|||
|
|||
|
Samora,
Now it's me who is confused... What are the names of your fields? What do you mean by "variables" in this context? I thought you had 3 fields called Field 1, Field 2, and Field 3. If you have a space in the name of a field or control, Access VBA intellisense will replace the space by a _ in the code, hence the Field_1 etc in the example I gave you. -- Steve Schapel, Microsoft Access MVP Samora wrote: hi Steve what do you mean by field 3 and field_3 ?? the same thing with Field 2 and field_2 and field 1 and field_1 ? where are the differences ??? i have to replace them by my variables but you appear with another variables that i didn't understand quiet clear... According to this , my variables name are Field 1 NumUniBen String Field 2 Sigla String Field 3 NumDoc String do you think the code you sent to me is still OK??? Thanks for your help Samora
|
|
#6
February 19th, 2005, 06:37 PM
|
|||
|
|||
|
Samora,
I am not sure what is going wrong here... I think I understand the desired outcome, and this is exactly what the example procedure I gave you is designed to do. It should only assign the next higher number if there is no previous instance of the Field 1 and Field 2 values. -- Steve Schapel, Microsoft Access MVP Samora wrote: Hello Steve Once again i think the code is working, but with one problem. it retrieves for me the higher value on Field 3. it is not what i want. what i really want is: if Field 1 concatenated with field 2 exists then it puts on my field 3 the text that is on field 3 . the first one it mets. as you can see on my example, it was supposed to be the number 0004. but as field 1 and field 2 allready exists on my table i have to put there 0001 as you can see on my first line. i think now i explained everything for you Once again, thanks for your help
|
|
#7
February 19th, 2005, 06:39 PM
|
|||
|
|||
|
Samora,
However, I do see an omission in my code... I forgot to add 1 to the existing maximum Field 3 value. Should be like this... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail"))+1,"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Hello Steve Once again i think the code is working, but with one problem. it retrieves for me the higher value on Field 3. it is not what i want. what i really want is: if Field 1 concatenated with field 2 exists then it puts on my field 3 the text that is on field 3 . the first one it mets. as you can see on my example, it was supposed to be the number 0004. but as field 1 and field 2 allready exists on my table i have to put there 0001 as you can see on my first line. i think now i explained everything for you Once again, thanks for your help -----Original Message----- hi Steve what do you mean by field 3 and field_3 ?? the same thing with Field 2 and field_2 and field 1 and field_1 ? where are the differences ??? i have to replace them by my variables but you appear with another variables that i didn't understand quiet clear... According to this , my variables name are Field 1 NumUniBen String Field 2 Sigla String Field 3 NumDoc String do you think the code you sent to me is still OK??? Thanks for your help Samora -----Original Message----- Samora, You could try code something like this (warning: untested!) on the After Update event of Field 2... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Good evening everyone I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora . .
|
|
#8
February 21st, 2005, 10:58 PM
|
|||
|
|||
|
Hi Steve..
Thanks for your great help. It is not necessary to add 1 to the maximum field 3. The only problem i have is : When there is no records with the Field 1 and the Field 2 , Field 3 must have "0000" in order for the user place the number he wants. Of course , your solution of adding 1 to the maximum field is Ok and would releases a lot of work for the user ( i could not make a tab stop to guarantee the value of the Field 3). However, i really would like to know your opinion about this. Thanks in advance. Samora -----Original Message----- Samora, However, I do see an omission in my code... I forgot to add 1 to the existing maximum Field 3 value. Should be like this... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail"))+1,"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Hello Steve Once again i think the code is working, but with one problem. it retrieves for me the higher value on Field 3. it is not what i want. what i really want is: if Field 1 concatenated with field 2 exists then it puts on my field 3 the text that is on field 3 . the first one it mets. as you can see on my example, it was supposed to be the number 0004. but as field 1 and field 2 allready exists on my table i have to put there 0001 as you can see on my first line. i think now i explained everything for you Once again, thanks for your help -----Original Message----- hi Steve what do you mean by field 3 and field_3 ?? the same thing with Field 2 and field_2 and field 1 and field_1 ? where are the differences ??? i have to replace them by my variables but you appear with another variables that i didn't understand quiet clear... According to this , my variables name are Field 1 NumUniBen String Field 2 Sigla String Field 3 NumDoc String do you think the code you sent to me is still OK??? Thanks for your help Samora -----Original Message----- Samora, You could try code something like this (warning: untested!) on the After Update event of Field 2... Dim Previous3 As String Dim Next3 As String Previous3 = Nz(DLookup("[Field 3]","Detail","[Field 1]='" & Me.Field_1 & "' And [Field 2]='" & Me.Field_2 & "'"),"") Next3 = Format(Val(DMax("[Field 3]","Detail")),"0000") If Len(Previous3) Then Me.Field_3 = Previous3 Else Me.Field_3 = Next3 End if -- Steve Schapel, Microsoft Access MVP Samora wrote: Good evening everyone I need some help i have 3 tables: Header, Detail and Footer The Detail table among other fields have 3 fields that i have to manage: The problem i have is this one while i am writing the records and after i update field 1 and field 2 (both of them written) i have to lookup on my Detail table to see if the field 1 concatenated with field 2 allready exists. if it exists, i want to obtain the field 3 . What is the purpose of this? Field 1 Field 2 Field 3 1234 AP 0001 4567 AA 0002 6789 AA 0003 1234 AP 0001 ----- As you can see on line 4 , the field 3 must have the value 0001 (because Field 1 concatenated with Field 2 allready exists) instead of 0004 as it was supposed to be. If someone can help me, i would appreciate very much Samora . . .
|
|
#9
February 22nd, 2005, 08:50 AM
|
|||
|
|||
|
Samora,
I am sorry, I am not sure that I understand your question now. I don't know what you mean about the "tab stop". What I was trying to do was set things up so that when the user makes a new entry in field 1 and field 2, the value of field 3 is automatically entered for you. If there is already a record with the same field 1 and field 2 values, field 3 will show the same as the field 3 vlaue of the previous matching record. If there is no previous matching record, field 3 will have the next sequential number entered automatically. I thought that was what you wanted. That's the way I would do it if it was mine :-) -- Steve Schapel, Microsoft Access MVP wrote: Hi Steve.. Thanks for your great help. It is not necessary to add 1 to the maximum field 3. The only problem i have is : When there is no records with the Field 1 and the Field 2 , Field 3 must have "0000" in order for the user place the number he wants. Of course , your solution of adding 1 to the maximum field is Ok and would releases a lot of work for the user ( i could not make a tab stop to guarantee the value of the Field 3). However, i really would like to know your opinion about this. Thanks in advance. Samora
|
|
#10
February 22nd, 2005, 01:06 PM
|
|||
|
|||
|
Hi Steve.. everything is running OK.. Thanks for your help... i posted a new problem...named MAIL - COMMAND or ACTIVEX. Do you think you can help me on that one??? Thanks for your kindly help Best regards Samora -----Original Message----- Samora, I am sorry, I am not sure that I understand your question now. I don't know what you mean about the "tab stop". What I was trying to do was set things up so that when the user makes a new entry in field 1 and field 2, the value of field 3 is automatically entered for you. If there is already a record with the same field 1 and field 2 values, field 3 will show the same as the field 3 vlaue of the previous matching record. If there is no previous matching record, field 3 will have the next sequential number entered automatically. I thought that was what you wanted. That's the way I would do it if it was mine :-) -- Steve Schapel, Microsoft Access MVP wrote: Hi Steve.. Thanks for your great help. It is not necessary to add 1 to the maximum field 3. The only problem i have is : When there is no records with the Field 1 and the Field 2 , Field 3 must have "0000" in order for the user place the number he wants. Of course , your solution of adding 1 to the maximum field is Ok and would releases a lot of work for the user ( i could not make a tab stop to guarantee the value of the Field 3). However, i really would like to know your opinion about this. Thanks in advance. Samora .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Update Tables using forms | achett | Using Forms | 5 | January 28th, 2005 12:25 AM |
| in a table using lookup how do i get results based on a value in . | Mark05 | Running & Setting Up Queries | 5 | January 14th, 2005 02:03 AM |
| Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
| Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
| Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Linear Mode
